1

Programando em PSQL
Criação de stored procedures e triggers
POR CARLOS H. CANTU

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 Firebird (FB) suporta ambos os recursos, fornecendo comandos especiais para serem utilizados
nesses casos, que fazem parte do PSQL (Procedural SQL).
Indo além, o 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 stored procedures
"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. As
SPs têm 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
Nota: A variação CREATE OR ALTER pode facilitar a manutenção de um banco de dados pois
permite criar ou alterar uma procedure/trigger quando já existir, sem a necessidade de ter dois
comandos separados (CREATE e ALTER).
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 a estrutura:
CREATE TRIGGER nome_trigger FOR nome_tabela
[ACTIVE | INACTIVE]
{BEFORE | AFTER} 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
 2

Até a versão 1.0, um trigger só poderia estar associado a um único evento (after/before - insert,
update, delete). A partir do FB 1.5, podemos associar um único trigger a vários eventos! Para
identificar o tipo de ação que disparou o trigger foram criadas 3 variáveis: Inserting, Updating e
Deleting, que podem ser checadas no código do trigger e, através de Ifs, condicionar os comandos a
serem executados, como mostrado no código abaixo:
CREATE TRIGGER TABELA_BIU FOR TABELA
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
if (inserting) then
begin
/* Executa alguma operação quando for inserção */
end
else
if (updating) then
begin
/* Executa alguma operação quando for edição */
end
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 anteriores dos campos da tabela em questão. Na
Tabela 1, temos as situações em que cada variável de contexto pode ser utilizada. O FB permite ter
vários triggers definidos para um mesmo evento. Eles serão executados na sequência definida pelo
valor da cláusula POSITION. Variáveis OLD são sempre ReadOnly.

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 NEW nesses eventos não causará qualquer efeito prático nas
versões antigas do FB, mas resultará em erro nas versões mais novas.
A linguagem de SPs e triggers

O Firebird suporta uma linguagem própria para a construção de triggers e stored procedures,
chamada PSQL. A seguir, mostro um resumo da sintaxe e da forma de uso dos principais comandos
da linguagem de SPs e triggers do FB disponíveis no Firebird 1.5. Cada nova versão do Firebird
introduz novos comandos, portanto, sugiro uma leitura do Guia de Referência de Linguagem, em
[Link]
[Link]#fblangref40-psql para conhecer todos os comando disponíveis atualmente.

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 FB, exceto os tipos ARRAY (e em versões antigas,
também os BLOBs).
Variáveis no 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;
 3

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.

A partir da versão 2.1 do Firebird, é permitido declarar o tipo de uma variável baseado no tipo de
um domínio ou coluna de uma tabela através do type of ou do type of column, por exemplo:
declare variable a type of MEUDOMINIO;
declare variable a type of column [Link];

Importante: referências a variáveis e parâmetros devem ser precedidas por um “:” (dois-pontos)
sempre que puderem ser confundidas com campos de uma tabela.

Comentários

Comentários em SPs e triggers são delimitados por /* e */. Por exemplo:

IF ([Link] = ‘A’) THEN /* Verificar se foi aprovado */
Desde o Firebird 1.5 é possível definir comentários de uma linha através do uso de “--", ex:
x = 10; -- inicializa a variável x com valor 10

Blocos

Um bloco é uma sequência de comandos e instruções delimitados por BEGIN e END. Como no
Pascal, o bloco pode conter outros blocos.
No Firebird, o bloco principal de uma stored procedure ou trigger deve 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 (erro) previamente definida pelo usuário através do
comando CREATE EXCEPTION.
A sintaxe básica é a seguinte:
EXCEPTION nome ‘mensagem de erro’;

A exceção a ser gerada é definida pelo 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 (mais adiante),
por exemplo:
CREATE EXCEPTION estoque_negativo
‘O estoque deve ser maior que zero’;
...
IF ([Link] < 0) THEN
EXCEPTION estoque_negativo;
...
IF ([Link] < 0) THEN –- No Firebird 1.5
EXCEPTION estoque_negativo ’O estoque do produto ‘ || [Link] || ‘
não pode ser negativo!’;
...
 4
WHEN ANY DO
BEGIN
INSERT INTO ERROR_LOG (...) VALUES (SQLCODE, ...);
EXCEPTION; -- Re-raise de exceção no Firebird 1.5
END

Nota: A partir do Firebird 1.5 é possível gerar exceções dinâmicas, definindo a mensagem exibida
para o usuário no momento em que a exceção é gerada. É permitido também que a exceção seja re-
disparada (re-raise) dentro de um bloco de tratamento de exceções.

EXECUTE PROCEDURE

O comando EXECUTE PROCEDURE permite chamar stored procedures de dentro de triggers ou

de SPs. As chamadas podem ser recursivas (o limite é de mil iterações). 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] [retuning_values
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 praxe, o ELSE é executado
quando a condição avaliada for falsa. É permitido utilizar blocos de código dentro de IFs (veja o
exemplo na Listagem 1) e IFs aninhados. Exemplo:
if (:status = 1) then
:desc_status = 'OK';
else
:desc_status = 'FALHOU';

Observe que diferentemente do Pascal, onde o “;” pode determinar o final de um if, no 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 disparar ações
como realizar uma atualização nos 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 variáveis/parâmetros. Na Listagem 1, um SELECT desse tipo é
 5

usado para recuperar os valores dos débitos e créditos dos lançamentos anteriores para posteriormente
calcular o saldo inicial.
Nota: no Firebird, podemos garantir que um select não retorne mais que uma linha usando a cláusula
“FIRST 1” do SELECT.

EXIT e SUSPEND

Esses dois comandos funcionam de maneiras diferentes de acordo com o tipo de stored procedure.
Veja as diferenças na Tabela 2, lembrando que o comando EXIT pode ser utilizado em triggers
somente a partir do Firebird 1.5, e que o SUSPEND só pode ser utilizado em Stored Procedures –
tornando-a uma stored procedure selecionável.

Tabela 2. Funcionamento de EXIT e SUSPEND

Tipo de SP SUSPEND EXIT END final
SP selecionável Suspende a execução da procedure Vai para o END final Retorna o controle para a aplicação
até que o próximo FETCH seja e altera SQLCODE para 100,
recebido e retorna os valores de significando que não há mais
saída. registros a serem retornados.
SP executável Vai para o END final (não é Vai para o END final Retorna os valores e devolve o
recomendado seu uso nesse tipo de controle para a aplicação
SP)

WHEN...DO

WHEN..DO é 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 ao qual
o WHEN pertence. 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 FB) e ANY (qualquer um dos anteriores). Veja um exemplo do uso do
WHEN na Listagem 2.

SQLCODE e GDSCODE
A partir do Firebird 1.5 as variáveis contextuais SQLCODE e GDSCODE estão disponíveis no PSQL e
fornecem um código do tipo inteiro representando o erro relacionado à exceção ativa dentro de um bloco de
tratamento de exceções. Fora do bloco, ambas retornam zero. Com o uso de WHEN ANY ou WHEN
SQLCODE, a variável GDSCODE sempre retornará zero. Com WHEN GDSCODE a variável SQLCODE será
sempre zero. Se a exceção gerada é uma exceção definida pelo usuário, ambas as variáveis serão zero.

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;
 6
BEGIN
resultado = -1; /* Em caso de erro, retorna -1 */
/* A rotina só funciona para expoentes positivos */
IF (:expoente < 0) 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, permitindo abrir um cursor
unidirecional e percorrer os dados retornados pelo SELECT. Nesse tipo de SELECT, é exigido o uso
da cláusula INTO para receber os dados dos campos especificados. Antigamente, essa é a única forma
de se trabalhar com cursores de dentro de SPs e triggers. Versões mais recentes do Firebird
introduziram comandos próprios para a criação e uso de cursores explicitamente no PSQL.
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).

Comandos CASE, COALESCE e NULLIF

O comando CASE permite retornar um valor baseado nas condições definidas. A introdução desse
comando no Firebird 1.5 permitiu a implementação de duas outras funções: COALESCE e NULLIF.
COALESCE retorna o primeiro valor não nulo de uma sequência de valores passados como
parâmetros. NULLIF recebe dois parâmetros de entrada e retorna NULL caso os valores dos 2
parâmetros sejam iguais; caso os valores sejam diferentes, o valor do primeiro parâmetro é retornado.
Internamente COALESCE e NULLIF são convertidos em um CASE.
A listagem 3 mostra a utilização do CASE em um select para substituir o código utilizado no campo
método_pagto (que representa o tipo de pagamento realizado em uma venda) pela sua respectiva
descrição.
Na listagem 4 usamos a função coalesce para obter, em ordem de preferência, o nome fantasia de
uma empresa, ou a razão social ou, caso nenhum desses esteja disponível, a string ‘Sem Nome’. A
função NULLIF utilizada no update faz com que o campo estoque da tabela produtos fique nulo caso
o valor atual dele seja 0.
SELECT v.Nota_Fiscal, [Link],
CASE v.metodo_pagto
WHEN 'V' THEN 'A vista'
WHEN 'P' THEN 'Parcelado'
WHEN 'C' THEN 'Cartão de crédito'
WHEN 'D' THEN 'Cheque Pré-datado'
ELSE 'Tipo desconhecido ''' || v.metodo_pagto || ''''
END
FROM Vendas v;
Listagem 3. Uso do CASE em select
 7
select coalesce (c.nome_fantasia, c.razao_social, 'Sem Nome')
from clientes c;
update produtos set estoque = nullif(estoque,0);
Listagem 4. Exemplos de uso do coalesce e nullif.

[FOR] Execute Statement

Um recurso poderoso que permite montar e executar dinamicamente comandos SQL dentro de
Stored Procedures e Triggers através dos comandos Execute Statement e sua variação For Execute
Statement. O primeiro deve ser utilizado para a execução de comandos que não retornam resultados
(ex: updates, inserts, etc.) ou retornam no máximo 1 linha de dados. O segundo é específico para
comandos que retornam várias linhas de resultado (a maioria dos selects).
Como a engine do banco de dados não pode validar previamente o comando SQL que será montado
em tempo de execução, é necessário tomar cuidado na sua construção pois um comando inválido
provocará uma exceção/erro ao ser executado. Devemos lembrar também que não é possível utilizar
parâmetros nos comandos montados dinamicamente.
O código da listagem 5 mostra um exemplo simples de utilização desse recurso. A procedure SOMA
recebe o nome de um campo e de uma tabela e realiza uma operação de somatória (SUM) nesse
campo, retornando o valor obtido. A procedure selecionável GET_ULT_COMPRA recebe como
parâmetro o nome de uma tabela e uma sequência de códigos de clientes separados por vírgula e
devolve o nome de cada cliente e a data da sua última compra.
CREATE PROCEDURE Soma (Campo VARCHAR(30), Tabela VARCHAR(50))
RETURNS (Resultado NUMERIC (18,2))
AS
BEGIN
EXECUTE STATEMENT ‘SELECT SUM(‘ || Campo || ‘) FROM ’ || Tabela INTO
:Resultado;
END
CREATE PROCEDURE GET_ULT_COMPRA (
VARTABELA VARCHAR(32),
VARCODIGOS VARCHAR(512))
AS
DECLARE VARIABLE VARNOME VARCHAR(50);
DECLARE VARIABLE VARULTCOMPRA DATE;
begin
for execute statement
'select nome, ultcompra from ' || :VarTabela ||
'where codigo in (' || :VarCodigos || ')'
into :VarNome, :VarUltCompra
do
begin
/* Processa as informações */
suspend;
end
end
Listagem 5. Utilização do Execute Statement e For Execute Statement

LEAVE/BREAK/CONTINUE

LEAVE e BREAK permitem interromper o processamento de loops gerados pelos comandos

WHILE, FOR SELECT e FOR EXECUTE, desviando o processamento para o próximo comando
logo após o END final do loop. O padrão SQL-99 aconselha a utilização do LEAVE ao invés do
BREAK, mas ambos têm a mesma finalidade.
Os comandos Leave, Break e Exit podem ser usados tanto em triggers como em stored procedures.
LEAVE pode ser acompanhado de um rótulo, permitindo interromper a execução do loop atual e
também de um loop externo devidamente rotulado.
CONTINUE permite desviar o fluxo de execução para a próxima iteração do loop e, quando
acompanhado de um rótulo, para a próxima iteração de um loop externo.
 8

A listagem 6 mostra um exemplo de utilização do leave para interromper um loop baseado em uma
condição.
conta = 1;
while (:conta < 100) do
begin
-- Realiza algum processamento, como cálculos com as variáveis x e y
if (:x = :y) then leave; -- Se o valor da variável x for igual ao de y,
sai do loop.
conta = conta + 1;
end
Listagem 6. Utilização do Leave para interromper o loop.

Stored procedures selecionáveis

O suporte a stored procedures selecionáveis é um dos recursos mais poderosos do FB. Com ele
podemos utilizar uma stored procedure como fonte de dados para SELECTs, como se a SP fosse uma
tabela do 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 7. Na seção seguinte, explico cada parte
do código.
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([Link])
FROM apagar ap
WHERE ([Link] <:inicio)
INTO:saldo_ant_debito;
/* Recupera a soma dos créditos anteriores ao período informado */
SELECT sum([Link])
FROM areceber ar
WHERE ([Link] <: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);
/* Inicializando a variavel que armazenara o saldo final
com o valor do saldo anterior */
saldo_final = valor;
 9
/* Trata valores negativos */
IF (:valor < 0) 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 [Link], [Link], [Link]
FROM apagar ap
WHERE ([Link] 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 [Link], [Link], [Link]
FROM areceber ar
WHERE ([Link] 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
^
Listagem 7. Stored procedure para um fluxo de caixa

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,
histórico, 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),
 10
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([Link])
FROM apagar ap
WHERE ([Link] <:inicio) INTO:saldo_ant_debito;
SELECT SUM([Link])
FROM areceber ar
WHERE ([Link] <: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 sabemos, 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;
Nota: A listagem 8 mostra uma versão da procedure adaptada para o Firebird 1.5 (ou superior) onde
substituímos a checagem de nulls pelo comando COALESCE.

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; se 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);
saldo_final = valor;
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 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 sequência, devemos inicializar a variável saldo_final com o saldo anterior obtido:
 11

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, histórico e valor nos respectivos
parâmetros de saída que formarão uma linha/registro 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 o 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 sequência pode ser observada no seguinte código:
tipo = 'D';
FOR SELECT [Link], [Link], [Link]
FROM apagar ap
WHERE ([Link] BETWEEN:inicio AND:fim)
INTO:data,:historico,:valor
DO
BEGIN
saldo_final = saldo_final - valor;
SUSPEND;
END
tipo = 'C';
FOR SELECT [Link], [Link], [Link]
FROM areceber ar
WHERE ([Link] 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 o 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 deve ser chamada por um SELECT, como se fosse uma tabela
comum do banco de dados:
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 FB que a data está 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.
 12

Figura 1. Resultado de SELECT sobre a procedure selecionável CAIXA

Exemplo com triggers

Para exemplificar o uso de um trigger, veremos como simular um campo “autoincremento” no FB.
Para 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, definiremos um trigger no evento Before Insert (ou seja, antes
que uma inserção seja efetivada), que irá incrementar o nosso contador e atribuir o valor dele ao
campo "autoincremento". Verifique o código:
CREATE TRIGGER APAGAR_BI_BI FOR APAGAR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF ([Link] IS NULL) THEN
[Link] = GEN_ID(GEN_APAGAR_ID,1);
END
^

No exemplo, ID é o campo autoincremento e só recebe um valor caso já não tenha sido inicializado.
Isso permite uma flexibilização que não ocorre no tipo autoincremento real, encontrado em diversos
SGBDs. Dessa maneira, podemos forçar um registro a ter um determinado ID simplesmente
atribuindo um valor a ele antes de ser efetivamente gravado. 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 Firebird que incrementa o valor de um generator retornando
um novo valor.

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

select coalesce(sum([Link]),0)
from apagar ap
where ([Link] < :inicio)
 13

into :saldo_ant_debito;

select coalesce(sum([Link]),0)
from areceber ar
where ([Link] < :inicio)
into :saldo_ant_credito;

data = :inicio - 1;
historico = 'SALDO ANTERIOR';
valor = (saldo_ant_credito - saldo_ant_debito);

saldo_final = valor;

if (:valor < 0) then

begin
tipo = 'D';
valor = valor * -1;
end
else
begin
tipo = 'C';
end

suspend; -- Retornando à primeira linha (registro) com o saldo anterior

tipo = 'D'; -- Estamos recuperando débitos

for select [Link], [Link], [Link]

from apagar ap
where ([Link] between :inicio and :fim)
into :data, :historico, :valor
do
begin
saldo_final = saldo_final - valor; /*Subtraimos o valor do lançamento
do saldo final */
suspend;
end

tipo = 'C'; /* Estamos recuperando créditos */

for select [Link], [Link], [Link]

from areceber ar
where ([Link] between :inicio and :fim)
into :data, :historico, :valor
do
begin
saldo_final = saldo_final + valor;
suspend;
end

data = :fim;
historico = 'SALDO FINAL';
tipo = 'C';
valor = saldo_final;

suspend;
end
Listagem 8. Código da procedure alterado para Firebird 1.5 utilizando o comando COALESCE e comentário de uma linha
 14

Esse artigo foi extraído do livro “Firebird Essencial”, de minha autoria, e revisado em 17-julho-2021. Autor: Carlos Henrique Cantu