Procedures, Funções e Trigger

Prévia do material em texto

Procedures e Funções em PL/SQL 
 
 
Procedures 
 
Uma procedure nada mais é do um bloco PL/SQL nomeado. A grande vantagem 
sobre um bloco PL/SQL anônimo é que pode ser compilado e armazenado no banco de 
dados como um objeto de schema. Graças a essa característica as procedures são de 
fácil manutenção, o código é reutilizável e permitem que trabalhemos com módulos de 
programa. 
Uma procedure é, então, um bloco PL/SQL nomeado que pode aceitar 
argumentos (também chamado de parâmetros) e pode ser chamada por um programa, 
uma sessão SQL ou uma trigger. 
Durante a instalação do banco de dados Oracle um script é executado 
automaticamente e cria toda a estrutura necessária para que as procedures sejam 
executadas. Eventualmente esse procedimento automático pode falhar devido a 
alguma falha física no disco rígido, nesse caso o usuário SYS pode recriar a estrutura 
através do script SQL DBMSSTDX.SQL. 
Para criar uma procedure o usuário precisa ter o privilégio de sistema CREATE 
PROCEDURE, para criar a procedure em outros schemas o usuário deve ter o 
privilégio de CREATE ANY PROCEDURE. Este é um ponto muito interessante sobre 
as procedures, os privilégios para criação de procedures têm que ser concedidos 
explicitamente, ou seja, não pode ser adquirido através de roles. 
Para executar uma procedure externa é necessário ter o privilégio de EXECUTE. Caso 
queira alterar a procedure de outro schema deve ter o privilégio de sistema ALTER 
ANY PROCEDURE. 
A sintaxe básica de uma procedure é: 
 
 
 
 
 
 
 
CREATE [OR REPLACE] PROCEDURE [schema.]nome_da_procedure 
[(parâmetro1 [modo1] tipodedado1, 
 parâmetro2 [modo2] tipodedado2, 
 ...)] 
IS|AS 
Bloco PL/SQL 
Onde: 
 REPLACE – indica que caso a procedure exista ela será eliminada e 
substituída pela nova versão criada pelo comando; 
 BLOCO PL/SQL – inicia com uma cláusula BEGIN e termina com END 
ou END nome_da_procedure; 
 NOME_DA_PROCEDURE – indica o nome da procedure; 
 PARÂMETRO – indica o nome da variável PL/SQL que é passada na 
chamada da procedure ou o nome da variável que retornará os valores 
da procedure ou ambos. O que irá conter em parâmetro depende de 
MODO; 
 MODO – Indica que o parâmetro é de entrada (IN), saída (OUT) ou 
ambos (IN OUT). É importante notar que IN é o modo default, ou seja, 
se não dissermos nada o modo do nosso parâmetro será, 
automaticamente, IN; 
 TIPODEDADO – indica o tipo de dado do parâmetro. Pode ser 
qualquer tipo de dado do SQL ou do PL/SQL. Pode usar referencias 
como %TYPE, %ROWTYPE ou qualquer tipo de dado escalar ou 
composto. Atenção: não é possível fazer qualquer restrição ao 
tamanho do tipo de dado neste ponto. 
 IS|AS – a sintaxe do comando aceita tanto IS como AS. Por 
convenção usamos IS na criação de procedures e AS quando 
estivermos criando pacotes. 
 BLOCO PL/SQL – indica as ações que serão executadas por aquela 
procedure. 
Exemplo de procedure: 
 
 
 
 
 
 
 
 
CREATE OR REPLACE PROCEDURE Aumenta_Salario (F_Cd_Funcionario IN 
FUNCIONARIO.Cd_Funcionario%TYPE) IS 
BEGIN 
 UPDATE 
 FUNCIONARIO 
 SET 
 Cd_Salario = Cd_Salario * 1.10 
 WHERE 
 Cd_Funcionario = F_Cd_Funcionario; 
END Aumenta_Salario; 
 
Neste exemplo estamos criando uma procedure para aumentar o salário de um 
funcionário em 10%. A primeira linha define o NOME DA PROCEDURE, que vai ser 
AUMENTA_SAL. 
A linha dois define o parâmetro P_EMPNO no modo IN. Ou seja, vai ser um 
dado informado na chamada da procedure. Em seguida determinamos que ele será do 
mesmo tipo e tamanho que a coluna EMPNO da tabela EMP. Isso é feito através da 
referência EMP.EMPNO%TYPE. 
Podemos verificar o estado de nossa procedure através de uma simples 
consulta: 
 
 
 
 
Agora podemos verificar o funcionamento de nossa procedure: 
 
 
 
 
 
 
 
 
Ou 
 
 
 
 
 
 
 
 
Podemos notar que o salário do funcionário 001 aumentou em 10%. 
 
 
SELECT object_name, status 
 FROM user_objects 
 WHERE object_name LIKE '%AUMENTA%'; 
 
SELECT Cd_Funcionario, Cd_Salario from FUNCIONARIO; 
FUNC SAL 
---------- ---------- 
001 10000 
002 6000 
003 4000 
CALL AUMENTA_SAL(001); 
 
EXECUTE AUMENTA_SAL(001); 
SELECT Cd_Funcionario, Cd_Salario from FUNCIONARIO; 
 EMPNO SAL 
---------- ---------- 
 001 11000 
 002 6000 
 003 4000 
 
Funções 
 
Sintaxe Básica: 
 
 
 
 
 
 
 
 
Ao contrário das procedures as funções tem que retornar ao menos um valor. 
 
 
 
 
 
 
 
 
 
 
Executando 
 
 
 
 
 
CREATE [OR REPLACE] FUNCTION nome_da_função 
[( parameter1 [ mode1] datatype1, 
parameter2 [ mode2] datatype2, 
. . .)] 
RETURN tipo_de_dado 
IS|AS 
Bloco PL/SQL; 
 
CREATE OR REPLACE FUNCTION Get_Salario(F_Cd_Funcionario IN 
FUNCIONARIO.Cd_Funcionario%TYPE) RETURN NUMBER IS valor_salario 
FUNCIONARIO.Cd_Salario%TYPE; 
BEGIN 
 SELECT Cd_Salario 
 INTO valor_salario 
 FROM FUNCIONARIO 
 WHERE Cd_Funcionario = F_Cd_Funcionario; 
 RETURN valor_salario; 
END Get_Salario; 
 
VARIABLE Get_Sal NUMBER; 
EXECUTE :Get_Sal := Get_Salario('001'); 
PRINT Get_Sal; 
SQL 
 
 
Gatilho (Trigger) 
Apesar de Trigger SQL serem muito úteis, não é aconselhável a utilização deste 
tipo de instrução, pelo fato de prejudicar a performance. A trigger é um tipo de 
procedure que é executada automaticamente após uma ação especifica que ocorre em 
uma tabela ou view (por isso prejudica a performance). 
Definição é um bloco PL/SQL que é disparado de forma automática e implícita 
sempre que ocorrer um evento associado a uma tabela: 
 INSERT 
 UPDATE 
 DELETE 
Não pode ser chamado explicitamente. 
 
Utilidades 
 
Manutenção de tabelas de auditoria, manutenção de tabelas duplicatas, 
implementação de níveis de segurança mais complexos, geração de valores de 
colunas referentes a atributos derivados, validação de restrições de integridade mais 
complexas que as suportadas diretamente pelo SGBD. 
 
Estrutura 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Cláusulas 
 Tempo 
o BEFORE: antes do evento 
o AFTER: depois do evento 
 Eventos de disparo 
o INSERT 
o UPDATE 
o DELETE 
 WHEN 
o Restringe as tuplas que disparam o gatilho. 
 COMANDO 
o Aciona o gatilho antes ou depois de um comando, 
independentemente do número de tuplas afetadas 
o Não requer a cláusula FOR EACH ROW 
CREATE OR REPLACE TRIGGER nome_gatilho 
BEFORE | AFTER 
DELETE OR INSERT OR UPDATE OF coluna1, coluna2, ... 
ON nome_da_tabela / view 
REFERENCING OLD AS nome NEW AS nome 
FOR EACH ROW 
WHEN condição 
DECLARE 
área de declaração 
BEGIN 
área de comandos 
END; 
 
O gatilho somente será 
disparado se alguma coluna 
especificada após a cláusula 
UPDATE OF for alterada 
 
o Não permite o uso dos prefixos :OLD e :NEW 
 LINHA 
o Aciona o gatilho uma vez para cada linha afetada pelo comando ao 
qual o gatilho está associado 
o Requer a cláusula FOR EACH ROW 
o Permite o uso dos prefixos :OLD e :NEW 
o Permite o uso das cláusulas REFERENCING e WHEN 
o Não pode ler ou modificar a tabela à qual o gatilho está associado, 
caso da Tabela Mutante. 
 Referências aos valores dos atributos 
o :NEW.nome_atributo 
 Indica um novo valor para um campo que está sendo 
alterado por um comando INSERT ou UPDATE 
o :OLD.nome_atributo 
 Indica o valor anterior de um campo que está sendo alterado 
por um comando DELETE ou UPDATE 
REFERENCING OLD AS antigo NEW ASnovo substitui OLD por antigo e NEW 
POR novo. 
 
Restrições ao uso de Triggers 
 O número máximo de gatilhos que podem ser especificados por tabela é 
12. 
 Não é possível criar dois gatilhos diferentes com as mesmas 
características para uma mesma tabela. 
 Chaves primárias, únicas ou estrangeiras não podem ser alteradas por 
gatilhos. 
 Não podemos realizar os comandos COMMIT, ROLLBACK e 
SAVEPOINT em um Trigger, mesmo que seja uma procedure executada 
em um Trigger. 
 Não podemos fazer select na mesma tabela que sofre a ação de um 
Trigger, pois isso pode provocar um erro chamado MUTANT TABLE. 
Mesmo porque se quisermos saber o valor de uma coluna do registro que 
está sendo tratado em um Trigger basta colarmos :new.nomecoluna ou 
:old.nomecoluna para termos respectivamente os valores atuais e 
anteriores a alteração. 
 
Predicados: 
 Retornam TRUE se o gatilho foi disparado por: 
o INSERT: predicado inserting. 
o UPDATE: predicado updating. 
o DELETE: predicado deleting. 
 Exemplo 
IF inserting THEN comandos_inserção; 
ELSIF deleting THEN comandos_remoção; 
ELSE comandos_atualização; 
END IF; 
Tipos de Gatilhos 
 
Os Triggers podem ser do tipo ROW LEVEL (Linha) ou STATEMENT (Tabela). 
Trigger ROW LEVEL: 
 Os Triggers do tipo ROW LEVEL podem ser usados sempre que 
precisarmos que um Trigger trate de valores em uma transação, e por sua 
vez são disparados a cada ocorrência de uma transação sobre uma 
tabela. Se um UPDATE atualizar, por exemplo, 1000 linhas em uma 
tabela que possua um Trigger de Update do tipo row level, serão 
disparadas 1000 vezes. 
 Os Triggers do tipo row level são utilizadas para operações como: 
o Gravação de LOGS de auditoria de uma aplicação; 
o Verificação de dados (Consistência); 
o Implementação de integridade referencial; 
 
Trigger STATEMENT: 
 Os Triggers do tipo STATEMENT tem a finalidade de tratar a execução de 
ações sobre tabelas independentemente de quantas linhas forem 
afetadas. Através deste tipo de Trigger podemos registrar a execução de 
comandos INSERT, UPDATE e DELETE contra tabelas que tenham 
Triggers contemplando essas ações. Caso um comando UPDATE 
atualize 1000 linhas, um Trigger deste tipo apenas dispararia 1 única vez. 
Este tipo de Trigger não pode referenciar qualquer valor contido em uma 
coluna da tabela. Isso ocorre porque se o mesmo dispara uma única vez. 
 Este tipo de Trigger funciona nos casos de registro de transações 
ocorridas, independentemente do número de linhas afetadas. 
 
COLUMN Trigger: 
 Triggers de Coluna são disparados sempre que a determinada coluna 
relacionada no Trigger sofrer a ação ligada ao mesmo. Com isso se a 
ação ocorrer em outras colunas que não aquelas associadas ao Trigger, o 
Trigger não será disparado. Isso evita de certa forma que um Trigger seja 
disparado se a ação não ocorra, nada será disparado. Isso pode ser útil 
em casos de auditoria de mudanças em determinadas colunas, por 
exemplo: 
o Toda vez que um salário for alterado na tabela de funcionário um 
Trigger gravará em uma tabela chamada log_funcionário um 
registro contendo o código do empregado e os salários anteriores e 
atuais. 
Exemplo 
 
 
 
 
 
 
 
TABLE Trigger: 
 
 Trigger de tabela ocorrem independentemente das colunas afetadas pela 
ação. Isso quer dizer que se uma ação ocorre, não importa qual coluna 
CREATE OR REPLACE TRIGGER Auditoria_Funcionário AFTER INSERT OR 
DELETE OR UPDATE OF Cd_Salario ON FUNCIONARIO REFERENCING OLD AS 
Antigo NEW AS Novo FOR EACH ROW 
BEGIN 
INSERT INTO LOG_FUNCIONARIO (Cd_Funcionario, Cd_Salario_Ant, 
Cd_Salario_Atu, Cd_user, Dt_trasac) 
VALUES (:Novo.Cd_Funcionario, :Antigo.Cd_Salario , 
:Novo.Cd_Salario , user, SYSDATE); 
END; 
seja afetada, a ação que o Trigger cobre não está ligado a nenhuma 
coluna. Estes Triggers podem ser úteis em ações como o exemplo a 
seguir; 
 Toda vez que um dado referente ao funcionário for alterado (não importa 
a coluna) a trigger bkp_funcionario é executada fazendo a cópia de toda a 
tabela funcionario para a tabela bkp_funcionario. 
Exemplo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Momento (Antes ou Depois) 
Antes (Before): 
 Os Triggers do tipo BEFORE como podemos deduzir, disparam antes que 
a ação ocorra. Isso leva a entender que antes que uma ação de banco de 
dados ocorra o Trigger será disparado, o que pode fazer com que a ação 
nem venha a ocorrer. Um Trigger pode impedir que uma ação venha a 
ocorrer, portanto podemos usar um Trigger deste tipo em situações como, 
por exemplo: 
o Validação de dados; 
o Carregamento de dados obrigatórios (datas, usuários, etc..); 
o Impedimento de ações em horários não previstos. 
After – (Depois): 
 Os Triggers do tipo AFTER ocorrem depois que a ação tenha ocorrido, ou 
seja eles são disparados depois, com isso NÃO podemos com esses tipos 
de Triggers fazer o que fazemos com Triggers do tipo BEFORE. Aqui a 
ação já ocorreu então o que podemos fazer com Triggers deste tipo é a 
auditoria. 
 
Modificadores OLD e NEW 
 
Podemos nos casos de Triggers de linha, fazer referência a valores contidos nas 
colunas e com isso podemos querer saber os valores antes da alteração e depois dos 
valores efetivamente alterados. Isso vale na ação de UPDATE, nos casos de INSERT e 
DELETE os valores de OLD (INSERT) e NEW (DELETE) são nulos. Estes 
modificadores podem ser usados APENAS em TRIGGERS. Não podemos usá-los em 
procedures, functions ou packages. 
 Os valores são referenciados da seguinte forma :OLD.nomecoluna e 
:NEW.nomecoluna; 
CREATE OR REPLACE TRIGGER Bkp_Funcionário AFTER INSERT OR DELETE 
OR UPDATE ON FUNCIONARIO REFERENCING OLD AS Antigo NEW AS Novo 
BEGIN 
 DELETE BKP_FUNCIONARIO; 
INSERT INTO BKP_FUNCIONARIO (Cd_Funcionario, De_Nome, 
Cd_Unidade, Cd_Departamento, cd_cargo, dt_admis, dt_demis, 
cd_salario, Cd_user, Dt_trasac) 
SELECT F.Cd_Funcionario as Cd_Funcionario, F.De_Nome as 
De_Nome, F.Cd_Unidade as Cd_Unidade, F.Cd_Departamento as 
Cd_Departamento, F.cd_cargo as cd_cargo, F.dt_admis as 
dt_admis, F.dt_demis as dt_demis, F.cd_salario as cd_salario, 
user as Cd_user, SYSDATE as Dt_trasac 
 FROM FUNCIONARIO F ; 
END; 
 Não importa se o Trigger for BEFORE ou AFTER os modificadores OLD e 
NEW não são afetados. 
 
Clausula WHEN 
 
Caso o Trigger tenha alguma condição para ser executado, podemos incluir uma 
cláusula chamada WHEN. Nesta colocamos as condições que o Trigger irá disparar. 
Caso precisemos tratar o valor de alguma coluna, usamos os modificadores OLD e 
NEW, mas nessa clausula não colocaremos os : na frente, pois nesse caso ocorrerá 
erro. 
Exemplo 
 
 
 
 
 
 
 
 
 
 
No exemplo acima o Trigger somente será executado (BEGIN...END) se a 
condição WHEN vier a ocorrer. 
 
Operadores, INSERTING, UPDATING E DELETING 
 
Podemos criar Triggers para serem disparados para várias ações de banco de 
dados. Dessa forma como podemos diferenciar uma ação de insert de outra de delete 
ou ainda update. Estes modificadores podem ser feitos APENAS dentro de Triggers, 
portanto se usarmos esses operadores em procedures, functions, Packages ou mesmo 
PL/SQL anônimos os mesmos ocasionarão erros de compilação. 
Exemplo 
 
 
 
 
 
 
 
 
 
 
 
 
 
Comando INSTEAD OF 
 
 
Triggers deste tipo foram implementadas a partir da versão 9i e tem a finalidade 
de permitir que havendo ações de modificação sobre visões, que os comandos possam 
ser realizados nas tabelas associadas a essas visões. 
create or replace TRIGGER Audita_Transacao2 BEFORE UPDATE ON 
FUNCIONARIO 
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
WHEN (NEW.Dt_Demis is not null) 
BEGIN 
 raise_application_error(-20001, 'Operação não pode ser 
executada. O funcionario ' || :OLD.Cd_Funcionario|| ' esta 
demitido!'); 
END; 
 
 
create or replace TRIGGER Auditoria_Funcionário2 AFTER INSERT OR DELETE OR UPDATE 
ON FUNCIONARIO REFERENCING OLD AS Antigo NEW AS Novo FOR EACH ROW 
BEGIN 
 IF INSERTING THEN 
INSERT INTO LOG_FUNCIONARIO (Cd_Funcionario, Cd_Salario_Ant, Cd_Salario_Atu, 
Fg_Acao, Cd_user, Dt_trasac) 
VALUES (:Novo.Cd_Funcionario, :Antigo.Cd_Salario , :Novo.Cd_Salario , 'I', 
user, SYSDATE); 
 ELSIF UPDATING THEN 
INSERT INTO LOG_FUNCIONARIO (Cd_Funcionario, Cd_Salario_Ant, Cd_Salario_Atu, 
Fg_Acao, Cd_user, Dt_trasac) 
VALUES (:Novo.Cd_Funcionario, :Antigo.Cd_Salario , :Novo.Cd_Salario , 'U', 
user, SYSDATE); 
 ELSIF DELETING THEN 
INSERT INTO LOG_FUNCIONARIO (Cd_Funcionario, Cd_Salario_Ant, Cd_Salario_Atu, 
Fg_Acao, Cd_user, Dt_trasac) 
VALUES (:Novo.Cd_Funcionario, :Antigo.Cd_Salario , :Novo.Cd_Salario , 'D', 
user, SYSDATE); 
 END IF; 
END; 
Em outras palavras o INSTEAD OF indica que a trigger irá ser executada no 
lugar da instrução que disparou a trigger. Literalmente, a instrução é substituída pela 
trigger. Essa técnica permite que façamos, por exemplo, alterações em uma tabela 
através de uma view. É usado nos casos em que a view não pode alterar uma tabela 
por não referenciar uma coluna com a constraint not null. Nesse caso a trigger pode 
atualizar a coluna que a view não tem acesso. 
Dois detalhes muito importantes sobre INSTEAD OF: 
 Só funcionam com views; 
 É sempre de linha. Será considerado assim, mesmo que "FOR EACH 
ROW" for omitido. 
Exemplo 
 
 
 
 
 
 
Habilitar/Desabilitar 
ALTER TRIGGER 
 Habilita/desabilita um ou mais gatilhos 
 
 
 
Exemplo: nome do gatilho 
 ALTER TRIGGER Audita_Transacao ENABLE; 
 ALTER TRIGGER Funcionario ENABLE ALL TRIGGERS; 
 nome da tabela 
Compilar 
ALTER TRIGGER 
 Compila novamente um gatilho 
 
 
 
Exemplo: 
 ALTER TRIGGER Audita_Transacao COMPILE; 
 
Remover 
 
DROP TRIGGER 
 Remove um gatilho do banco de dados 
 
 
Exemplo: 
 DROP TRIGGER Audita_Transacao; 
 
 
ALTER TRIGGER nome_gatilho 
ENABLE | DISABLE; 
ALTER TRIGGER nome_gatilho 
COMPILE; 
DROP TRIGGER nome_gatilho; 
create or replace TRIGGER NOVO_FUNCIONARIO 
INSTEAD OF INSERT ON EMPREGADOS FOR EACH ROW 
BEGIN 
INSERT INTO EMPREGADOS (Cd_Funcionario, De_Nome, Cd_Unidade, 
Cd_Departamento, cd_cargo, dt_admis, dt_demis, cd_salario) 
VALUES (:NEW.Cd_Funcionario, :NEW.De_Nome, :NEW.Cd_Unidade, 
:NEW.Cd_Departamento, :NEW.cd_cargo, :NEW.dt_admis, 
:NEW.dt_demis, :NEW.cd_salario ); 
END;