7.2.1. Benefits of Stored Procedures

Stored procedures have the following advantages:

7.2.2. Types of Stored Procedures

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

Executable Procedures

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

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 may have input parameters and the output set is specified by the clause in the header.

A selectable stored procedure is called with a SELECT statement. See 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)

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.

See 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)

For more information about modifying stored procedures

See 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)

See in Chapter 5, Data Definition (DDL) Statements.