Procedures, asfsFunções e Trigger

7/25/2019 Procedures, asfsFunes e Trigger

1/10


2/10

Onde:

REPLACE indica que caso a procedure exista ela ser eliminada e

substituda pela nova verso criada pelo comando;

BLOCO PL/SQL inicia com uma clusula BEGIN e termina com END

ou END nome_da_procedure;

NOME_DA_PROCEDURE indica o nome da procedure;

PARMETRO indica o nome da varivel PL/SQL que passada na

chamada da procedure ou o nome da varivel que retornar os valores

da procedure ou ambos. O que ir conter em parmetro depende de

MODO;

MODO Indica que o parmetro de entrada (IN), sada (OUT) ou

ambos (IN OUT). importante notar que IN o modo default, ou seja,

se no dissermos nada o modo do nosso parmetro ser,

automaticamente, IN;

TIPODEDADO indica o tipo de dado do parmetro. 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. Ateno: no possvel fazer qualquer restrio ao

tamanho do tipo de dado neste ponto.

IS|AS a sintaxe do comando aceita tanto IS como AS. Por

conveno usamos IS na criao de procedures e AS quando

estivermos criando pacotes.

BLOCO PL/SQL indica as aes que sero executadas por aquela

procedure.

Exemplo de procedure:

CREATE OR REPLACE PROCEDURE Aumenta_Salario (F_Cd_Funcionario INFUNCIONARIO.Cd_Funcionario%TYPE) ISBEGIN

UPDATEFUNCIONARIO

SETCd_Salario = Cd_Salario * 1.10

WHERECd_Funcionario = F_Cd_Funcionario;

END Aumenta_Salario;


3/10

Neste exemplo estamos criando uma procedure para aumentar o salrio de um

funcionrio em 10%. A primeira linha define o NOME DA PROCEDURE, que vai ser

AUMENTA_SAL.

A linha dois define o parmetro 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 atravs da

referncia EMP.EMPNO%TYPE.

Podemos verificar o estado de nossa procedure atravs de uma simples

consulta:

Agora podemos verificar o funcionamento de nossa procedure:

Ou

Podemos notar que o salrio do funcionrio 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


4/10

Funes

Sintaxe Bsica:

Ao contrrio das procedures as funes tem que retornar ao menos um valor.

Executando

CREATE [OR REPLACE] FUNCTION nome_da_funo[( 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 FUNCIONARIOWHERE Cd_Funcionario = F_Cd_Funcionario;

RETURN valor_salario;

END Get_Salario;

VARIABLE Get_Sal NUMBER;EXECUTE :Get_Sal := Get_Salario('001');

PRINT Get_Sal;


5/10

SQL

Gatilho (Trigger)Apesar de Trigger SQL serem muito teis, no aconselhvel a utilizao deste

tipo de instruo, pelo fato de prejudicar a performance. A trigger um tipo deprocedure que executada automaticamente aps uma ao especifica que ocorre emuma tabela ou view (por isso prejudica a performance).

Definio um bloco PL/SQL que disparado de forma automtica e implcitasempre que ocorrer um evento associado a uma tabela:

INSERT UPDATE DELETE

No pode ser chamado explicitamente.

Utilidades

Manuteno de tabelas de auditoria, manuteno de tabelas duplicatas,implementao de nveis de segurana mais complexos, gerao de valores decolunas referentes a atributos derivados, validao de restries de integridade maiscomplexas que as suportadas diretamente pelo SGBD.

Estrutura

Clusulas Tempo

o BEFORE: antes do eventoo AFTER: depois do evento

Eventos de disparoo INSERTo UPDATEo DELETE

WHENo Restringe as tuplas que disparam o gatilho.

COMANDOo Aciona o gatilho antes ou depois de um comando,

independentemente do nmero de tuplas afetadaso No requer a clusula FOR EACH ROW

CREATE OR REPLACE TRIGGER nome_gatilhoBEFORE | AFTER

DELETE OR INSERT OR UPDATE OF coluna1, coluna2, ...ON nome_da_tabela / viewREFERENCING OLD AS nome NEW AS nomeFOR EACH ROWWHEN condio

DECLARErea de declarao

BEGINrea de comandos

END;

O gatilho somente serdisparado se alguma colunaespecificada aps a clusulaUPDATE OF for alterada


6/10

o No permite o uso dos prefixos :OLD e :NEW LINHA

o Aciona o gatilho uma vez para cada linha afetada pelo comando aoqual o gatilho est associado

o Requer a clusula FOR EACH ROW

o Permite o uso dos prefixos :OLD e :NEWo Permite o uso das clusulas REFERENCING e WHENo No pode ler ou modificar a tabela qual o gatilho est associado,

caso da Tabela Mutante. Referncias aos valores dos atributos

o :NEW.nome_atributo Indica um novo valor para um campo que est sendo

alterado por um comando INSERT ou UPDATEo :OLD.nome_atributo

Indica o valor anterior de um campo que est sendo alteradopor um comando DELETE ou UPDATE

REFERENCING OLD AS antigo NEW AS novo substitui OLD por antigo e NEWPOR novo.

Restries ao uso de Triggers

O nmero mximo de gatilhos que podem ser especificados por tabela 12.

No possvel criar dois gatilhos diferentes com as mesmascaractersticas para uma mesma tabela.

Chaves primrias, nicas ou estrangeiras no podem ser alteradas porgatilhos.

No podemos realizar os comandos COMMIT, ROLLBACK eSAVEPOINT em um Trigger, mesmo que seja uma procedure executada

em um Trigger.

No podemos fazer select na mesma tabela que sofre a ao 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 eanteriores a alterao.

Predicados: Retornam TRUE se o gatilho foi disparado por:

o INSERT: predicado inserting.o UPDATE: predicado updating.o DELETE: predicado deleting.

ExemploIF inserting THEN comandos_insero;

ELSIF deleting THEN comandos_remoo;ELSE comandos_atualizao;

END IF;


7/10

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 transao, e por suavez so disparados a cada ocorrncia de uma transao sobre umatabela. Se um UPDATE atualizar, por exemplo, 1000 linhas em umatabela que possua um Trigger de Update do tipo row level, serodisparadas 1000 vezes.

Os Triggers do tipo row level so utilizadas para operaes como:o Gravao de LOGS de auditoria de uma aplicao;o Verificao de dados (Consistncia);o Implementao de integridade referencial;

Trigger STATEMENT: Os Triggers do tipo STATEMENT tem a finalidade de tratar a execuo de

aes sobre tabelas independentemente de quantas linhas foremafetadas. Atravs deste tipo de Trigger podemos registrar a execuo decomandos INSERT, UPDATE e DELETE contra tabelas que tenhamTriggers contemplando essas aes. Caso um comando UPDATEatualize 1000 linhas, um Trigger deste tipo apenas dispararia 1 nica vez.Este tipo de Trigger no pode referenciar qualquer valor contido em umacoluna da tabela. Isso ocorre porque se o mesmo dispara uma nica vez.

Este tipo de Trigger funciona nos casos de registro de transaesocorridas, independentemente do nmero de linhas afetadas.

COLUMN Trigger: Triggers de Coluna so disparados sempre que a determinada coluna

relacionada no Trigger sofrer a ao ligada ao mesmo. Com isso se aao ocorrer em outras colunas que no aquelas associadas ao Trigger, oTrigger no ser disparado. Isso evita de certa forma que um Trigger sejadisparado se a ao no ocorra, nada ser disparado. Isso pode ser tilem casos de auditoria de mudanas em determinadas colunas, porexemplo:

o Toda vez que um salrio for alterado na tabela de funcionrio umTrigger gravar em uma tabela chamada log_funcionrio um

registro contendo o cdigo do empregado e os salrios anteriores eatuais.Exemplo

TABLE Trigger:

Trigger de tabela ocorrem independentemente das colunas afetadas pelaao. Isso quer dizer que se uma ao ocorre, no importa qual coluna

CREATE OR REPLACE TRIGGER Auditoria_Funcionrio AFTER INSERT ORDELETE OR UPDATE OF Cd_Salario ON FUNCIONARIO REFERENCING OLD ASAntigo NEW AS Novo FOR EACH ROWBEGIN

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;


8/10

seja afetada, a ao que o Trigger cobre no est ligado a nenhumacoluna. Estes Triggers podem ser teis em aes como o exemplo aseguir;

Toda vez que um dado referente ao funcionrio for alterado (no importaa coluna) a trigger bkp_funcionario executada fazendo a cpia 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 quea ao ocorra. Isso leva a entender que antes que uma ao de banco dedados ocorra o Trigger ser disparado, o que pode fazer com que a aonem venha a ocorrer. Um Trigger pode impedir que uma ao venha aocorrer, portanto podemos usar um Trigger deste tipo em situaes como,por exemplo:

o Validao de dados;o Carregamento de dados obrigatrios (datas, usurios, etc..);o Impedimento de aes em horrios no previstos.

After (Depois): Os Triggers do tipo AFTER ocorrem depois que a ao tenha ocorrido, ou

seja eles so disparados depois, com isso NO podemos com esses tiposde Triggers fazer o que fazemos com Triggers do tipo BEFORE. Aqui a

ao j ocorreu ento o que podemos fazer com Triggers deste tipo aauditoria.

Modificadores OLD e NEW

Podemos nos casos de Triggers de linha, fazer referncia a valores contidos nascolunas e com isso podemos querer saber os valores antes da alterao e depois dosvalores efetivamente alterados. Isso vale na ao de UPDATE, nos casos de INSERT eDELETE os valores de OLD (INSERT) e NEW (DELETE) so nulos. Estesmodificadores podem ser usados APENAS em TRIGGERS. No podemos us-los emprocedures, functions ou packages.

Os valores so referenciados da seguinte forma :OLD.nomecoluna e:NEW.nomecoluna;

CREATE OR REPLACE TRIGGER Bkp_Funcionrio AFTER INSERT OR DELETEOR UPDATE ON FUNCIONARIO REFERENCING OLD AS Antigo NEW AS NovoBEGIN

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 asDe_Nome, F.Cd_Unidade as Cd_Unidade, F.Cd_Departamento as

Cd_Departamento, F.cd_cargo as cd_cargo, F.dt_admis asdt_admis, F.dt_demis as dt_demis, F.cd_salario as cd_salario,user as Cd_user, SYSDATE as Dt_trasacFROM FUNCIONARIO F ;

END;


9/10

No importa se o Trigger for BEFORE ou AFTER os modificadores OLD eNEW no so afetados.

Clausula WHEN

Caso o Trigger tenha alguma condio para ser executado, podemos incluir umaclusula chamada WHEN. Nesta colocamos as condies que o Trigger ir disparar.Caso precisemos tratar o valor de alguma coluna, usamos os modificadores OLD eNEW, mas nessa clausula no colocaremos os : na frente, pois nesse caso ocorrererro.

Exemplo

No exemplo acima o Trigger somente ser executado (BEGIN...END) se acondio WHEN vier a ocorrer.

Operadores, INSERTING, UPDATING E DELETING

Podemos criar Triggers para serem disparados para vrias aes de banco dedados. Dessa forma como podemos diferenciar uma ao de insert de outra de deleteou ainda update. Estes modificadores podem ser feitos APENAS dentro de Triggers,portanto se usarmos esses operadores em procedures, functions, Packages ou mesmoPL/SQL annimos os mesmos ocasionaro erros de compilao.

Exemplo

Comando INSTEAD OF

Triggers deste tipo foram implementadas a partir da verso 9i e tem a finalidadede permitir que havendo aes de modificao sobre vises, que os comandos possamser realizados nas tabelas associadas a essas vises.

create or replace TRIGGER Audita_Transacao2 BEFORE UPDATE ONFUNCIONARIOREFERENCING NEW AS NEW OLD AS OLD FOR EACH ROWWHEN (NEW.Dt_Demis is not null)BEGIN

raise_application_error(-20001, 'Operao no pode serexecutada. O funcionario ' || :OLD.Cd_Funcionario || ' estademitido!');END;

create or replace TRIGGER Auditoria_Funcionrio2 AFTER INSERT OR DELETE OR UPDATEON FUNCIONARIO REFERENCING OLD AS Antigo NEW AS Novo FOR EACH ROWBEGIN

IF INSERTING THENINSERT 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 THENINSERT 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;


10/10

Em outras palavras o INSTEAD OF indica que a trigger ir ser executada nolugar da instruo que disparou a trigger. Literalmente, a instruo substituda pelatrigger. Essa tcnica permite que faamos, por exemplo, alteraes em uma tabelaatravs de uma view. usado nos casos em que a view no pode alterar uma tabelapor no referenciar uma coluna com a constraint not null. Nesse caso a trigger pode

atualizar a coluna que a view no 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 tabelaCompilar

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_gatilhoENABLE | DISABLE;

ALTER TRIGGER nome_gatilhoCOMPILE;

DROP TRIGGER nome gatilho;

create or replace TRIGGER NOVO_FUNCIONARIOINSTEAD OF INSERT ON EMPREGADOS FOR EACH ROWBEGIN

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;

Documents

Procedures, asfsFunções e Trigger