Stored Procedures in Firebird

General

"Stored Procedures" are routines which run on the server and can be called by client applications
Stored Procedures are pre-compiled. So they don't need to be sent over the network and parsed every time. They're just executed.
Procedures can take parameters and – like SELECT – give back their data in the form of a table.

Advantages

Parts of code only implemented once: client applications get smaller and less complicated to implement and mantain.
Easier Maintenance: Client applications don't need to be recompiled and redistributed
Increased performance because of reduced network traffic

Calling Stored Procedures

Stored Procedures can perform an action and do not return any data
"Select" Procedures return tables, just like SELECT, Tables or Views. They can be used like a table reference in a SELECT statement:
SELECT a, b FROM procedurename (params) ...
To be able to call a procedure, the user must have EXECUTE rights (granted by GRANT/REVOKE)

Declaring Stored Procedures

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

  <body>

END;

Syntax of a variable declaration

DECLARE VARIABLE variable datatype

(In and Out parameters of a Stored Procedure are used like variables)

SET TERM

Every command in a script must be terminated by a semi-colon, the procedure itself, too. To distinguish the semi-colons in the procedure from the terminating semi-colon, there must be another terminator for the end of the procedure. This is done with SET TERM:

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

The first "SET TERM" replaces the terminator semi-colon with the terminator double-exclamation. The procedure declaration contains the usual semi-colons after each command. The procedure itself is terminated by the "new" terminator !!. After that, the terminator symbol is set back to a semi-colon.

Creating, altering and dropping Stored Procedures

Create: CREATE PROCEDURE name ...
Alter: ALTER PROCEDURE name ... (the rest is like CREATE PROCEDURE)
Create or Alter, depending on existance of proceudure: CREATE OR ALTER ...
Drop: DROP PROCEDURE name. Can only be done by the owner of the procedure.
You can only drop procedures, which are not used by other procedures, triggers or views.

Exceptions

Create: CREATE EXCEPTION name “message“
Alter: ALTER EXCEPTION name “message“
Drop: DROP EXCEPTION name

Examples

/* --- Returning a single value –----------------------------------- */ 
CREATE PROCEDURE Mul (a INTEGER, b INTEGER) 
  RETURNS (Result INTEGER) 
AS BEGIN 
  Result = a * b; 
END

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

Commands

Command	Description	Version
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

Stefan Heymann, 2004-08-22

This documentation is licensed under (choose your favorite): GPL, LGPL, CC, IDPL, GFDL, BSD, (did I forget one?)