No artigo anterior conhecemos as estruturas da linguagem PL/SQL. Nesta edição, veremos a construção de triggers, stored procedures e functions, objetos que encapsulam e permitem a execução de blocos PL/SQL através de aplicações front-end.
Stored Procedure é um bloco de código PL/SQL armazenado no servidor, que não retorna valor. As Stored Procedures são passivas, ou seja, alguma aplicação precisa solicitar sua execução. Function é um bloco PL/SQL que retorna valor. Assim como a stored procedure, uma function precisa ser solicitada por alguma aplicação.
Triggers também são procedimentos PL/SQL armazenados. A diferença é que triggers são ativas, ou seja, são acionadas automaticamente a partir de um evento que representa uma ação sobre a tabela. Esses eventos estão relacionados a comandos insert, update ou delete. Por exemplo, podemos associar uma trigger ao evento insert de uma tabela. Sempre que um registro for inserido nesta tabela, o código da trigger será executado, automaticamente. Uma trigger pode chamar uma stored procedure, que por sua vez pode chamar outra stored procedure.
Utilizando Triggers
Em relação a forma como são acionadas, existem três tipos de triggers:
- Row-level: executada para cada registro afetado pelo comando SQL. Neste caso, um comando UPDATE que afeta dez registros dispara a trigger dez vezes, uma para cada registro.
- Statement-level: disparada apenas uma vez para cada comando de atualização. Por exemplo, um comando UPDATE sobre dez registros acionará a trigger apenas uma vez.
- Instead-Of: Essa trigger é associada a uma view, permitindo simular views atualizáveis. O mecanismo é simples: define-se no corpo da trigger as instruções SQL correspondentes para cada tabela participante da view.
A trigger é acionada pelos comandos INSERT, DELETE ou UPDATE executados na tabela associada. Independente da trigger ser do tipo statement ou row-level, seu disparo pode acontecer em dois momentos distintos: antes ou depois da execução do comando de atualização. A trigger instead-of é sempre do tipo row-level e é disparada por ação, não existindo os eventos after e before.
Observe a sintaxe para criação de uma trigger:
CREATE [ OR REPLACE ] TRIGGER Nome
(INSTEAD OF / (BEFORE/AFTER)) Comando
ON Tabela
[ FOR EACH ROW ]
[ WHEN Condição ]
[ DECLARE lista de variáveis/constantes/objetos ]
Begin
{ Bloco de códigos PL/SQL }
End;
Onde,
- [ OR REPLACE ] : Não existe um comando do tipo ‘ALTER TRIGGER’. Essa cláusula permite alterar uma trigger já existente;
- Nome: Nome da trigger a ser criada/alterada;
- INSTEAD OF / (BEFORE/AFTER): Momento de disparo da trigger: antes (before) ou depois (after) da execução do comando. Se a trigger for instead of as palavras after/before não devem ser utilizadas;
- Comando: Comando que acionará a trigger: INSERT, UPDATE ou DELETE;
- [ FOR EACH ROW ]: Indica que a trigger é do tipo row-level, ou seja, é executada para cada linha afetada pelo comando;
- [ WHEN Condição ]: Condicional para execução da trigger;
- [ DECLARE ]: Seção de declaração de variáveis, constantes, objetos, procedures ou funções utilizadas internamente na trigger;
- { Bloco de códigos PL/SQL }: ‘Programa’ PL/SQL que será executado.
Como exemplo prático, vamos criar um log sobre as operações de alteração e exclusão em uma tabela do banco de dados. Uma tabela chamada LogOperacao armazenará o log, que será gerado por duas triggers, uma before update e outra after delete. O script de criação e preenchimento das tabelas de exemplo está descrito na Listagem 1.
CREATE TABLE
CARGO
(IdCargo INTEGER NOT NULL PRIMARY KEY,
NomeCargo VARCHAR(50),
PercAumento NUMBER(5,2));
CREATE TABLE FUNCIONARIO
(IDFUNCIONARIO NUMBER PRIMARY KEY NOT NULL,
NOMEFUNCIONARIO VARCHAR2(100),
SALARIO NUMBER(17,2), FLGGERENTE CHAR(1),
IDCARGO NUMBER,
FOREIGN KEY (IDCARGO) REFERENCES CARGO (IDCARGO));
CREATE TABLE LOGOPERACAO
(IDLOGOPERCAO NUMBER PRIMARY KEY NOT NULL,
USUARIO VARCHAR2(30),
DATA DATE,
DESCRICAO VARCHAR2(1000));
CREATE SEQUENCE SEQLOGOPERACAO START WITH 1 NOCACHE;
INSERT INTO CARGO VALUES (1, 'GERENTE DE PROJETO', 15.75);
INSERT INTO CARGO VALUES (2, 'ANALISTA DE SISTEMAS', 10.00);
INSERT INTO FUNCIONARIO VALUES (1,’JOSÉ DAS COUVES’,1500,’S’, 1);
INSERT INTO FUNCIONARIO VALUES (2,’ANTONIO FLORENÇO’,3725.50,’N’, 2);
INSERT INTO FUNCIONARIO VALUES (3,’JUSTINA FEIJÓ’,2725.50,’N’, 2);
INSERT INTO FUNCIONARIO VALUES (4,’MARIA ORACLINA’,8500,’N’, 2);
Listagem 1 – Criação de tabela para teste de trigger
O código completo das triggers está disponível na Listagem 2.
/* Trigger para log o Update */
CREATE OR REPLACE TRIGGER UPDATE_FUNCIONARIO
BEFORE UPDATE ON FUNCIONARIO
FOR EACH ROW
DECLARE
sDecricao VARCHAR2(1000);
bAlterouRegistro Boolean := False;
BEGIN
sDecricao := '';
IF :NEW.IDFUNCIONARIO <> :OLD.IDFUNCIONARIO THEN
sDecricao := sDecricao || ' Campo: IDFUNCIONARIO' ||
' Valor Atual: ' || :NEW.IDFUNCIONARIO ||
' Valor Anterior: ' || :OLD.IDFUNCIONARIO;
bAlterouRegistro := True;
END IF;
IF :NEW.NOMEFUNCIONARIO <> :OLD.NOMEFUNCIONARIO THEN
sDecricao := sDecricao || ' Campo: NOMEFUNCIONARIO' ||
' Valor Atual: ' || :NEW.NOMEFUNCIONARIO ||
' Valor Anterior: ' || :OLD.NOMEFUNCIONARIO;
bAlterouRegistro := True;
END IF;
IF :NEW.SALARIO <> :OLD.SALARIO THEN
sDecricao := sDecricao || ' Campo: SALARIO' ||
' Valor Atual: ' || :NEW.SALARIO ||
' Valor Anterior: ' || :OLD.SALARIO;
bAlterouRegistro := True;
END IF;
IF :NEW.FLGGERENTE <> :OLD.FLGGERENTE THEN
sDecricao := sDecricao || ' Campo: FLGGERENTE' ||
' Valor Atual: ' || :NEW.FLGGERENTE ||
' Valor Anterior: ' || :OLD.FLGGERENTE;
bAlterouRegistro := True;
END IF;
IF bAlterouRegistro THEN
sDecricao := 'Alteração da Tabela Funcionario' || sDecricao;
INSERT INTO LOGOPERACAO
(IDLOGOPERCAO, USUARIO, DATA, DESCRICAO)
VALUES
(SEQLOGOPERACAO.NEXTVAL, USER, SYSDATE, sDecricao);
END IF;
END;
/* Trigger para log do Delete */
CREATE OR REPLACE TRIGGER DELETE_FUNCIONARIO
AFTER DELETE ON FUNCIONARIO
FOR EACH ROW
DECLARE
sDecricao LOGOPERACAO.DESCRICAO%type;
BEGIN
sDecricao := 'Exclusão da Tabela Funcionario' ||
' Campo: IDFUNCIONARIO' ||
' Valor: ' || :OLD.IDFUNCIONARIO ||
' Campo: NOMEFUNCIONARIO' ||
' Valor: ' || :OLD.NOMEFUNCIONARIO ||
' Campo: SALARIO' ||
' Valor: ' || :OLD.SALARIO ||
' Campo: FLGGERENTE' ||
' Valor: ' || :OLD.FLGGERENTE;
INSERT INTO LOGOPERACAO
(IDLOGOPERCAO, USUARIO, DATA, DESCRICAO)
VALUES
(SEQLOGOPERACAO.NEXTVAL, USER, SYSDATE, sDecricao);
END;
Listagem 2 – Criação das triggers de Log de Operações para a tabela funcionario
Observe que elas são do tipo row-level, pois especificamos a cláusula FOR EACH ROW. A trigger de update testa e compara os valores atuais, representados pela variável interna :NEW, com os valores anteriores ao update, representados pela variável :OLD. O log só acontece se os valores de NEW e OLD forem diferentes. Para testar a trigger, execute os comandos a seguir:
DELETE FROM FUNCIONARIO WHERE IDFUNCIONARIO = 2;
UPDATE FUNCIONARIO SET SALARIO = SALARIO * 1.25 WHERE IDFUNCIONARIO = 1; UPDATE FUNCIONARIO SET SALARIO = SALARIO;
Em seguida, execute um SELECT na tabela LogOperacao. O resultado é exibido na Listagem 3. A primeira operação de update faz o log apenas da coluna SALARIO, pois somente esse campo tem seu valor alterado. A segunda linha de update não gera log, pois não provoca nenhuma alteração real.
IDLOGOPERCAO USUARIO DATA DESCRICAO
1 SCOTT 14/02/03 Exclusão da Tabela Funcionario
Campo: IDFUNCIONARIO
Valor: 2
Campo: NOMEFUNCIONARIO
Valor: ANTONIO FLORENÇO
Campo: SALARIO
Valor: 4507,86
2 SCOTT 14/02/03 Alteração da Tabela Funcionario
Campo: SALARIO
Valor Atual: 2835,94
Valor Anterior: 2268,75
Listagem 3 –Visualização dos registros da tabela de log
Variáveis NEW e OLD
Em uma trigger do tipo INSERT, podemos acessar os valores que serão gravados através da variável :NEW. No caso de uma trigger UPDATE, temos acesso aos valores alterados através do variável :OLD e dos valores a serem atribuídos através de :NEW. Para um comando DELETE estão disponíveis apenas os valores antigos, através de :OLD, uma vez que o registro não existirá após a execução do comando. O acesso a esses valores fornece flexibilidade para efetuação de testes e validações na trigger.
Triggers Instead-of
Vejamos outro tipo de trigger. Crie uma view chamada GERENTES, com o comando abaixo:
CREATE VIEW GERENTES AS
SELECT IDFUNCIONARIO, NOMEFUNCIONARIO, SALARIO FROM FUNCIONARIO WHERE FLGGERENTE = 'S'
Uma trigger do tipo instead of, no evento INSERT desta view, permitirá a inserção de dados como se estivéssemos trabalhando com uma tabela. Veja a seguir o código para criação da trigger:
CREATE OR REPLACE TRIGGER INSERT_GERENTES
INSTEAD OF INSERT ON GERENTES
FOR EACH ROW
BEGIN
INSERT INTO FUNCIONARIO
(IDFUNCIONARIO, NOMEFUNCIONARIO, SALARIO, FLGGERENTE)
VALUES
(:NEW.IDFUNCIONARIO, :NEW.NOMEFUNCIONARIO, :NEW.SALARIO, 'S');
END;
A eficácia da trigger pode ser comprovada através do comando:
INSERT INTO GERENTES VALUES (6,’João da Silva’,2500);
Triggers Statement-Level
Para exemplificar esta trigger, vamos utilizar a mesma idéia de log. O comando para criar o exemplo é:
CREATE OR REPLACE TRIGGER LOG_UPDATE_FUNCIONARIO
BEFORE UPDATE ON FUNCIONARIO
BEGIN
INSERT INTO LOGOPERACAO
(IDLOGOPERCAO, USUARIO, DATA, DESCRICAO)
VALUES
(SEQLOGOPERACAO.NEXTVAL, USER, SYSDATE, 'O USUÁRIO ALTEROU REGISTROS NA TABELA');
END;
Note que a ausência da frase “FOR EACH ROW” caracteriza a trigger como statement-level. Para testar, execute o comando:
UPDATE FUNCIONARIO SET SALARIO = SALARIO * 1.1;
SELECT * FROM LOGOPERACAO;
Observe que a mensagem 'O USUÁRIO ALTEROU...' será cadastrada na tabela de log apenas uma vez, independente da quantidade de registros alterados. Um fato interessante é que a trigger row-level UPDATE_FUNCIONARIO, criada anteriormente, continua ativa, inserindo ocorrências na tabela de log para registro alterado.
Algumas considerações sobre o uso de Triggers
Em alguns casos, como rotinas de importação ou manutenção de erros, é interessante suspender a ativação automática da trigger. Para isso, podemos utilizar o comando:
ALTER TRIGGER nome_da_trigger DISABLE
Para reativar a trigger utilize:
ALTER TRIGGER nome ENABLE
- A exclusão de uma trigger é efetuada através do comando DROP TRIGGER nome.
- Dentro da trigger não é permitido a utilização de comandos COMMIT e ROLLBACK ou de stored procedures que executem tais tarefas.
- Uma trigger não pode executar comandos SELECT, INSERT, UPDATE ou DELETE na tabela associada. Para recuperar dados nesta tabela, utilize as variáveis :NEW e :OLD.
- Podemos visualizar as triggers do banco de dados dando um SELECT em DBA_TRIGGERS ou ALL_TRIGGERS.
- É possível criar triggers para eventos relacionados ao sistema, como shut dow ou start up em uma instância, eventos relacionados ao usuário, como log on e log off, ou ainda eventos associados a objetos, como create, alter ou drop. Este artigo explora somente as triggers associadas a inserts, deletes e updates em tabelas.
Utilizando Stored Procedures
A sintaxe para criação de uma stored procedure é:
CREATE [ OR REPLACE ] PROCEDURE nome [ (lista de parâmetros) ] AS
[ DECLARE lista de variávies/constantes/obetos/procedures/funções ]
Begin
{ Bloco de códigos PL/SQL }
End;
Onde,
- [ OR REPLACE ]: Não existe um comando do tipo ‘ALTER PROCEDURE’. Essa cláusula permite alterar uma stored procedure já existente;
- Nome: Nome da stored procedure a ser criada/alterada;
- [ (lista de parâmetros) ]: parâmetros passados pela aplicação que solicitou a execução da procedure;
- [ DECLARE ]: Seção de declaração de variáveis, constantes, objetos, procedures ou funções utilizadas internamente na stored procedure;
- { Bloco de códigos PL/SQL }: ‘Programa’ PL/SQL a ser executado.
A execução de uma Stored Procedure pode ser feita diretamente pelo SQL*Plus ou por qualquer aplicativo front-end através do comando:
EXECUTE nome_da_procedure
A Listagem 4 exibe um exemplo de procedure que atualiza o salário de um funcionário de acordo com o percentual indicado no seu cargo. A procedure é executada através do comando EXECUTE PROCEDURE CorrigeSalario(1) , onde 1 é o código do empregado.
Utilizando Functions
Functions são blocos de código PL/SQL que retornam valor. O interessante é que podemos chamá-las a partir de qualquer outro código PL/SQL ou dentro de uma cláusula SQL. A sintaxe para criação de uma function é a seguinte:
CREATE [ OR REPLACE ] FUNCTION nome [ (lista de parâmetros) ]
RETURN tipo de dado AS
[ DECLARE lista de variávies/constantes/obetos ]
BEGIN
{ Bloco de códigos PL/SQL }
Return Valor
End;
A Listagem 5 mostra uma função que retorna o percentual do salário de um gerente em relação ao somatório dos salários de todos os dirigentes.
CREATE OR REPLACE PROCEDURE CorrigeSalario
( iIdFuncionario FUNCIONARIO.IdFuncionario%TYPE)
AS
nPercAumento CARGO.PercAumento%TYPE;
BEGIN
SELECT C.PercAumento INTO nPercAumento
FROM CARGO C, FUNCIONARIO F
WHERE
F.IdCargo = C.IdCargo AND
F.IdFuncionario = iIdFuncionario;
UPDATE
FUNCIONARIO
SET Salario = Salario * (1 + (nPercAumento/100 ) )
WHERE
iIdFuncionario = iIdFuncionario;
END;Listagem 4– Criação da Stored Procedure
CREATE OR REPLACE FUNCTION PERCENT_SAL_GERENTES(nIdFuncionario Number)
RETURN NUMBER
AS
iNumGerentes Integer;
dSalarioGerentes Number;
dPercentSal Number;
BEGIN
SELECT COUNT(*) INTO iNumGerentes
FROM FUNCIONARIO
WHERE FLGGERENTE = 'S';
SELECT SUM(SALARIO) INTO dSalarioGerentes
FROM FUNCIONARIO
WHERE FLGGERENTE = 'S';
IF dSalarioGerentes = 0 THEN
RETURN dPercentSal;
ELSE
SELECT ( SALARIO * 100 / dSalarioGerentes ) INTO dPercentSal FROM FUNCIONARIO WHERE
IDFUNCIONARIO = nIdFuncionario;
RETURN ROUND(dPercentSal,2);
END IF;
END;
Listagem 5
Se o identificador passado como parâmetro não for de um gerente, o valor retornado será zero. Para testar a função, podemos executar o comando a seguir:
SELECT NOMEFUNCIONARIO AS GERENTE, PERCENT_SAL_GERENTES(IDFUNCIONARIO)
FROM FUNCIONARIO WHERE FLGGERENTE = ‘S’;
O Oracle fornece uma tabela para execução e teste de funções, chamada dual. A figura 1 exibe um exemplo de uso desta tabela.
Figura 1
Utilizando Packages
Os packages funcionam como containers, agrupando vários objetos do banco de dados de uma vez. O package torna a distribuição das procedures e functions mais organizada, permitindo criar grupos de atividades em comum. Outra vantagem é que após a primeira leitura, os packages permanecem em memória, tornando a execução dos objetos mais veloz. A sintaxe para criação do package é vista a seguir:
CREATE PACKAGE nome AS
[ seção de declaração ]
END;
CREATE [ OR REPLACE ] PACKAGE BODY nome AS
[ seção de declaração ]
[ implementação de procedures, functions, inicialização ]
END;
ALTER PACKAGE nome COMPILE;
A criação de um package é feita em duas etapas: primeiro definimos a especificação e em seguida o corpo do pacote.
A especificação contém as declarações dos objetos contidos no package. O corpo do package contém toda a implementação dos objetos declarados na especificação. Após a criação dessas seções o package deve ser montado e compilado para que possa ser acessado de forma otimizada.
Como exemplo, vamos utilizar alguns objetos criados neste artigo, encapsulando-os em um package, chamado MANUT_FUNCIONARIOS. O código completo para criação do package está disponível na Listagem 6.
CREATE PACKAGE MANUT_FUNCIONARIOS AS
FUNCTION CONTA_GERENTES RETURN INTEGER;
PROCEDURE CorrigeSalario ( iIdFuncionario FUNCIONARIO.IdFuncionario%TYPE);
END;
CREATE OR REPLACE PACKAGE BODY MANUT_FUNCIONARIOS AS
--
FUNCTION CONTA_GERENTES
RETURN INTEGER
AS
iNumGerentes Integer;
BEGIN
SELECT COUNT(*) INTO iNumGerentes
FROM FUNCIONARIO
WHERE FLGGERENTE = 'S';
RETURN iNumGerentes;
END;
--
PROCEDURE CorrigeSalario
( iIdFuncionario FUNCIONARIO.IdFuncionario%TYPE)
AS
nPercAumento CARGO.PercAumento%TYPE;
BEGIN
SELECT
C.PercAumento INTO nPercAumento
FROM
CARGO C, FUNCIONARIO F
WHERE
F.IdCargo = C.IdCargo AND
F.IdFuncionario = iIdFuncionario;
UPDATE
FUNCIONARIO
SET
Salario = Salario * (1 + (nPercAumento/100 ) )
WHERE
iIdFuncionario = iIdFuncionario;
END;
--
END;
Listagem 6
Para compilar o package execute o comando:
ALTER PACKAGE manut_funcionarios COMPILE;
Para executar um objeto contido em um package, basta acrescentar o nome do pacote seguido por “ponto”:
SELECT MANUT_FUNCIONARIOS.CONTA_GERENTES FROM DUAL;
A desvantagem deste recurso é a dificuldade de manutenção, pois não é possível manipular os objetos do package individualmente. Se temos um pacote com 10 objetos e precisamos alterar um, o script deve ser igual ao de criação do package, com o objeto em questão alterado.
Verificando erros de criação
Quando um objeto do Oracle é criado com erros, a mensagem a seguir é exibida:
“Aviso: Procedimento criado com erros de compilação.”
O problema é que esta mensagem não dá maiores informações sobre o erro. Para ter acesso aos detalhes, devemos consultar a tabela de sistema ALL_ERRORS, através do comando:
SELECT * FROM ALL_ERRORS WHERE NAME = ‘nome_da_trigger_ou_procedure’.
Conclusão
Nesta matéria vimos conceitos, definições e exemplos referentes a criação de objetos armazenados. Ainda há muito a ser discutido, não deixe de acompanhar novos artigos sobre esse assunto em edições futuras. Até a próxima!
