7.2. Stored Procedures

A stored procedure is a program stored in the database metadata for execution on the server. A stored procedure can be called by stored procedures (including itself), triggers and client applications. A procedure that calls itself is known as recursive.

7.2.1. Benefits of Stored Procedures

Stored procedures have the following advantages:

Modularity

applications working with the database can use the same stored procedure, thereby reducing the size of the application code and avoiding code duplication.

Simpler Application Support

when a stored procedure is modified, changes appear immediately to all host applications, without the need to recompile them if the parameters were unchanged.

Enhanced Performance

since stored procedures are executed on a server instead of at the client, network traffic is reduced, which improves performance.

7.2.2. Types of Stored Procedures

Firebird supports two types of stored procedures: executable and selectable.

7.2.2.1. Executable Procedures

Executable procedures usually modify data in a database. They can receive input parameters and return a single set of output (RETURNS) parameters. They are called using the EXECUTE PROCEDURE statement. See an example of an executable stored procedure at the end of the CREATE PROCEDURE section of Chapter 5.

7.2.2.2. Selectable Procedures

Selectable stored procedures usually retrieve data from a database, returning an arbitrary number of rows to the caller. The caller receives the output one row at a time from a row buffer that the database engine prepares for it.

Selectable procedures can be useful for obtaining complex sets of data that are often impossible or too difficult or too slow to retrieve using regular DSQL SELECT queries. Typically, this style of procedure iterates through a looping process of extracting data, perhaps transforming it before filling the output variables (parameters) with fresh data at each iteration of the loop. A SUSPEND statement at the end of the iteration fills the buffer and waits for the caller to fetch the row. Execution of the next iteration of the loop begins when the buffer has been cleared.

Selectable procedures may have input parameters and the output set is specified by the RETURNS clause in the header.

A selectable stored procedure is called with a SELECT statement. See an example of a selectable stored procedure at the end of the CREATE PROCEDURE section of Chapter 5.

7.2.3. Creating a Stored Procedure

The syntax for creating executable stored procedures and selectable stored procedures is exactly the same. The difference comes in the logic of the program code.

Syntax (partial) 

  |CREATE PROCEDURE procname
  |  [(<inparam> [, <inparam> ...])]
  |  [RETURNS (<outparam> [, <outparam> ...])]
  |AS
  |  [<declarations>]
  |BEGIN
  |  [<PSQL_statements>]
  |END

The header of a stored procedure must contain the procedure name, and it must be unique among the names of stored procedures, tables, and views. It may also define some input and output parameters. Input parameters are listed after the procedure name inside a pair of brackets. Output parameters, which are mandatory for selectable procedures, are bracketed inside one RETURNS clause.

The final item in the header (or the first item in the body, depending on your opinion of where the border lies) is one or more declarations of any local variables and/or named cursors that your procedure might require.

Following the declarations is the main BEGIN…​END block that delineates the procedure’s PSQL code. Within that block could be PSQL and DML statements, flow-of-control blocks, sequences of other BEGIN…​END blocks, including embedded blocks. Blocks, including the main block, may be empty and the procedure will still compile. It is not unusual to develop a procedure in stages, from an outline.

For more information about creating stored proceduresSee CREATE PROCEDURE in Chapter 5, Data Definition (DDL) Statements.

7.2.4. Modifying a Stored Procedure

An existing stored procedure can be altered, to change the sets of input and output parameters and anything in the procedure body.

Syntax (partial) 

  |ALTER PROCEDURE procname
  |  [(<inparam> [, <inparam> ...])]
  |  [RETURNS (<outparam> [, <outparam> ...])]
  |AS
  |  [<declarations>]
  |BEGIN
  |  [<PSQL_statements>]
  |END

For more information about modifying stored proceduresSee ALTER PROCEDURE, CREATE OR ALTER PROCEDURE, RECREATE PROCEDURE, in Chapter 5, Data Definition (DDL) Statements.

7.2.5. Deleting a Stored Procedure

The DROP PROCEDURE statement is used to delete stored procedures.

Syntax (complete) 

  |DROP PROCEDURE procname

For more information about deleting stored proceduresSee DROP PROCEDURE in Chapter 5, Data Definition (DDL) Statements.