Stored Procedures e Triggers no Oracle

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!

Stored Procedures e Triggers no Oracle - artigo SQL Magazine 03

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.