Stored Procedures em Firebird

Introdução

• "Stored Procedures", ou SP, são rotinas que rodam no servidor e podem ser chamadas pela aplicação cliente.
  
• Stored Procedures são pre-compiladas. Portanto, não é necessário mandar o comando pela rede para ser validado e executado. Elas são simplesmente executadas.
• Procedures podem possuir parâmetros e, assim como o SELECT, retornar dados de uma tabela.

Vantagens

• A aplicação cliente conterá apenas parte do código. Assim sua manutenção será simples e rápida.
• Fácil manutenção. Aplicações cliente não precisam ser recompiladas ou redistribuídas caso alguma alteração nas SP's seja feita.
• Ganho de performance pela diminuição do tráfego na rede.

Chamando Stored Procedures

• Stored Procedures podem executar uma ação e não retornar nenhum dado
• Procedures selecionáveis podem retornar tabelas, assim como o SELECT, ou Views. Pode ser usado como uma tabela em um comando SELECT:
• SELECT a, b FROM procedurename (params) ...
• Para habilitar a chamada de procedure o usuário deve antes atribuir direitos a ela:
  
• EXECUTE rights (granted by GRANT/REVOKE)

Declarando Stored Procedures

CREATE PROCEDURE name [(param1 datatype1, param2 datatype2, ...)] 
[RETURNS (param3 datatype3, param4 datatype4, ...)] 
AS BEGIN
 <corpo>
END; 

Sintaxe para declaração de uma variável

DECLARE VARIABLE variable datatype 

(Os parâmetros de Entrada e Saída de uma SP são usados como variáveis)

SET TERM

Todo comando dentro da Stored Procedure deve terminar com um ; (Ponto e vírgula), a Procedure também. Para distinguir o ponto-e-vígula dentro da Procedure do ponto-e-vírgula de terminação da Procedure pode ser usado outro terminador. Isso pode ser feito usando o comando SET TERM:

SET TERM !! ; 
CREATE PROCEDURE x AS BEGIN ... END !! 
SET TERM ; !! 

O primeiro "SET TERM" substitui o terminador padrão (;) pelo terminador (!!). A procedure contém declarações utilizando o terminador padrão (;). A procedure termina a sua própria declaração usando o "novo" terminador (!!). Depois disso, o símbolo de terminação volta a ser o padrão (;).

Criando, alterando e destruindo Stored Procedures

• Criar: CREATE PROCEDURE nome ...
• Alterar: ALTER PROCEDURE nome ... (o restante é igual ao comando CREATE PROCEDURE)
• Criar ou Alterar, dependendo da existência da SP: CREATE OR ALTER ...
• Apagar: DROP PROCEDURE nome. Só pode ser executada pelo done (owner) da SP.
  Você só pode apagar SP, quando elas não são usadas pode outras SP's, Trigger's ou View's.

Exceções

• Criar: CREATE EXCEPTION nome “mensagem“
• Alterar: ALTER EXCEPTION nome “mensagem“
• Apagar: DROP EXCEPTION nome

Exemplos

/* --- Retornando um valor simples –----------------------------------- */ 
CREATE PROCEDURE Mul (a INTEGER, b INTEGER) 
 RETURNS (Result INTEGER) 
AS BEGIN 
 Result = a * b; 
END 

/* --- Retornando uma tabela –--------------------------------- */ 
CREATE PROCEDURE CountTo10 
 RETURNS (Cnt INTEGER) 
AS BEGIN 
 Cnt = 1; 
 WHILE (Cnt <= 10) DO BEGIN 
 SUSPEND; /* Return next line */ 
 Cnt = Cnt + 1; 
 END;
END 

Comandos

Comando	Descrição	Versão
BEGIN <statements> END	Compound Statement like in PASCAL
variable = expression	Assignment. "variable" can be a local variable, an "in" or an "out" parameter.
compound_statement	A single command or a BEGIN/END block
select_statement	Normal SELECT statement. The INTO clause must be present at the end of the statement. Variable names can be used with a colon preceding them. Example SELECT PRICE FROM ARTICLES WHERE ARTNO = :ArticleNo INTO :EPrice
/* Comment */	Comment, like in C
-- Comment	Single line SQL comment	1.5.0
DECLARE VARIABLE name datatype [= startval]	Variable declaration. After AS, before the first BEGIN.	1.5.0 (startval)
EXCEPTION	Re-fire the current exception. Only makes sense in WHEN clause	1.5.0
EXCEPTION name [message]	Fire the specified exception. Can be handled with WHEN.	1.5.0 (message)
EXECUTE PROCEDURE name arg, arg RETURNING_VALUES arg, arg	Calling a procedure. arg's must be local variables. Nesting and recursion allowed.
EXIT	Leaves the procedure (like in PASCAL)
FOR select_statement DO compound_statement	Executes "compound_statement" for every line that is returned by the SELECT statement
IF (condition) THEN compound_statement [ELSE compound_statement]	IF statement, like in PASCAL
POST_EVENT name	Posts the specified event
SUSPEND	Only for SELECT procedures which return tables: Waits for the client to request the next line. Returns the next line to the client.
WHILE (condition) DO compound_statement	WHILE statement. Like in PASCAL.
WHEN {EXCEPTION a | SQLCODE x | ANY} DO compound_statement	Exception handling. WHEN statements must be at the end of the procedure, directly before the final END.
EXECUTE STATEMENT stringvalue	Executes the DML statement in stringvalue	1.5.0
EXECUTE STATEMENT stringvalue INTO variable_list	Executes Statement and returns variables (singleton)	1.5.0
FOR EXECUTE STATEMENT stringvalue INTO variable_list DO compound_statement	Executes Statement and iterates through the resulting lines	1.5.0