Firebird 2.0
PSQL Reference

Overview of PSQL Stored Procedures

A PSQL stored procedure is a self-contained program written in Firebird's PSQL language, and stored as part of a the database metadata.

Once you have created a stored procedure, you can invoke it directly from an application, or substitute the procedure for a table or view in a SELECT statement. Stored procedures can receive input parameters from and return values to applications.

Firebird's PSQL language includes the standard SQL data manipulation statements and some powerful procedural extensions, such as assignments, flow control, cursors, exceptions, and error handling. The PSQL language is very similar, but not identical, to the PSM (Persistent Stored Modules) extension of the SQL200x standard.

The advantages of using stored procedures include:

  • Modular design
    Applications that access the same database can share stored procedures, eliminating duplicate code and reducing the size of the applications.

  • Streamlined maintenance
    When a procedure is updated, the changes are automatically reflected in all applications that use it without the need to recompile and relink them; applications are compiled and optimized only once for each client

  • Improved performance
    Stored procedures are executed by the server, not the client, which reduces network traffic, and improves performance—especially for remote client access

Calling stored procedures

Stored procedures can be used both from applications using dynamic SQL access (such as applications using the ODBC, JDBC or ADO.Net technologies) and from applications using embedded SQL statements (as was common in applications written before the mid-nineties). For more information on calling stored procedures from the latter type of applications, see the Embedded SQL Manual.

There are two types of stored procedures:

  • Executable procedures that an application can call directly with the EXECUTE PROCEDURE statement. An executable procedure can optionally return values to the calling program.

  • SELECT procedures that an application can use in place of a table or view in a SELECT statement. A select procedure must be defined to return one or more values (output parameters), or an error results.

Both kinds of procedures are defined with the CREATE PROCEDURE statement and have essentially the same syntax. The difference is in how the procedure is written and how it is intended to be used. Select procedures can return more than one row, so that to the calling program they appear as a table or view. Executable procedures are routines invoked by the calling program, which can optionally return values.

In fact, a single procedure can be used as a select procedure or as an executable procedure, but in general a procedure is written specifically to be used in a SELECT statement (a select procedure) or to be used in an EXECUTE PROCEDURE statement (an executable procedure).

Privileges for stored procedures

To use a stored procedure, a user must be the creator of the procedure or must be given EXECUTE privilege for it. A procedure executes with the privileges of the user and role currently associated with the connection (“session”).

Stored procedures themselves sometimes need access to tables or views for which a user does not – or should not – have privileges. For such cases the procedure can be granted privileges itself. These privileges are also available to the procedure during its execution, in addition to the privileges of the user/role invoking the procedure.

Creating nested and recursive procedures

A stored procedure can itself execute a stored procedure. Each time a stored procedure calls another procedure, the call is said to be nested because it occurs in the context of a previous and still active call to the first procedure. A stored procedure called by another stored procedure is known as a nested procedure.

If a procedure calls itself, it is recursive. Recursive procedures are useful for tasks that involve repetitive steps. Each invocation of a procedure is referred to as an instance, since each procedure call is a separate entity that performs as if called from an application, reserving memory and stack space as required to perform its tasks.

Error behaviour

When a procedure encounters an error – either a system error or a user-defined exception – all statements since the last SUSPEND are undone.

Since select procedures can have multiple SUSPENDs, possibly inside a loop statement, only the actions since the last SUSPEND are undone. Since executable procedures should not use SUSPEND, when an error occurs the entire executable procedure is undone (if EXIT is used, as recommended).

Altering and dropping procedures in use

You must make special considerations when making changes to stored procedures that are currently in use by other requests. A procedure is in use when it is currently executing, or if it has been compiled internally to the metadata cache by a request.

Changes to procedures are not visible to client applications until they disconnect and reconnect to the database; triggers and procedures that invoke altered procedures don’t have access to the new version until there is a point in which all clients are disconnected.

To simplify the task of altering or dropping stored procedures, it is highly recommended to perform this task during a maintenance period when no client applications are connected to the database. By doing this, all client applications see the same version of a stored procedure before and after you make an alteration.

goto index next page

Legal information