PL/SQL

BLOCOS
COLCHETES SIMBOLIZAM PARÂMETROS OPCIONAIS
Anônimo
[DECLARE]

BEGIN
[EXCEPTION]
END;

Procedimento
PROCEDURE nome IS

BEGIN
[EXCEPTION]
END;

Função
FUNCTION nome RETURN tipo IS

BEGIN

RETURN valor
[EXCEPTION]
END;

DECLARANDO VARIÁVEIS
Identificador [CONSTANT] tipo [NOT NULL] [:= ] [DEFAULT Expressão];
Não são case sensitive
Tipos de Variáveis
Numéricos
BINARY_INTEGER => inteiro de -231-1 até 231-1
NATURAL => inteiro de 0 até 231
POSITIVE => inteiro de 1 até 231
 NUMBER(p,e) => (nº de dígitos, nº de casas decimais)
Caractere
CHAR(N) => N é o tamanho fixo da string
VARCHAR2(N) => N é tamanho máximo da string(string tem tamanho variável)
BOOLEAN (true ou false)
DATE => to_date(‘00/00/0000’,‘dd/mm/yyyy’)
Data do Sistema => SYSDATE
HORA DO SISTEMA => TO_CHAR(SYSDATE, 'HH24')
TIPO TABELA
Coleções indexadas que não permitem DML
TYPE nome_var IS TABLE OF NUMBER INDEX BY tipo_índice;
Índice geralmente é inteiro do tipo BINARY_INTEGER
Declarando
DECLARE
nome_var nome_var_tipo_tab;
Utilizando
Nome_var_tipo_tab(índice) := valor
Manipulando
EXISTS(n): True se o elemento existir na tabela
COUNT: retorna nº de linhas da tabela
FIRST: retorna primeiro índice da tabela
LAST: retorna último índice da tabela
PRIOR(n): retorna índice que precede n na tabela
NEXT(n): retorna índice que sucede n na tabela
DELETE: apaga tabela
DELETE(n): deleta elemento n da tabela
DELETE(m,n): apaga elementos com índices de m até n
OPERADORES
Aritméticos: +, -, *, /, MOD
Exponenciação: **
Comparação: =, <, >, <>, !=, <=, >=, IS NULL, LIKE, BETWEEN, IN
Atribuição: :=
Intervalo: ..
Concatenacão: ||
Lógicos: NOT, AND, OR
COMENTÁRIOS
Uma linha: --
Mais linhas: /* comentário
Continuação*/
IMPRIMIR LINHA: DBMS_OUTPUT.PUT_LINE(string, var);
“COPIANDO” TIPO DA COLUNA DA TABELA => identificador [Link]%TYPE
“PROJETAR” DADOS (consultas retornam apenas uma linha)
SELECT coluna1[,coluna2,coluna3,...,colunan]
INTO var1[,var2,var3,...,varn]
FROM tabela1[,tabela2,tabela3,...,tabelan]*
[WHERE cond1[AND cond2 AND... condn];
*pode identificar as tabelas => ex: tabela1 t1, tabela2 t2
*nesse caso as colunas recebem o novo símbolo => antes = tabela1.coluna1 e
depois = t1.coluna1
CONTADOR: SELECT COUNT(coluna ou *)
* => todas as linhas
INSERIR DADOS
INSERT INTO tabela(coluna1[,coluna2,...,colunan])
VALUES(v1[,v2,...,vn)
ATUALIZAR DADOS
UPDATE tabela
SET coluna_tabela = var_novo_valor
WHERE condição;
Obs: condição geralmente permite encontrar valor que deseja modificar
EXCLUIR DADOS
DELETE FROM tabela
WHERE condição;
IF
IF condição THEN
Comandos;
[ELSIF condição2 THEN
Comandos;]
...
[ELSIF condiçãon THEN
 Comandos;]
[ELSE
COMANDOS;]
END IF;
CASE
CASE seletor
WHEN expr1 THEN resul1
WHEN expr2 THEN resul2
...
WHEN exprn THEN resuln
[ELSE resultadox]
END;
CONVERTER PARA MAIÚSCULA: UPPER(string)
CONVERTER PARA MINÚUSCULA: LOWER(str)
ENTRADA A PARTIR DO CONSOLE: '&caractere'
TIRAR ESPAÇOS A DIREITA DA STRING: RTRIM(str)
TAMANHO DA STRING: LENGTH(str)
BUSCAR SUBSTRING EM STRING: INSTR(str,substr)
SELECIONAR SUBSTRING EM STRING: SUBSTR(str,poscharini,poscharfinal)
LOOP
LOOP
Comandos;
EXIT [WHEN condição];
END LOOP;
WHILE
WHILE condição LOOP
COMANDOS;
END LOOP;
FOR
FOR contador IN [REVERSE] lim_inf..lim_sup LOOP
COMANDOS;
END LOOP;
OBS: CONTADOR NÃO PODE SER DECLARADO ANTES!!!!!!
CURSOR
“Ponteiro” para manipular linhas de uma tabela temporária
Sintaxe
DECLARE
CURSOR nome_cursor IS
(Faz select normal, sem INTO);

BEGIN
LOOP
OPEN nome_cursor;
FETCH nome_cursor INTO var[,var2,var3,...,varn]*
EXIT WHEN nome_cursor%NOTFOUND;
...
CLOSE cursor_nome
END LOOP;
END;
Atributos de Cursores
%ROWCOUNT: qtde de linhas que foram geradas por SELECT
%FOUND: true se alguma linha tenha sido gerada
%NOTFOUND: true de alguma linha não tenha sido gerada
%ISOPEN: true se cursor tiver aberto
“Copiar” Tipo do Cursor: var nome_cursor%ROWTYPE;
Quando for usar a variável criada lembrar de colocar [Link]
CURSOR COM FOR
FOR var IN nome_cursor LOOP
END LOOP;
OBS: NÃO PRECISA USAR OPEN,FETCH E CLOSE
OBS: var é do tipo ROWTYPE então usa [Link]
OBS: LEMBRAR QUE var FAZ O PAPEL DO CONTADOR ENTÃO NÃO DECLARA
Cursor com Parâmetros
DECLARE
CURSOR nome_cursor (var tipo) IS
(faz SELECT);
**pode colocar condição do WHERE comparando valor de coluna com var
**não coloca tamanho com tipo
BEGIN
nome_cursor(valor de var);
 END;
**se for passar mais de um parâmetro, tem que abrir e fechar cursor a cada
parâmetro: OPEN nome_cursor(par1); ->
...
CLOSE nome_cursor
OPEN nome_cursor(par2)
...
EXCEÇÕES
EXCEPTION
WHEN exceção1 [OR exceção2...] THEN
comando1;
comando2;
...
[WHEN exceçãon [OR execeçãon+1...] THEN
...
[WHEN OTHERS THEN
...]
Exceções pré definidas no Oracle
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX
SUBPROGRAMAS
NÃO USAR DECLARE (procedimento/função “substitui” DECLARE)
TIPOS:
Procedimentos(PROCEDURE): Não retornam valor(exceto em modos OUT e IN
OUT)
Funções(FUNCTION): retornam um único valor
SINTAXE
Procedimento
CREATE OR REPLACE PROCEDURE nome_procedimento [(par1 [modo1]
tipo1, par2 [modo2] tipo2,...)] IS|AS
Declaracão_das_variáveis*
BEGIN
 Corpo_do_procedimento;
END;
Função
CREATE OR REPLACE FUNCTION nome_função [(par1 [modo1] tipo1,
par2 [modo2] tipo2,...)] RETURN tipo_retorno IS|AS
Declaracão_das_variáveis*
BEGIN
Corpo_da_função;
END;
*Declara as variáveis normalmente como se fosse no DECLARE
CHAMANDO
Procedimento
BEGIN
nome_procedimento[(valor(es)_par)];
END;
Função
BEGIN
DBMS_OUTPUT.PUT_LINE([str] [||] nome_função[(valor(es)_par)])
END;
MODOS DE PASSAGEM DE PARÂMETRO
IN (entrada - default) => número, string ou var inicializada
OUT (saída)* => var não inicializada (sem valor atribuído)
IN OUT (entrada/saída)* => var inicializada
*devem ser especificados
**OUT funciona como o “RETURN”(função) do procedimento
EXCLUINDO
Procedimento
DROP PROCEDURE nome_procedimento;
Função
DROP FUNCTION nome_função;
ORGANIZANDO (PACOTES)
Pacotes são como um conjunto de subprogramas
Duas partes
Especificação: público
Corpo: privado
Sintaxe
Especificação
CREATE OR REPLACE PACKAGE nome_pacote IS|AS
declara tipos, var e assinaturas de subprogramas
END [nome_pacote];
Exemplo:
CREATE OR REPLACE PACKAGE ex_pkg IS
var_teste INT;
PROCEDURE teste_proc[(parâmetros [modos] tipos)];
FUNCTION teste_function [(par [modos] tipos)] RETURN tipo;
END ex_pkg;
Corpo
CREATE OR REPLACE PACKAGE BODY nome_pacote IS|AS
declara tipos, var e corpo dos subprogramas
END [nome_pacote];
/
Exemplo:
CREATE OR REPLACE PACKAGE BODY ex_pkg
PROCEDURE teste_proc[(...)] IS
BEGIN
...
END;
FUNCTION teste_function[(...)] IS
BEGIN
...
RETURN var;
END;
END ex_pkg;
/
Chamando
[DECLARE]
BEGIN
nome_pacote.nome_subprograma[(...)];
 END;
Exemplo:
ex_pkg.teste_proc[(...)];

Removendo
DROP PACKAGE nome_pacote;
DROP PACKAGE BODY nome_pacote;
TRIGGERS
Códigos associados a tabela ou view(objetos) e são executados quando ocorre
determinado evento (logon, shutdown, criação, exclusão, atualização)
Caso ultrapasse 60 linhas, criar procedure
Não usar para refazer ações já existentes no SGBD (NOT NULL, UNIQUE, PK, FK,
CHECK...)
SINTAXE
CREATE OR REPLACE TRIGGER nome_trigger
Momento evento1 [OR evento2 OR...]
ON nome_objeto
[REFERENCING OLD AS apelido_do_valor_antigo | NEW AS apelido_novo_valor]
FOR EACH ROW (presença/ausência depende do tipo)
[WHEN condição] (restringe linhas que acionarão trigger)
BEGIN
corpo do trigger
END;
MOMENTO: quando o trigger deve ser executado
BEFORE
AFTER
INSTEAD OF
EVENTO
CREATE SHUTDOWN
ALTER GRANT
DROP REVOKE
SERVERERROR INSERT
LOGON DELETE
LOGOFF UPDATE
STARTUP
Referenciar valores novo e antigo (UPDATE)
Novo => :[Link]
Velho => :[Link]
Só podem ser usados em triggers de linha (FOR EACH ROW)
TIPO
Comando
Sem o “FOR EACH ROW”
Pode atualizar uma ou mais linhas
Não permite acesso às linhas atualizadas
Atualização: UPDATE [OF]
Linha
Com “FOR EACH ROW”
Acionado para cada linha afetada
Atualização: UPDATE OF campo (obrigatório uso do OF)
AÇÃO
Quando há mais de um evento, separa o que fazer para cada um
Geralmente usado com condicionais(IF)
INSERTING[(campo)]: trigger disparado por INSERT
UPDATING[(campo)]: trigger disparado por UPDATE
DELETING[(campo)]: trigger disparado por DELETE
REMOVENDO
DROP TRIGGER nome_trigger
MENSAGEM DE ERRO (trigger e subrogramas)
RAISE_APPLICATION_ERROR(cod, 'msg')
Código entre -20000 e -20999
Mensagem até 2048 caracteres