Stored Procedures e Triggers no Firebird

Stored Procedures e Triggers no Firebird

(Parte 1 de 4)

Introdução a Stored Procedures e Triggers no Firebird Por Bill Todd, Borland Developers Conference San Diego 2000

Traduzido e adaptado com autorização do autor por: Alessandro Cunha Fernandes, Comunidade Firebird, Julho de 2002

Uma stored procedure é um programa escrito numa linguagem própria para procedures e triggers do Firebird que é armazenado como parte do banco de dados. Stored procedures podem ser chamadas por aplicações cliente ou por outras stored procedures ou triggers. Triggers são quase a mesma coisa que stored procedures exceto pelo modo como são chamadas. Triggers são chamadas automaticamente quando uma alteração em uma linha da tabela ocorre. Este artigo examina primeiro as stored procedures e logo depois as triggers. Como você poderá ver a maioria das coisas que serão ditas sobre stored procedures se aplicarão também às triggers.

Vantagens do uso de Stored Procedures

A maior vantagem do uso de stored procedures é a redução de tráfico na rede. Já que as stored procedures são executadas pelo Firebird na máquina servidora de banco de dados, você pode utiliza-las para mover grande parte do seu código de manipulação de dados para o servidor. Isto elimina a transferência de dados do servidor para o cliente, pela rede, para a manipulação e reduzir tráfico é aumentar performance, particularmente em uma WAN ou em qualquer conexão de baixa velocidade.

Stored procedures aumentam a performance de outra forma também. Você pode utilizar querys para fazer muitas das coisas que podem ser feitas com stored procedures mas uma query tem uma grande desvantagem. Cada vez que a aplicação cliente envia um comando SQL para o servidor o comando tem que ser “parsed”, ou seja, analisado gramaticalmente, submetido ao optimizador para formulação de um plano de execução. Stored procedures são analisadas , optimizadas e armazenadas em uma forma executável no momento em que são adicionadas ao banco de dados. A partir do momento que uma stored procedure não tem que ser analisada e optimizada cada vez que é chamada, ela é executada mais rapidamente que uma query equivalente. Stored procedures podem também executar operações muito mais complexas que uma simples query.

Se mais de uma aplicação irá acessar o banco de dados, as stored procedures podem também economizar tempo de manutenção e desenvolvimento já que qualquer aplicação poderá chama-la. A manutenção é mais fácil porque você pode alterar a stored procedure sem ter que alterar ou mesmo recompilar cada aplicação cliente.

Finalmente, stored procedures tem uma grande importância na segurança do banco de dados uma vez que elas podem acessar tabelas que o usuário não tem o direito de faze-lo. Por exemplo, suponha que um usuário precise rodar um relatório que mostra o total de salários por departamento e nível salarial. Embora estas informações venham da tabela de salários dos empregados você não quer que este usuário tenha acesso aos salários individuais de todos os empregados. A solução é escrever uma stored procedure que extraia da tabela de salários as informações resumidas que o relatório precisa e dar direitos de leitura à stored procedure para a tabela de salários. Você pode então dar direito ao usuário de executar a stored procedure. O usuário não precisa ter direitos sobre a tabela de salários.

Quando você deve usar Stored Procedures?

A resposta curta é, sempre que você puder. Não existem desvantagens em se usar stored procedures. Existem apenas duas limitações. Primeiro, você tem que ser capaz de passar qualquer informação variável para a stored procedure como parâmetros ou coloca-las em uma tabela que a stored procedure possa acessar. Segundo, a linguagem de escrita de stored procedures e triggers pode ser muito limitada para operações mais complexas.

Usando o comando CREATE PROCEDURE

Stored procedures são criadas através do comando CREATE PROCEDURE que tem a seguinte sintaxe:

CREATE PROCEDURE NomedaProcedure <parâmetros de entrada> RETURNS <parâmetros de saída> AS <declaração de variáveis locais> BEGIN <comandos da procedures> END

Os parâmetros de entrada permitem à aplicação cliente passar os valores que serão usados para modificar o comportamento da stored procedure. Por exemplo, se o objetivo da stored procedure é calcular o total mensal da folha de pagamento para a um determinado departamento, o número do departamento deverá ser passado para a stored procedure como um parâmetro de entrada. Parâmetros de saída ou de retorno são é o meio pelo qual a stored procedure retorna informações para a aplicação cliente. Em nosso exemplo, o total da folha de pagamento mensal para o departamento passado deverá ser retornado em um parâmetro de saída. Um parâmetro pode ser de qualquer tipo de dados do Firebird exceto BLOB ou ARRAY. A procedure a seguir demonstra o uso tanto dos parâmetros de entrada como os de saída:

CREATE PROCEDURE SUB_TOT_BUDGET( HEAD_DEPT CHAR(3) ) RETURNS ( TOT_BUDGET NUMERIC (15, 2), AVG_BUDGET NUMERIC (15, 2), MIN_BUDGET NUMERIC (15, 2), MAX_BUDGET NUMERIC (15, 2) ) AS BEGIN SELECT SUM(BUDGET), AVG(budget), MIN(budget), MAX(budget) FROM department WHERE head_dept = :head_dept INTO :tot_budget, :avg_budget, :min_budget, :max_budget; SUSPEND; END ^

Esta stored procedure declara um parâmetro de entrada, HEAD_DEPT cujo tipo é CHAR(3) e quatro parâmetros de saída, TOT_BUDGET, AVG_BUDGET, MIN_BUDGET, e MAX_BUDGET todos do tipo NUMERIC(15, 2). Tanto os parâmetros de entrada quando os de saída devem estar entre parênteses . O comando SUSPEND pausa a stored procedure até que o cliente busque os valores dos parâmetros de saída. Este comando é explicado em mais detalhes mais tarde neste mesmo artigo.

Declarando variáveis locais

Você pode declarar variáveis locais de qualquer tipo suportado pelo Firebird dentro de uma stored procedure. Estas variáveis só existem enquanto a stored procedure está sendo executada e seu escopo é local à procedure. Note que não existem variáveis globais quando se trabalha com stored procedures e triggers e elas não são necessárias. Se você tem valores que precisam ser compartilhados por duas ou mais procedures, você pode passa-los por parâmetros ou guarda-los em uma tabela.

Variáveis locais são declaradas depois da palavra chave AS e antes da palavra chave BEGIN que identifica o início do corpo da stored procedure. Para declarar variáveis use DECLARE VARIABLE <Nome variável> <Tipo variável>

DECLARE VARIABLE OrderCount Integer; DECLARE VARIABLE TotalAmount NUMERIC(15,2);

Note que cada comando DECLARE VARIABLE só pode declarar uma variável. A procedure a seguir ilustra o uso do comando DECLARE VARIABLE. Ela declara quatro variáveis locais , ord_stat, hold_stat, cust_no and any_po. Observe que quando uma variável é usada na clausula INTO de um comando SELECT um sinal de dois pontos ':' deve ser adicionado como primeiro caracter do nome da variável, entretanto quando a variável é usada em qualquer outra parte este sinal não é mais necessário.

DECLARE VARIABLEord_stat CHAR(7);
DECLARE VARIABLEhold_stat CHAR(1);
DECLARE VARIABLEcust_no INTEGER;
DECLARE VARIABLEany_po CHAR(8);
FROM saless, customer c

CREATE PROCEDURE SHIP_ORDER( PO_NUM CHAR(8) ) AS BEGIN SELECT s.order_status, c.on_hold, c.cust_no WHERE po_number = :po_num AND s.cust_no = c.cust_no INTO :ord_stat, :hold_stat, :cust_no;

IF (ord_stat= 'shipped') THEN
EXCEPTIONorder_already_shipped;

/* Este pedido já foi enviado */ BEGIN SUSPEND; END

ELSE IF (hold_stat= '*') THEN
EXCEPTIONcustomer_on_hold;

/* Cliente está em atraso. */ BEGIN SUSPEND;

/* * Se existe uma conta não paga de pedidos enviados a mais de 2 meses,

* passe o cliente para cliente em atraso.

FOR SELECTpo_number
AND order_status= 'shipped'
AND paid ='n'
EXCEPTIONcustomer_check;

*/ FROM sales WHERE cust_no = :cust_no AND ship_date < CAST('NOW' AS DATE) - 60 INTO :any_po DO BEGIN

SET on_hold= '*'

UPDATE customer WHERE cust_no = :cust_no; SUSPEND; END

/* * Envia o pedido.

SET order_status= 'shipped', ship_date = 'NOW'

*/ UPDATE sales WHERE po_number = :po_num; SUSPEND; END ^

Escrevendo o corpo da procedure

O corpo da stored procedure consiste em um conjunto de qualquer número de comandos da linguagem de escrita de stored procedure e triggers do Firebird dentro de um bloco BEGIN/END. O corpo da seguinte procedure consiste em um comando SELECT e um SUSPEND entre as palavras chave BEGIN e AND.

CREATE PROCEDURE SUB_TOT_BUDGET( HEAD_DEPT CHAR(3) ) RETURNS ( TOT_BUDGET NUMERIC (15,2), AVG_BUDGET NUMERIC (15,2), MIN_BUDGET NUMERIC (15,2), MAX_BUDGET NUMERIC (15,2) ) AS BEGIN SELECT SUM(budget), AVG(budget),MIN(budget), MAX(budget) FROM department WHERE head_dept=:head_dept INTO :tot_budget, :avg_budget,:min_budget, :max_budget; SUSPEND; END ^

Cada comando no corpo de uma procedure tem que terminar com um ponto e virgula ';'.

Outros elementos de linguagem

A linguagem de escrita de stored procedure e triggers do Firebird inclui todas as construções de uma linguagem de programação estruturada assim como declarações próprias para trabalhar com dados em tabelas. A seguinte seção descreverá estes elementos.

Comentários

Você pode colocar comentários onde quiser em uma stored procedure usando a sintaxe /* Este é um comentário */. Um comentário pode ter várias linhas, mas comentários aninhados não são permitidos.

Bloco de comandos (BEGIN-END)

A linguagem de stored procedures e triggers se assemelha ao Pascal em algumas construções como IF-THEN-ELSE e loops WHILE que somente podem conter um comando. Entretanto, as palavras chave BEGIN e END podem ser usadas para agrupar uma série de comandos de forma que eles se tornem um comando composto. Nunca coloque um ponto-evírgula após um BEGIN ou um END.

Comandos de atribuição

A linguagem de procedures e triggers suporta comandos de atribuição da seguinte forma: Var1 = Var2 * Var3;

Var1 tanto pode ser uma variável local quanto um parâmetro de saída. Var2 e Var3 tanto podem ser variáveis locais como parâmetros de entrada. A expressão à direita do sinal de igual pode ser tão complexa quanto você deseje e você pode usar parênteses para agrupar operações com quantos níveis quiser.

A sintaxe do comando IF no Firebird é a seguinte:

IF <expressão condicional> THEN <comando> ELSE <comando>

Onde <comando> pode ser tanto um comando simples quanto um bloco de comandos delimitado por um BEGIN-END. Na <expressão condicional> além dos operadores lógicos normais (=, <, >, <=, >=, <>) você pode usar também os seguintes operadores SQL:

Expressão Condicional Descrição

Valor BETWEEN valor AND valor Faixa de valores

Valor LIKE valor O valor à direita pode incluir um ou mais curingas. Use

% para zero ou mais caracteres e _ para um caracter.

(Parte 1 de 4)

Comentários