Criação de stored procedures e triggers
Um dos recursos mais importantes de um sistema gerenciador de banco de dados é sem dúvida nenhuma sua capacidade de executar procedimentos internos e de disparar ações baseadas em eventos ocorridos em uma tabela – são as stored procedures e os triggers. Como não poderia deixar de ser, o InterBase e o Firebird (IB/FB) suportam bem ambos os recursos, fornecendo comandos especiais para serem utilizados nesses casos.
Indo além, o IB/FB permite que você use stored procedures como fonte de dados, ou seja, como se fossem tabelas do banco. São as select stored procedures (que chamaremos de "selecionáveis"). Além disso, a possibilidade de utilizar UDFs (funções definidas pelo usuário) aumenta ainda mais o poder e a funcionalidade das stored procedures e triggers.
Estrutura e criação de uma stored procedure
O comando utilizado para a criação de uma stored procedure (SP) é o CREATE PROCEDURE. Uma SP tem duas partes: cabeçalho e corpo. No cabeçalho, temos a definição do nome da SP, parâmetros de entrada (caso existam) e parâmetros de saída (caso a SP retorne valores). O corpo contém declarações de variáveis e comandos a serem executados. A estrutura é definida da seguinte maneira:
CREATE PROCEDURE nome_procedure (
PARAMETRO1 TIPO, /* lista de parâmetros de entrada */
PARAMETRO2 TIPO)
RETURNS (
RESULTADO1 TIPO, /* lista de parâmetros de saída */
RESULTADO2 TIPO)
AS
DECLARE VARIABLE VAR1 TIPO;
DECLARE VARIABLE VAR2 TIPO;
<... Outras declarações de variáveis ...>
BEGIN
<... Corpo da stored procedure (comandos) ...>
END
Estrutura e criação de um trigger
Semelhante a uma stored procedure, um trigger também possui um cabeçalho e um corpo. No cabeçalho definimos a tabela à qual o trigger está associado e o tipo de evento que irá dispará-lo (veja os eventos suportados na Tabela 1). O corpo do trigger contém a declaração de variáveis e comandos a serem executados. Veja sua estrutura:
CREATE TRIGGER nome_trigger FOR nome_tabela
ACTIVE evento POSITION posição
AS
DECLARE VARIABLE VAR1 TIPO;
DECLARE VARIABLE VAR2 TIPO;
<... Outras declarações de variáveis ...>
BEGIN
<... Corpo do trigger (comandos)...>
END
Em triggers (mas não em SPs) é possível utilizar as variáveis de contexto NEW e OLD, que representam respectivamente os valores atuais e os anteriores dos campos da tabela em questão. Na Tabela 1, temos as situações onde cada variável de contexto pode ser utilizada. Você pode ter vários triggers definidos para um mesmo evento. Eles serão executados na seqüência definida pelo valor da cláusula POSITION.
Nota: se o valor de uma variável NEW for alterado em um evento Before Insert ou Before Update, esse valor não é refletido em triggers criados para o mesmo evento. Sendo assim, se por exemplo uma variável New.campo1 tiver seu valor original 0 alterado para 1, outros triggers definidos para o mesmo evento continuarão enxergando o valor 0.
Tabela 1. Eventos e variáveis de contexto
| Evento | Momento em que é disparado | Variáveis de contexto |
| Before Insert | Antes de uma inserção | New |
| After Insert | Após uma inserção | New* |
| Before Update | Antes de uma atualização | New e Old |
| After Update | Após uma atualização | New* e Old |
| Before Delete | Antes de uma exclusão | Old |
| After Delete | Após uma exclusão | Old |
* Alterar os valores das variáveis nesses eventos não causará qualquer efeito prático.
A linguagem de SPs e triggers
Os bancos InterBase e Firebird suportam uma linguagem própria para a construção de triggers e stored procedures. Basicamente, os mesmos comandos se aplicam tanto a triggers como a SPs, salvo em poucas exceções. A seguir, mostro um resumo da sintaxe e da forma de uso dos principais comandos da linguagem de SPs e triggers do IB/FB.
Variáveis
As variáveis têm papel fundamental nas stored procedures e triggers. Pode-se definir e utilizar variáveis de qualquer tipo de dado suportado no IB/FB, exceto os tipos ARRAY e BLOB. Variáveis no IB/FB são sempre locais, ou seja, são visíveis somente dentro da SP ou trigger em que foram criadas. Valores são atribuídos a variáveis usando o operador de atribuição "=":
Variável = Valor;
Aqui, Valor pode conter um valor imediato (1, 2, 'A' etc.), o resultado de uma expressão (como x/y+2), ou até mesmo chamadas a UDFs. Note o “;” obrigatório no final da sentença, semelhante ao Pascal.
Importante: referências a variáveis e parâmetros devem ser precedidas por um “:” (dois-pontos).
Comentários
Comentários em SPs e triggers são delimitados por /* e */. Por exemplo:
IF (new.status = ‘A’) THEN /* Verificar se foi aprovado */
Blocos
Um bloco é uma seqüência de comandos e instruções delimitados por BEGIN e END. Como em Delphi, um bloco pode conter outros blocos. No Firebird e no InterBase, os blocos devem terminar com um END seguido do terminador. O terminador é definido com o comando SET TERM. No exemplo da Listagem 1, o terminador foi definido como sendo o acento circunflexo (^).
EXCEPTION
O comando EXCEPTION gera uma exceção previamente definida pelo usuário (a definição é feita usando o comando CREATE EXCEPTION). A sintaxe básica é a seguinte:
EXCEPTION nome;
A exceção a ser gerada é definida em nome. Quando o comando EXCEPTION é executado, ele termina a execução da SP ou do trigger e desfaz todas as operações executadas anteriormente por eles. O tratamento de exceções pelo usuário pode ser feito usando comandos WHEN...DO (veja adiante). Por exemplo:
CREATE EXCEPTION estoque_negativo
"O estoque deve ser maior que zero";
...
IF (New.estoque < style="">THEN
EXCEPTION estoque_negativo;
Nota: o Firebird 1.5 traz a possibilidade de gerar exceções dinâmicas; com essa versão, você não precisa criar uma exceção antes de gerá-la.
EXECUTE PROCEDURE
O comando EXECUTE PROCEDURE permite chamar stored procedures de dentro de triggers ou SPs. As chamadas podem ser recursivas (o limite é de mil níveis de recursão). Caso a procedure sendo chamada tenha parâmetros de entrada ou de saída, estes devem ser informados obrigatoriamente.
A sintaxe para esse comando é a seguinte (as partes entre colchetes são opcionais):
EXECUTE PROCEDURE nome [parametros_entrada] [parametros_saida];
Veja um exemplo de chamada:
DECLARE VARIABLE Produto CHAR(4);
DECLARE VARIABLE CustoMedio NUMERIC(9,2);
...
produto = ‘0001’;
EXECUTE PROCEDURE CalculaCustoMedio:Produto
RETURNING_Values:CustoMedio;
IF...THEN...ELSE
A condição em um IF deve sempre estar entre parênteses. Como de costume, o ELSE é executado quando a condição avaliada for falsa. Você também pode utilizar blocos de código dentro de IFs (veja o exemplo na Listagem 1) e IFs aninhados. Um exemplo:
if (:status = 1) then
:desc_status = 'OK';
else
:desc_status = 'FALHOU';
Nesse exemplo, observe que diferentemente do Pascal, onde o “;” pode determinar o final de um if, no IB/FB isso não acontece.
POST_EVENT
POST_EVENT faz com que seja enviado um evento do servidor para todas as aplicações que estejam registradas para receber esse evento. A sintaxe é a seguinte:
POST_EVENT ‘nome_do_evento’ | campo_de_uma_tabela;
Após a aplicação cliente receber a notificação de que o evento foi disparado, ela pode realizar ações como atualizar os dados, replicar alguma informação em outro banco etc.
SELECT...INTO
Você também pode executar SELECTs que retornem no máximo uma linha a partir de SPs ou triggers e armazenar o resultado em uma variável ou parâmetro. Na Listagem 1, um SELECT desse tipo é usado para recuperar os valores dos débitos e créditos dos lançamentos anteriores para posteriormente calcular o saldo inicial.
Nota: até o InterBase 6, a única maneira de se garantir que um SELECT retornaria apenas uma linha era usando funções de agregação, como o SUM, COUNT, MAX etc. No Firebird 1.0 você pode garantir isso usando a cláusula “FIRST 1” do SELECT e no InterBase 6.5 ou 7.0 usando “ROWS 1”.
EXIT e SUSPEND
Os comandos EXIT e SUSPEND só podem ser usados em SPs e funcionam de maneira diferente de acordo com o tipo de stored procedure. Veja as diferenças na Tabela 2 (adaptada do manual "Language Reference" do InterBase 6).
Tabela 2. Funcionamento de EXIT e SUSPEND
| Tipo de SP | SUSPEND | EXIT | END final |
| SP selecionável | Suspende a execução da procedure até que o próximo FETCH seja recebido e retorna os valores de saída | Vai para o END final | Retorna o controle para a aplicação e altera SQLCODE para 100, significando que não há mais registros a serem retornados |
| SP executável | Vai para o END final (não é recomendado seu uso nesse tipo de SP) | Vai para o END final | Retorna os valores e devolve o controle para a aplicação |
WHEN...DO
WHEN..DO é o comando responsável pelo tratamento de erros em SPs e triggers. Quando um erro é gerado, as ações executadas dentro do bloco BEGIN-END atual são desfeitas e um WHEN é procurado. Caso não seja encontrado um WHEN dentro do bloco atual, o servidor volta um nível na cadeia de blocos BEGIN-END à procura desse comando e assim por diante. Se um WHEN for encontrado, a ação (ou bloco de ações) definida por ele é executada e o fluxo retorna para o bloco BEGIN-END onde foi encontrado. Caso nenhum WHEN seja encontrado, todas as ações executadas pela SP são desfeitas.
Os erros que podem ser capturados pelo WHEN são EXCEPTIONs, SQLERRORs (erros de SQL), GDSERRORs (erros do IB/FB) e ANY (qualquer um dos anteriores). Veja um exemplo do uso do WHEN na Listagem 2.
WHILE…DO
O WHILE é o único comando de loop genérico disponível para SPs e triggers, mas é suficiente para executar praticamente todo tipo de loop necessário. A Listagem 2 contém um exemplo de uso do WHILE.
Listagem 2. Stored procedure para elevar um número a uma potência
CREATE PROCEDURE POWER (
NUMERO NUMERIC(9,2),
EXPOENTE INTEGER)
RETURNS (
RESULTADO NUMERIC(15,2))
AS
DECLARE VARIABLE CONTADOR INTEGER;
BEGIN
resultado = -1; /* Em caso de erro, retorna -1 */
/* A rotina só funciona para expoentes positivos */
IF (:expoente <>THEN
EXCEPTION exp_negativo;
contador = 0;
resultado = 1;
WHILE (:contador <:expoente) DO
BEGIN
resultado = resultado * numero;
contador = contador + 1;
/* Caso ocorra algum erro (como um overflow, retorna -1) */
WHEN ANY DO
BEGIN
resultado = -1;
EXIT;
END
END
END
^
FOR SELECT...DO
O FOR SELECT é um dos comandos mais importantes em SPs e triggers. Permite que você abra um cursor unidirecional e percorra os dados retornados pelo SELECT. Nesse tipo de SELECT, é exigido o uso da cláusula INTO para receber os dados dos campos especificados. Essa é a única forma de se trabalhar com cursores de dentro de SPs e triggers.
A Listagem 1 usa dois loops FOR SELECT para percorrer as tabelas de débito e crédito retornando os registros desejados e incrementando o valor do saldo final. Esses loops alimentam algumas das variáveis de saída da procedure (que serão retornadas quando for executada).
Stored procedures selecionáveis
O suporte a stored procedures selecionáveis é um dos recursos mais poderosos do IB/FB. Com ele você pode utilizar uma stored procedure como fonte de dados para os seus SELECTs, como se a SP fosse uma tabela do seu banco de dados.
O efeito é o mesmo de ter uma “tabela virtual” montada com informações vindas de uma ou mais tabelas do banco de dados, sendo que essas informações podem ser transformadas, calculadas etc. para montar a tabela.
Para demonstrar o uso desse tipo de stored procedure, criei um exemplo simples de um fluxo de caixa, onde temos uma tabela para contas a receber e outra para contas a pagar. A intenção é que possamos obter dados para o fluxo de caixa em determinado período, ordenado por data, listando os lançamentos e seus tipos (débitos e créditos), incluindo o saldo inicial (antes do período informado) e o saldo final. O código da SP pode ser visto na Listagem 1. O script para a criação do banco de dados está disponível para download no site da Revista. Na seção seguinte, explico cada parte do código.
Listagem 1. Stored procedure para um fluxo de caixa
CREATE PROCEDURE CAIXA (
INICIO DATE,
FIM DATE)
RETURNS (
DATA DATE,
HISTORICO VARCHAR(30),
VALOR NUMERIC(9,2),
TIPO CHAR(1))
AS
DECLARE VARIABLE SALDO_ANT_CREDITO NUMERIC(9,2);
DECLARE VARIABLE SALDO_ANT_DEBITO NUMERIC(9,2);
DECLARE VARIABLE SALDO_FINAL NUMERIC(9,2);
begin
/* Recupera a soma dos débitos anteriores ao período informado */
SELECT sum(ap.valor)
FROM apagar ap
WHERE (ap.data <:inicio)
INTO:saldo_ant_debito;
/* Recupera a soma dos créditos anteriores ao período informado */
SELECT sum(ar.valor)
FROM areceber ar
WHERE (ar.data <:inicio)
INTO:saldo_ant_credito;
/* Trata nulos */
IF (:saldo_ant_credito IS NULL) THEN
saldo_ant_credito = 0.00;
IF (:saldo_ant_debito IS NULL) THEN
saldo_ant_debito = 0.00;
data =:inicio - 1;
historico = 'SALDO ANTERIOR';
valor = (saldo_ant_credito - saldo_ant_debito);
/* Trata valores negativos */
IF (:valor <>THEN
BEGIN
tipo = 'D';
valor = valor * -1;
END
ELSE
BEGIN
tipo = 'C';
END
SUSPEND;
/* Retorna a primeira linha com o saldo anterior */
saldo_final = valor;
/* Seleciona os registros dentro do período informado
buscando na tabela de contas a pagar */
tipo = 'D'; /* Estamos recuperando débitos */
FOR SELECT ap.data, ap.historico, ap.valor
FROM apagar ap
WHERE (ap.data BETWEEN:inicio and:fim)
INTO:data,:historico,:valor
DO
BEGIN
saldo_final = saldo_final - valor;
/* O suspend a seguir retorna uma linha contendo os campos
data, histórico, tipo e valor */
SUSPEND;
END
tipo = 'C'; /* Estamos recuperando créditos */
/* Seleciona os registros dentro do período informado
buscando na tabela de contas a receber */
FOR SELECT ar.data, ar.historico, ar.valor
FROM areceber ar
WHERE (ar.data BETWEEN:inicio and:fim)
INTO:data,:historico,:valor
DO
BEGIN
saldo_final = saldo_final + valor;
/* O suspend a seguir retorna uma linha contendo os campos
data, historico, tipo e valor */
SUSPEND;
END
/* Montamos a última linha, com os valores do saldo final */
data =:fim;
historico = 'SALDO FINAL';
tipo = 'C';
valor = saldo_final;
SUSPEND;
END
^
Análise da stored procedure
A stored procedure CAIXA tem como parâmetros de entrada as datas de início e final do período que desejamos visualizar e, como parâmetros de saída, os “campos” da nossa “tabela virtual” (data, historico, valor e tipo). Como vimos anteriormente, essas informações são definidas no cabeçalho da SP:
CREATE PROCEDURE CAIXA (
INICIO DATE,
FIM DATE)
RETURNS (
DATA DATE,
HISTORICO VARCHAR(30),
VALOR NUMERIC(9,2),
TIPO CHAR(1))
Em seguida definimos duas variáveis numéricas que armazenarão os totais de débitos e créditos anteriores ao período informado, para que possamos então calcular o saldo anterior, além de uma terceira variável (saldo_final), para armazenar o saldo final do período:
DECLARE VARIABLE SALDO_ANT_CREDITO NUMERIC(9,2);
DECLARE VARIABLE SALDO_ANT_DEBITO NUMERIC(9,2);
DECLARE VARIABLE SALDO_FINAL NUMERIC(9,2);
O próximo passo é obter os valores para o cálculo do saldo anterior. Usamos dois SELECTs com a função SUM para totalizar os lançamentos (débitos e créditos) anteriores à data inicial:
SELECT SUM(ap.valor)
FROM apagar ap
WHERE (ap.data <:inicio) INTO:saldo_ant_debito;
SELECT SUM(ar.valor)
FROM areceber ar
WHERE (ar.data <:inicio) INTO:saldo_ant_credito;
A seguir devemos verificar se as variáveis são nulas (isso ocorrerá se não houver nenhum lançamento anterior ao dia inicial). Como você sabe, NULL dentro de qualquer cálculo matemático faz com que o resultado da operação também seja NULL, o que pode fazer com que o saldo anterior fique incorreto. Sendo assim, checamos se alguma das variáveis possui valor NULL. Caso positivo, atribuímos a ela o valor zero:
IF (:saldo_ant_credito IS NULL) THEN
saldo_ant_credito = 0.00;
IF (:saldo_ant_debito IS NULL) THEN
saldo_ant_debito = 0.00;
O próximo passo é montar a primeira linha do resultado da nossa SP, contendo os dados do saldo anterior. Definimos o parâmetro data como o dia anterior à data inicial informada, depois descrevemos o histórico como sendo “SALDO ANTERIOR” e calculamos o saldo com base nas variáveis anteriormente calculadas.
Para não mostrar valores negativos no resultado, verificamos se o saldo anterior é negativo; de for, o convertemos para um valor positivo. Nesse caso o lançamento é feito como débito, caso contrário, lançamos como crédito.
data =:inicio - 1;
historico = 'SALDO ANTERIOR';
valor = (saldo_ant_credito - saldo_ant_debito);
IF (:valor < 0) THEN
BEGIN
tipo = 'D';
valor = valor * -1;
END
ELSE
BEGIN
tipo = 'C';
END
SUSPEND;
A linha contendo o SUSPEND fará com que a nossa SP devolva o primeiro registro com os valores atribuídos aos parâmetros de saída. O processamento da stored procedure continua a partir dali assim que ela recebe uma requisição de fetch da aplicação cliente.
Seguindo a seqüência, devemos inicializar a variável saldo_final com o saldo anterior obtido:
saldo_final = valor;
O próximo passo é recuperar os lançamentos no período informado. Primeiramente, vamos percorrer a tabela de contas a pagar. Ela fornece os lançamentos de débito, portanto devemos alterar o parâmetro tipo para “D” (débito). Feito isso, usamos um FOR SELECT para varrer todos os registros dentro do período especificado, armazenando os valores dos campos data, historico e valor nos respectivos parâmetros de saída que formarão uma linha da “tabela virtual”. Note que dentro do loop atualizamos o saldo final com base no campo valor do registro que está sendo percorrido.
Obtidos todos os dados necessários, precisamos pedir à SP que retorne um novo registro para a aplicação, chamando mais uma vez SUSPEND. Note que para cada linha percorrida pelo FOR SELECT, teremos um SUSPEND retornando uma linha do resultado. Depois repetimos o processo para a tabela de contas a receber, alterando tipo para “C” (crédito) e somando os valores ao saldo final, em vez de subtraí-los. A seqüência pode ser observada no seguinte código:
tipo = 'D';
FOR SELECT ap.data, ap.historico, ap.valor
FROM apagar ap
WHERE (ap.data BETWEEN:inicio AND:fim)
INTO:data,:historico,:valor
DO
BEGIN
saldo_final = saldo_final - valor;
SUSPEND;
END
tipo = 'C';
FOR SELECT ar.data, ar.historico, ar.valor
FROM areceber ar
WHERE (ar.data BETWEEN:inicio AND:fim)
INTO:data,:historico,:valor
DO
BEGIN
saldo_final = saldo_final + valor;
SUSPEND;
END
Agora só nos resta retornar um registro contendo nosso saldo final. Montamos o registro com bases nos dados já coletados, como pode ser visto a seguir:
data =:fim;
historico = 'SALDO FINAL';
tipo = 'C';
valor = saldo_final;
SUSPEND;
Chamando a procedure
Uma stored procedure selecionável pode ser chamada em um SELECT como se fosse uma tabela comum do banco de dados. Veja um exemplo de chamado à nossa SP:
SELECT * FROM CAIXA ('15.1.2003','1.3.2003')
ORDER BY data
Note que as datas do período estão sendo passadas usando “.” ao invés da tradicional “/”. O ponto diz ao IB/FB que a data está sendo passada no formato dia/mês/ano. Se fosse usado “/”, deveria usar o formato americano mês/dia/ano. Na Figura 1 podemos ver um exemplo de resultado desse comando.
Figura 1. Resultado de SELECT sobre a procedure selecionável CAIXA
Exemplo com triggers
Para exemplificar o uso de um trigger, veremos como criar um campo “auto-incremento” no IB/FB (na verdade estamos simulando um campo auto-incremento, visto que esse tipo de dado não é suportado o IB/FB).
Para que possamos fazer isso, devemos primeiramente criar um generator, que nada mais é do que um contador interno de 64 bits dentro do banco. Para isso usamos o comando CREATE GENERATOR, como em:
CREATE GENERATOR GEN_APAGAR_ID;
Agora que temos o generator criado, o que precisamos fazer é definir um trigger no evento Before Insert (ou seja, antes que uma inserção aconteça), que irá incrementar o nosso contador e atribuir o valor dele ao nosso campo "auto-incremento". Verifique o código:
CREATE TRIGGER APAGAR_BI_BI FOR APAGAR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_APAGAR_ID,1);
END
^
No exemplo, ID é o campo auto-incremento e só recebe um valor caso já não tenha sido inicializado. Isso permite uma flexibilização que não ocorre no tipo auto-incremento real encontrado no Paradox, por exemplo. Dessa maneira, podemos forçar um registro a ter um determinado ID simplesmente atribuindo um valor a ele antes de confirmar as mudanças no registro. O trigger irá detectar que o campo não está nulo e portanto não incrementará o generator.
Nota: GEN_ID é uma função interna do servidor que incrementa um generator e retorna o novo valor.
