Creating Stored Procedures - Firebird

In your script or in isql, begin by setting the terminator symbol that will be used to mark the end of the CREATE PROCEDURE syntax. The following example will set the terminator symbol to &:

... SET TERM &;

The syntax pattern is

CREATE PROCEDURE procedure-name
[(argument data-type [, argument data-type [...]])]
[RETURNS (argument data-type [, argument data-type [...]])
AS
<procedure-body>
<procedure-body> =
[DECLARE [VARIABLE] var data-type; [...]]] BEGIN
<compound-statement>;
END <terminator>

Header Elements

In the header, declare

  • The name of the procedure, which is required and must be unique in the data-base, for example:

    CREATE PROCEDURE MyProc

  • Any optional input arguments (parameters) required by the procedure, and their data types, as a comma-separated list enclosed by brackets, for example:
    CREATE PROCEDURE MyProc
    (invar1 integer, invar2 date)
  • The name of each argument must be unique within the procedure. The data type can be any standard SQL data type, except arrays of data types. The name of an input argument need not match the name of any parameter in the calling program.
  • Any optional output arguments (parameters) required, and their data types, as a comma-separated list enclosed in brackets following the keyword RETURNS, for example:
    CREATE PROCEDURE MyProc (invar1 INTEGER, invar2 DATE) RETURNS (outvar1 INTEGER, outvar2 VARCHAR(20), outvar3 DOUBLE PRECISION)
  • The name of each argument must be unique within the procedure. The data type can be any standard SQL data type except arrays of data types.
  • The keyword AS, which is required:
    CREATE PROCEDURE MyProc (invar1INTEGER, invar2 DATE) RETURNS(outvar1 INTEGER, outvar2 VARCHAR(20), outvar3 DOUBLE PRECISION) AS

Body Elements

The syntax outline is

<procedure_body>=[<variable-declaration-list>] <compound-statement>

Local Variables

If you have local variables to declare, their declarations come next. Each declaration is terminated with a semicolon. In v.1.5, local variables can be optionally initialized in the declaration. The syntax pattern is

<variable_declaration_list>= DECLARE [VARIABLE] var datatype [{'=' | DEFAULT} value]; [DECLARE [VARIABLE] var datatype; ...]

For example:

CREATE PROCEDURE MyProc ( invar 1 INTEGER, invar2 DATE) RETURNS (
outvar1 INTEGER,
outvar2 VARCHAR(20),
outvar3 DOUBLE PRECISION)
AS
DECLARE VARIABLE localvar integer DEFAULT 0;
DECLARE VARIABLE anothervar DOUBLE PRECISION = 0.00;

Main Code Block

Next comes the main code block, designated in the higher-level syntax diagram as <compound-statement>. It starts with the keyword BEGIN and ends with the keyword END.

The syntax outline is

<compound-statement> =
BEGIN
<compound_-statement>
[<compound-statement>...]
END <terminator>

All <compound-statement> structures consist of single statements and/or other <compound-statement> structures that can nest others, for example:

CREATE PROCEDURE MyProc (
invar 1 INTEGER,
invar2 DATE)
RETURNS (
outvar1 INTEGER,
outvar2 VARCHAR(20),
outvar3 DOUBLE PRECISION)
AS
DECLARE VARIABLE localvar integer DEFAULT 0;
DECLARE VARIABLE anothervar DOUBLE PRECISION = 0.00;
BEGIN
<compound-statement>
END &

The <compound-statement> element can be any or all of a single statement, a block of statements, and embedded blocks of statements bounded by BEGIN .. . END pairs. Blocks can include

  • Assignment statements, to set values of local variables and input/output parameters.
  • SELECT statements, to retrieve column values into variables. SELECT statements must have an INTO clause as the last clause and corresponding local variable or output argument declarations for each column selected.
  • Looping structures, such as FOR SELECT ... DO and WHILE ... DO, to perform conditional or looping tasks.
  • Branching structures using IF ... THEN ... [ELSE].
  • EXECUTE PROCEDURE statements, to invoke other procedures, with optional RETURNING_VALUES clauses to return values into variables. Recursion is allowed.
  • SUSPEND and EXIT statements that return control and, optionally, return values to the calling application or PSQL module.
  • Comments to annotate procedure code.
  • EXCEPTION statements, to return custom error messages to applications or to signal conditions for exception handlers.
  • WHEN statements to handle specific or general error conditions.
  • POST_EVENT statements to add an event notification to the stack.

    For example:

...
BEGIN
FOR SELECT COL1, COL2, COL3, COL4
FROM TABLEA INTO :COL1, :COL2, :COL3 DO
BEGIN
<statements>
END
<statements>
END &
SET TERM ; &
COMMIT;

Notice the termination of the entire procedure declaration with the terminator character previously defined by the SET TERM statement. After the procedure body is complete, the terminator character is set back to the default semicolon. It need not always be the case. In a DDL script, where you may be declaring several PSQL modules, you can keep the alternative terminator current. Some people make it a practice to use an alternative terminator throughout all of their scripts, thus reserving the semicolon only for PSQL statement terminators. It is a matter of personal preference.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics