Stored Procedures e Triggers no Oracle - artigo SQL Magazine 03

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

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.

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.

exemplo de uso da 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!

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?