A stored procedure is a software module that can be called from a client, another procedure, function, executable block or trigger.Stored procedures are written in procedural SQL (PSQL) or defined using a UDR (User-Defined Routine).Most SQL statements are available in PSQL as well, sometimes with limitations or extensions.Notable limitations are the prohibition on DDL and transaction control statements in PSQL.

Stored procedures can have many input and output parameters.

`CREATE PROCEDURE`

Creates a stored procedure

Available in

DSQL, ESQL

Syntax

CREATE PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

<in_params> ::= <inparam> [, <inparam> ...]

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<out_params> ::= <outparam> [, <outparam> ...]

<outparam> ::= <param_decl>

<value> ::= {<literal> | NULL | <context_var>}

<param_decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
  [COLLATE collation]

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql_procedure> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! See Syntax of Module Body !!

<external-module-body> ::=
  !! See Syntax of Module Body !!

Table 1. `CREATE PROCEDURE` Statement Parameters
Parameter	Description
procname	Stored procedure name.The maximum length is 63 characters.Must be unique among all table, view and procedure names in the database
inparam	Input parameter description
outparam	Output parameter description
literal	A literal value that is assignment-compatible with the data type of the parameter
context_var	Any context variable whose type is compatible with the data type of the parameter
paramname	The name of an input or output parameter of the procedure.The maximum length is 63 characters.The name of the parameter must be unique among input and output parameters of the procedure and its local variables
collation	Collation

The CREATE PROCEDURE statement creates a new stored procedure.The name of the procedure must be unique among the names of all stored procedures, tables, and views in the database.

CREATE PROCEDURE is a compound statement, consisting of a header and a body.The header specifies the name of the procedure and declares input parameters and the output parameters, if any, that are to be returned by the procedure.

The procedure body consists of declarations for any local variables, named cursors, and subroutines that will be used by the procedure, followed by one or more statements, or blocks of statements, all enclosed in an outer block that begins with the keyword BEGIN and ends with the keyword END.Declarations and embedded statements are terminated with semicolons (‘;’).

docnext count = 17

Statement Terminators

Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon.This creates a conflict with PSQL syntax when coding in these environments.If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

Parameters

Each parameter has a data type.The NOT NULL constraint can also be specified for any parameter, to prevent NULL being passed or assigned to it.

A collation can be specified for string-type parameters, using the COLLATE clause.

Input Parameters: Input parameters are presented as a parenthesized list following the name of the function.They are passed by value into the procedure, so any changes inside the procedure has no effect on the parameters in the caller.Input parameters may have default values.Parameters with default values specified must be added at the end of the list of parameters.
Output Parameters: The optional RETURNS clause is for specifying a parenthesised list of output parameters for the stored procedure.

SQL Security

The SQL SECURITY clause specifies the security context for executing other routines or inserting into other tables.When SQL Security is not specified, the default value of the database is applied at runtime.

The SQL SECURITY clause can only be specified for PSQL procedures, and is not valid for procedures defined in a package.

See also SQL Security in chapter Security.

Variable, Cursor and Subroutine Declarations

The optional declarations section, located at the start of the body of the procedure definition, defines variables (including cursors) and subroutines local to the procedure.Local variable declarations follow the same rules as parameters regarding specification of the data type.See details in the PSQL chapter for DECLARE VARIABLE, DECLARE CURSOR, DECLARE FUNCTION, and DECLARE PROCEDURE.

External UDR Procedures

A stored procedure can also be located in an external module.In this case, instead of a procedure body, the CREATE PROCEDURE specifies the location of the procedure in the external module using the EXTERNAL clause.The optional NAME clause specifies the name of the external module, the name of the procedure inside the module, and — optionally — user-defined information.The required ENGINE clause specifies the name of the UDR engine that handles communication between Firebird and the external module.The optional AS clause accepts a string literal “body”, which can be used by the engine or module for various purposes.

Who Can Create a Procedure

The CREATE PROCEDURE statement can be executed by:

Administrators
Users with the CREATE PROCEDURE privilege

The user executing the CREATE PROCEDURE statement becomes the owner of the table.

Examples

Creating a stored procedure that inserts a record into the BREED table and returns the code of the inserted record:

CREATE PROCEDURE ADD_BREED (
  NAME D_BREEDNAME, /* Domain attributes are inherited */
  NAME_EN TYPE OF D_BREEDNAME, /* Only the domain type is inherited */
  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME,
    /* The table column type is inherited */
  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
  CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
  CODE_BREED INT
)
AS
BEGIN
  INSERT INTO BREED (
    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
  VALUES (
    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
  RETURNING CODE_BREED INTO CODE_BREED;
END

Creating a selectable stored procedure that generates data for mailing labels (from employee.fdb):

CREATE PROCEDURE mail_label (cust_no INTEGER)
RETURNS (line1 CHAR(40), line2 CHAR(40), line3 CHAR(40),
         line4 CHAR(40), line5 CHAR(40), line6 CHAR(40))
AS
  DECLARE VARIABLE customer VARCHAR(25);
  DECLARE VARIABLE first_name VARCHAR(15);
  DECLARE VARIABLE last_name VARCHAR(20);
  DECLARE VARIABLE addr1 VARCHAR(30);
  DECLARE VARIABLE addr2 VARCHAR(30);
  DECLARE VARIABLE city VARCHAR(25);
  DECLARE VARIABLE state VARCHAR(15);
  DECLARE VARIABLE country VARCHAR(15);
  DECLARE VARIABLE postcode VARCHAR(12);
  DECLARE VARIABLE cnt INTEGER;
BEGIN
  line1 = '';
  line2 = '';
  line3 = '';
  line4 = '';
  line5 = '';
  line6 = '';

  SELECT customer, contact_first, contact_last, address_line1,
    address_line2, city, state_province, country, postal_code
  FROM CUSTOMER
  WHERE cust_no = :cust_no
  INTO :customer, :first_name, :last_name, :addr1, :addr2,
    :city, :state, :country, :postcode;

  IF (customer IS NOT NULL) THEN
    line1 = customer;
  IF (first_name IS NOT NULL) THEN
    line2 = first_name || ' ' || last_name;
  ELSE
    line2 = last_name;
  IF (addr1 IS NOT NULL) THEN
    line3 = addr1;
  IF (addr2 IS NOT NULL) THEN
    line4 = addr2;

  IF (country = 'USA') THEN
  BEGIN
    IF (city IS NOT NULL) THEN
  	  line5 = city || ', ' || state || '  ' || postcode;
  	ELSE
      line5 = state || '  ' || postcode;
  END
  ELSE
  BEGIN
    IF (city IS NOT NULL) THEN
  	  line5 = city || ', ' || state;
  	ELSE
      line5 = state;
    line6 = country || '    ' || postcode;
  END

  SUSPEND; -- the statement that sends an output row to the buffer
           -- and makes the procedure "selectable"
END

With DEFINER set for procedure p, user US needs only the EXECUTE privilege on p.If it were set for INVOKER, either the user or the procedure would also need the INSERT privilege on table t.

set term ^;
create procedure p (i integer) SQL SECURITY DEFINER
as
begin
  insert into t values (:i);
end^
set term ;^

grant execute on procedure p to user us;
commit;

connect 'localhost:/tmp/17.fdb' user us password 'pas';
execute procedure p(1);

`ALTER PROCEDURE`

Alters a stored procedure

Available in

DSQL, ESQL

Syntax

ALTER PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

!! See syntax of CREATE PROCEDURE for further rules !!

The ALTER PROCEDURE statement allows the following changes to a stored procedure definition:

the set and characteristics of input and output parameters
local variables
code in the body of the stored procedure

After ALTER PROCEDURE executes, existing privileges remain intact and dependencies are not affected.

Altering a procedure without specifying the SQL SECURITY clause will remove the SQL Security property if currently set for this procedure.This means the behaviour will revert to the database default.

Caution

Take care about changing the number and type of input and output parameters in stored procedures.Existing application code and procedures and triggers that call it could become invalid because the new description of the parameters is incompatible with the old calling format.For information on how to troubleshoot such a situation, see the article The RDB$VALID_BLR Field in the Appendix.

Who Can Alter a Procedure

The ALTER PROCEDURE statement can be executed by:

Administrators
The owner of the stored procedure
Users with the ALTER ANY PROCEDURE privilege

`ALTER PROCEDURE` Example

Altering the GET_EMP_PROJ stored procedure.

ALTER PROCEDURE GET_EMP_PROJ (
  EMP_NO SMALLINT)
RETURNS (
  PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
      PROJ_ID
    FROM
      EMPLOYEE_PROJECT
    WHERE
      EMP_NO = :emp_no
    INTO :proj_id
  DO
    SUSPEND;
END

`CREATE OR ALTER PROCEDURE`

Creates a stored procedure if it does not exist, or alters a stored procedure

Available in

DSQL

Syntax

CREATE OR ALTER PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

!! See syntax of CREATE PROCEDURE for further rules !!

The CREATE OR ALTER PROCEDURE statement creates a new stored procedure or alters an existing one.If the stored procedure does not exist, it will be created by invoking a CREATE PROCEDURE statement transparently.If the procedure already exists, it will be altered and compiled without affecting its existing privileges and dependencies.

`CREATE OR ALTER PROCEDURE` Example

Creating or altering the GET_EMP_PROJ procedure.

CREATE OR ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
      PROJ_ID
    FROM
      EMPLOYEE_PROJECT
    WHERE
      EMP_NO = :emp_no
    INTO :proj_id
  DO
    SUSPEND;
END

`DROP PROCEDURE`

Drops a stored procedure

Available in

DSQL, ESQL

Syntax

DROP PROCEDURE procname

Table 1. `DROP PROCEDURE` Statement Parameter
Parameter	Description
procname	Name of an existing stored procedure

The DROP PROCEDURE statement deletes an existing stored procedure.If the stored procedure has any dependencies, the attempt to delete it will fail and raise an error.

Who Can Drop a Procedure

The DROP PROCEDURE statement can be executed by:

Administrators
The owner of the stored procedure
Users with the DROP ANY PROCEDURE privilege

`DROP PROCEDURE` Example

Deleting the GET_EMP_PROJ stored procedure.

DROP PROCEDURE GET_EMP_PROJ;

`RECREATE PROCEDURE`

Drops a stored procedure if it exists, and creates a stored procedure

Available in

DSQL

Syntax

RECREATE PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

!! See syntax of CREATE PROCEDURE for further rules !!

The RECREATE PROCEDURE statement creates a new stored procedure or recreates an existing one.If a procedure with this name already exists, the engine will try to drop it and create a new one.Recreating an existing procedure will fail at the COMMIT request if the procedure has dependencies.

Warning

Be aware that dependency errors are not detected until the COMMIT phase of this operation.

After a procedure is successfully recreated, privileges to execute the stored procedure, and the privileges of the stored procedure itself are dropped.

`RECREATE PROCEDURE` Example

Creating the new GET_EMP_PROJ stored procedure or recreating the existing GET_EMP_PROJ stored procedure.

RECREATE PROCEDURE GET_EMP_PROJ (
  EMP_NO SMALLINT)
RETURNS (
  PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
      PROJ_ID
    FROM
      EMPLOYEE_PROJECT
    WHERE
      EMP_NO = :emp_no
    INTO :proj_id
  DO
    SUSPEND;
END

PROCEDURE

PROCEDURE

CREATE PROCEDURE

docnext count = 17

Statement Terminators

Parameters

SQL Security

Variable, Cursor and Subroutine Declarations

External UDR Procedures

Who Can Create a Procedure

Examples

ALTER PROCEDURE

Who Can Alter a Procedure

ALTER PROCEDURE Example

CREATE OR ALTER PROCEDURE

CREATE OR ALTER PROCEDURE Example

DROP PROCEDURE

Who Can Drop a Procedure

DROP PROCEDURE Example

RECREATE PROCEDURE

RECREATE PROCEDURE Example

`PROCEDURE`

`CREATE PROCEDURE`

`ALTER PROCEDURE`

`ALTER PROCEDURE` Example

`CREATE OR ALTER PROCEDURE`

`CREATE OR ALTER PROCEDURE` Example

`DROP PROCEDURE`

`DROP PROCEDURE` Example

`RECREATE PROCEDURE`

`RECREATE PROCEDURE` Example