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
The advantages of using stored procedures include:
Applications that access the same database can
share stored procedures, eliminating duplicate code and reducing the
size of the applications.
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
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
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
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
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.
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