SOBRE O PL/SQL

O PL/SQL (Procedural Language/SQL) é uma extensão do SQL com recursos de design de linguagem de programação, sendo uma extensão de linguagem procedural da Oracle;

Incorpora muitos recursos avançados feitos em linguagem de programação projetadas durante as décadas de 70 e 80;

É uma tecnologia empregada pelo Oracle Server e por algumas ferramentas Oracle;

SCRIPT PARA SER EXECUTADO VIA LINHA DE COMANDO NO SQLPLUS

Salvar em arquivo texto dentro do linux e executar via sqlplus

create tablespace banco1 datafile '/u01/app/oracle/oradata/XE/banco1.dbf' size 10m autoextend on next 5m maxsize 50m online permanent extent management local autoallocate segment space management auto;

create user banco1 identified by banco1 default tablespace banco1 temporary tablespace temp;

grant create session, connect, resource to banco1;

alter user banco1 quota unlimited on banco1;

connect banco1/banco1;

CREATE TABLE DEPTO (
  CDDEPTO CHAR(02) PRIMARY KEY,
  NMDEPTO VARCHAR(30),
  RAMAL NUMBER(3)
);

CREATE TABLE CARGO (
  CDCARGO CHAR(02) PRIMARY KEY,
  NMCARGO VARCHAR(30),
  VRSALARIO NUMBER(8,2)
);

CREATE TABLE FUNCIONARIO (
  NRMATRIC NUMBER(04) PRIMARY KEY,
  NMFUNC   VARCHAR(30),
  DTADM    DATE,
  SEXO     CHAR(01),
  CDCARGO  CHAR(02),
  CDDEPTO  CHAR(02),
  CONSTRAINT FK_FUNC_CDCARGO FOREIGN KEY(CDCARGO) REFERENCES CARGO(CDCARGO),
  CONSTRAINT FK_FUNC_CDDEPTO FOREIGN KEY(CDDEPTO) REFERENCES DEPTO(CDDEPTO)
);

INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C1','COZINHEIRA',350);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C3','AUX ESCRITORIO',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C7','VIGIA',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C2','MECANICO',750);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C5','GERENTE',2300);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C4','ESCRITURARIO',600);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C8','PROGRAMADOR',NULL);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C9','ANALISTA_DE_SISTEMAS',2100);

COMMIT;

INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D1','ADMINISTRACAO',221);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D2','OFICINA',235);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D3','SERVICOS GERAIS',243);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D4','VENDAS',258);
COMMIT;

INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1001,'JOAO SAMPAIO','10-AUG-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1004,'LUCIO TORRES','02-MAR-94','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1034,'ROBERTO PEREIRA','23-MAY-92','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1021,'JOSE NOGUEIRA','10-NOV-94','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1029,'RUTH DE SOUZA','05-JAN-93','F','C1','D3');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1095,'MARIA DA SILVA','03-SEP-92','F','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1023,'LUIZ DE ALMEIDA','12-JAN-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1042,'PEDRO PINHEIRO','29-JUL-94','M','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1048,'ANA SILVEIRA','01-JUN-93','F','C5','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1015,'PAULO RODRIGUES','17-AUG-92','M','C2','D2');
COMMIT;

INSTRUÇÃO SELECT BÁSICA

SELECT [DISTINCT] {*, coluna [apelido],…}
FROM tabela;

Uma cláusula SELECT especifica as colunas a serem exibidas;

Uma cláusula FROM especifica a tabela que contém as colunas listadas na cláusula SELECT

Na sintaxe:

SELECT –> é a lista de uma ou mais colunas

DISTINCT –> suprime os itens duplicados

* –> selectiona todas as colunas

coluna –> seleciona a coluna nomeada

apelido –> fornece cabeçalhos diferentes às   colunas selecionadas

FROM –> tabela especifica a tabela contend   as colunas

CRIANDO INSTRUÇÕES SQL

Instruções SQL sem distinção entre maiusculas / minusculas;

Instruções SQL podem estar em uma ou mais linhas;

Palavras-chave não podem ser abreviadas ou divididas entre as linhas;

Normalmente, as cláusulas são colocadas em linhas separadas;

Guias e indentações são usadas para aperfeiçoar a legibilidade;

SELECIONANDO COLUNAS

SQL> SELECT *
  2  FROM DEPTO;

SQL> SELECT CDDEPTO, NMDEPTO, RAMAL
  2  FROM DEPTO;

SQL> SELECT CDDEPTO, NMDEPTO
  2  FROM DEPTO;

AJUSTANDO CABEÇALHO DAS COLUNAS

SQL> COL "CDDEPTO" FORMAT "A15";

SQL> SELECT *
  2  FROM DEPTO;

A15 –>quantidade de caracteres a ser apresentado

DEFAULTS DE CABEÇALHOS DE COLUNA

Justificativa default

Esquerda: dados de caractere e data

Direita: Dados numéricos

Exibição default: Letra maiúscula

SQL> SELECT cddepto, nmdepto
  2  FROM DEPTO; 

Obs –> mesmo digitando os campos selecionandos em minúsculo, será exibido em maiúsculo

EXPRESSÕES ARITMÉTICAS

Criar expressões com dados NUMBER e DATE usando operadores aritméticos.

OPERADOR	DESCRIÇÃO
+	Adicionar
–	Subtrair
*	Multiplicar
/	Dividir

USANDO OPERADORES ARITMÉTICOS

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  VRSALARIO + 1000
   4  FROM CARGO;

PRECEDENCIA DE OPERADORES

A multiplicação e a divisão têm prioridade sobre a adição e a substração;

Os operadores com a mesma prioridade são avaliados da esquerda para a direita;

Os parênteses são usados para forçar a avaliação priorizada e para esclarecer as instruções;

Exemplo 1:

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  12 * VRSALARIO + 100
   4  FROM CARGO;

Exemplo 2:

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  12 * (VRSALARIO + 100)
   4  FROM CARGO;

DEFININDO UM VALOR NULO

Um valor nulo não está disponível, não é atribuído, é desconhecido ou não é aplicável;

Um valor nulo não é o mesmo que um zero ou um espaço em branco;

SQL> SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2  FROM CARGO;

VALORES NULOS NAS EXPRESSÕES ARITMÉTICAS

Se qualquer valor da coluna em uma expressão aritmética for nulo, o resultado será nulo;

SQL> SELECT CDCARGO, NMCARGO, 
  2  VRSALARIO + 100
  3  FROM CARGO;

DEFININDO UM APELIDO (ALIAS) PARA COLUNA

Renomeia um cabeçalho de coluna;

É útil para cálculos;

Segue imediatamente o nome da coluna;

Palavra-chave “as” é opcional entre o nome da coluna e o apelido;

Necessita de aspas duplas caso contenha espaço ou caracteres especiais ou faça a distinção entre maiúscula e minúscula;

USANDO UM APELIDO DE COLUNA

SQL> SELECT CDCARGO AS CODIGO_DO_CARGO, 
   2 NMCARGO AS "Nome do Cargo", 
   3 VRSALARIO VALOR$SALARIO
   4 FROM CARGO;

Observação: Os caracteres especiais # e $ são permitidos serem utilizados em alias sem utilização das aspas duplas;

OPERADOR DE CONCATENAÇÃO

Concatena colunas ou strings de caractere a outras colunas;

É representado por duas barras verticais “||” (pipe);

Cria uma coluna resultante que é uma expressão de caracteres

USANDO UM OPERADOR DE CONCATENAÇÃO

SQL> SELECT CDCARGO || NMCARGO 
   2	AS codigo_nome 
   3  	FROM CARGO;

STRING LITERAIS DE CARACTERES

Uma literal é um caractere, um número ou uma data incluída na lista SELECT;

Valores literais de caractere e data devem estar entre aspas simples;

Cada string de caractere é gerada um vez para linha retornada;

LINHAS DUPLICADAS

A exibição das consultas é de todas as linhas, incluindo linhas duplicadas:

SQL> SELECT CDCARGO
   2  FROM FUNCIONARIO;

ELIMINANDO LINHAS DUPLICADAS

Elimine as linhas duplicadas usando a palavra chave DISTINC na cláusula SELECT:

SQL> SELECT DISTINCT CDCARGO
   2  FROM FUNCIONARIO;

O DISTINCT atua sobre a linha do select, não sendo possível ter 2 DISTINCTS na mesma SELECT

LIMITANDO LINHAS SELECIONADAS

Restringe as linhas retornadas usando a cláusula WHERE:

A cláusula WHERE pode comparer valores em colunas, valores literais, expressões aritméticas ou funções. É formada por tres elementos:

– Nome da coluna;
– Operadores de comparação;
– Nome da coluna, constant ou lista de valores;

USANDO A CLÁUSULA WHERE

SQL> SELECT NRMATRIC, NMFUNC
   2  FROM FUNCIONARIO
   3  WHERE CDCARGO = 'C2';

STRINGS DE CARACTERES E DATAS

As strings de caractere e valores de dados aparecem entre aspas simples;

Os valores de caractere fazem distinção entre maiusculas e minúculas e o valores de data diferenciam formatos;

O format de data default é DD-MON-YY.

Exemplo:

SQL> SELECT NRMATRIC, NMFUNC, DTADM
   2 FROM FUNCIONARIO
   3 WHERE SEXO = 'F' 
   4 AND DTADM = '01-JUN-93';

OPERADORES DE COMPARAÇÃO

OPERADOR	SIGNIFICADO
=	Igual
>	Maior do que
>=	Maior do que ou igual a
<	Menor
<=	Menor ou igual a
	Diferente de

UTILIZAÇÃO DOS OPERADORES DE COMPARAÇÃO

Use os operadores de comparação em condições que comparam uma expressão a outra.

Eles são usados na cláusula WHERE seguindo os exemplos:

WHERE hiredate = '01-JAN-95'

WHERE SAL >= 500

WHERE ENAME = 'SMITH'

OUTROS OPERADORES DE COMPARAÇÃO

OPERADOR	SIGNIFICADO
BETWEEN … AND…	Entre dois valores (inclusive)
IN (list)	Vincula qualquer um de uma lista de valores
LIKE	Vincula um padrão de caracteres
IS NULL	É um valor nulo

USANDO O OPERADOR BETWEEN

Use o operador BETWEEN apra exibir linhas baseadas em uma faixa de valores

SQL>SELECT NMCARGO, VRSALARIO
  2 FROM CARGO
  3 WHERE VRSALARIO BETWEEN 450 AND 750;

USANDO O OPERADOR IN

Use o operador IN para testar os valores de uma lista

SQL>SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
  2 FROM FUNCIONARIO
  3 WHERE CDCARGO IN ('C1','C3','C4');

USANDO O OPERADOR LIKE

Use o operador LIKE para executar pesquisas curinga com valores de string válidas;

As condições de pesquisa podem conter caracteres literais ou números;

– % denota zero ou muitos caracteres;

– _ (underline) denota um caractere;

Voce pode combiner caracteres de vinculação de padrão;

SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE 'JO%';

SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE '%RO%'

SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE '_O%'

USANDO A OPÇÃO ESCAPE

Quando for necessário ter uma correspondencia exata para os caracteres ‘%’ e ‘_’, use a opção ESCAPE

SQL> SELECT CDCARGO, NMCARGO
   2 FROM CARGO
   3 WHERE NMCARGO LIKE '%@_%' ESCAPE '@';

USANDO O OPERADOR IS NULL

O operador IS NULL testa valores que são nulos. Um valor nulo significa que o valor não está disponível, não-atribuído, desconhecido ou não aplicável. Assim, não é possível testar com “=” porque um valor nulo não pode ser igual ou desigual a qualquer valor.

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE VRSALARIO IS NULL;

OPERADORES LÓGICOS

OPERADOR	SIGNIFICADO
AND	Retorna TRUE se as condições de componmentes forem TRUE
OR	Retorna TRUE se cada condição de componente for TRUE
NOT	Retorna TRUE se a condição seguinte for FALSE

Um operador lógico combina o resultado de duas condições de componente para produzir um único resultado com base neles ou inverter o resultado para a condição única. Três operadores lógicos estão disponíveis no SQL: AND, OR e NOT

USANDO O OPERADOR AND

AND exige que ambas as conduições sejam TRUE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2   FROM CARGO
   3   WHERE CDCARGO = 'C5'
   4   AND VRSALARIO >= 2000;

TABELA VERDADE AND

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

USANDO O OPERADOR OR

OR exige que cada condição seja TRUE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE CDCARGO = 'C5'
   4 OR VRSALARIO >= 2000;

TABELA VERDADE OR

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

USANDO O OPERADOR NOT

NOT nega o operador usado na operação

O operador NOT pode ser utilizado também com outros operadores SQL, como BETWEEN e LIKE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE VRSALARIO IS NOT NULL;

TABELA VERDADE NOT

NOT	TRUE	FALSE	NULL
TRUE	FALSE	TRUE	NULL

EXEMPLO DO OPERADOR NOT

... WHERE CDDEPTO NOT IN ('D1','D3');

... WHERE VRSALARIO NOT BETWEEN 50 AND 500;

... WHERE NMCARGO NOT LIKE 'VIG%';

... WHERE RAMAL IS NOT NULL;

REGRAS DE PRECEDÊNCIA

ORDEM DE AVALIAÇÃO	OPERADOR
1	Todos os operadores de comparação
2	NOT
3	AND
4	OR

Sobreponha regras de precedência usando parênteses

REGRAS DE PRECEDÊNCIA

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE CDCARGO = 'C1'
   4 OR  CDCARGO = 'C7'
   5 AND VRSALARIO >= 350

A ordem de execução usando regras de precedencia é:

A primeira condição é que o CDCARGO seja igual a ‘C7’ e o VRSALARIO SEJA >= 350

A segunda condição é que o cargo CDCARGO seja igual a ‘C1’;

CLÁUSULA ORDER BY

ORDER BY Classifica as linhas de 2 formas:

–ASC: ordem crescente, default (do menor para o maior);

–DESC: ordem decrescente (do maior para o menor);

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 ORDER BY NMCARGO;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2  FROM CARGO
   3  ORDER BY CDCARGO ASC;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 ORDER BY VRSALARIO DESC;

CLASSIFICANDO POR APELIDO DE COLUNA

SQL> SELECT NMCARGO, VRSALARIO,
   2 VRSALARIO * 12 AS SAL_ANUAL
   3 FROM CARGO
   4 ORDER BY SAL_ANUAL

CLASSIFICANDO POR POSIÇÃO DE COLUNA

SQL> SELECT NMCARGO, VR_SALARIO,
   2 VRSALARIO * 12 AS SAL_ANUAL
   3 FROM CARGO
   4 ORDER BY 2

CLASSIFICANDO POR VÁRIAS COLUNAS

SQL> SELECT NMFUNC, SEXO, DTADM
   2 FROM FUNCIONARIO
   3 ORDER BY NMFUNC,DTADM DESC;

CONECTANDO DO SHELL DO LINUX PARA O SQL/PLUS

Como SYSDBA

[oracle@oracle ~]$ sqlplus / as sysdba

Como usuário comum

[oracle@oracle ~]$ sqlplus banco1/banco1

CONSULTANDO TABELAS DO SCHEMA NO SQL/PLUS

SQL> SELECT TABLE_NAME
   2 FROM USER_TABLES;

CONSULTANDO A ESTRUTURA DE UMA TABELA NO SQL/PLUS

SQL> DESC FUNCIONARIO;

SAINDO DO SQL/PLUS

SQL> exit;