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;