# Executable Procedures - Firebird

When you work with Firebird’s stored procedure language and program module structures, it is necessary to make the distinction between procedures that are executed, with the aim of altering data, and those that are intended to return a virtual table to the caller by way of a SELECT statement. The first is the form most familiar to those used to working with other database management systems: the executable procedure.

Complex Processing

One of the more obvious and common uses of executable procedures is to perform complex calculations on input data and perform updates on one or many tables. Complex business rules and routines are centralized on the server. Any client application with the appropriate permissions can invoke the same routines and get the same results, regardless of the host language environment. Apart from the savings in programmer and testing hours, server-side execution eliminates the integrity risks inherent in repeating and maintaining the same complex operations in multiple client language environments.

Support for “Live” Client Sets

Many client interface layers implement dataset or recordset classes that fetch output sets via SELECT statements. These client classes typically provide DML methods that target a single row from a buffer that stores and manages output from a server-side cursor. The row is selected by the user, and the class instance (object) uses the row’s unique key to simulate a positioned update or delete in the underlying database table. For inserts, the object “opens an empty row,” an input list of columns of the same types as those in the buffer, and accepts key and other values as input for the columns.

A single UPDATE, DELETE, or INSERT statement in SQL can operate on only one table. When the dataset (recordset) is selected from a natural table and contains the table’s unique key, it can be considered “live,” since its methods can pass an ordinary UPDATE, DELETE, or INSERT statement. The usual term for this type of set is naturally updatable.

A set that joins multiple tables is not naturally updatable. Executable stored procedures can be designed with input arguments that accept keys and values for multiple tables and execute the required operations on each table. The technique allows the client applications to treat joined sets as though they were “live.”

Operations in Executable Procedures

Virtually every data manipulation in SQL is available to the executable stored procedure. All activity is executed within the transaction context of the caller and is committed when the transaction commits. Rows that are changed by an operation in the procedure are versioned in exactly the same way as those posted by a DML request from the client.

Procedures can call other procedures, passing variables to input arguments and accepting return values into variables by way of a RETURNING _VALUES clause. They can insert one or many rows, change single rows, form cursors to target rows serially for positioned updates and deletes, and perform searched updates and deletes.

Once the procedure begins executing, values brought in as input arguments become local variables. Output arguments are read-write variables that can change value (though not data type) many thousands of times during the course of execution. Figure illustrates typical activities in an executable procedure.

Operations in an executable procedure

A Multi-Table Procedure

The executable procedure DELETE _EMPLOYEE is a version of one that you can find in the EMPLOYEE database in your Firebird /examples directory. It implements some business rules regarding employees who are leaving the company.

Declaring an Exception

Because we are going to use an exception in this procedure, it needs to be created before the procedure:

CREATE EXCEPTION REASSIGN_SALES
'Reassign the sales records before deleting this employee.' ^
COMMIT ^

The Procedure

Now, the procedure itself. The input argument, EMP_NUM, corresponds to the primary key of the EMPLOYEE table, EMP _NO. It allows the procedure to select and operate on a single employee record and foreign keys in other tables that reference that record.

CREATE PROCEDURE DELETE_EMPLOYEE (
EMP_NUM INTEGER )
AS
DECLARE VARIABLE any_sales INTEGER DEFAULT 0;
BEGIN

We want to establish whether the employee has any sales orders pending. If so, we raise an exception. For now, we let the procedure terminate and use the exception message to
inform the caller of the situation.

The SELECT ... INTO Construct

The SELECT ... INTO construct is very common in PSQL. When values are queried from tables, the INTO clause enables them to be stored into variables—local variables or output arguments. In this procedure, there are no output arguments. We use the variable ANY_SALES that we declared and initialized at the head of the procedure body to store a count of some sales records. Notice the colon (:) prefix on the variable ANY_SALES. We will look at that when the procedure is done.

SELECT count(po_number) FROM sales
WHERE sales_rep = :emp_num
INTO :any_sales;
IF (any_sales > 0) THEN
EXCEPTION reassign_sales;

In the case that order records are found, the procedure ends tidily with the EXCEPTION statement that, in the absence of an exception handler, takes execution directly to the last END statement in the whole procedure. Under these conditions, the procedure has completed and the exception message is transmitted back to the caller.

If there is no exception, execution continues. Next, the procedure has some little jobs to do, to update some positions as vacant (NULL) if they are currently held by our employee, remove the employee from projects, and delete his or her salary history. Finally, the employee record itself is deleted.

UPDATE department
SET mngr_no = NULL
WHERE mngr_no = :emp_num;
UPDATE project
SET team_leader = NULL
WHERE team_leader = :emp_num;
DELETE FROM employee_project
WHERE emp_no = :emp_num;
DELETE FROM salary_history
WHERE emp_no = :emp_num;
DELETE FROM employee
WHERE emp_no = :emp_num;

Job done, employee gone. An optional EXIT statement can be included for documentation purposes. It can help a lot when you are navigating scripts where there are many procedure definitions and those procedures have many nested BEGIN

.. . END
blocks:
EXIT;
END ^
COMMIT^

The Colon (:) Prefix for Variables

In this procedure, we noted two different ways in which the colon prefix was used on variables:

• Earlier, it was applied to the local variable :ANY_SALES when it was being used in an INTO clause as the destination for a data item returned from a SELECT.
• In the latter statements, it was used for a slightly different reason. PSQL syntax requires the colon prefix to be on any variable or argument when it is used in a DSQL statement.

These two usages of the colon prefix are consistent throughout PSQL. If you forget them when they should be used, or use them when PSQL does not require them, your procedures will not compile and the parser will throw an exception. Worse, if a variable of the same name as a column in the table is used in an SQL statement, without the colon, the engine thinks it is the column that is being referred to, processes it on that assumption, and throws no exception. Needless to say, the result of the operation will be quite unpredictable.

Using (Calling) Executable Procedures

An executable procedure is invoked with EXECUTE PROCEDURE. It can return, at most, one row of output. To execute a stored procedure in isql , use the following syntax:

EXECUTE PROCEDURE name [(] [argument [, argument ...]] [)];

The procedure name must be specified.

Calling Rules About Input Argument Values

The calling rules pertaining to arguments can be summarized as follows:

• Values must be supplied for all input arguments.
• If there are multiple input arguments, they are passed as a comma-separated list.
• Each argument is a constant, an expression that evaluates to a constant or a replaceable parameter.
• Variables can be passed as input arguments only inside a PSQL module.
• Replaceable parameters can be passed only by external DSQL statements
• Constants and expressions that resolve to constants are valid for any call.
• Expressions that operate on variables or replaceable parameters are not allowed.
• Brackets enclosing the argument list are optional. Because our procedure DELETE_EMPLOYEE has no return arguments, the syntax for calling it from a client application or from another procedure is the same:
EXECUTE PROCEDURE DELETE_EMPLOYEE (29);

However, when executing a procedure from within another procedure, input arguments can be (and usually are) furnished as variables. Because EXECUTE PROCEDURE is a DSQL statement, the syntax requires that the variable name be prefixed by a colon:

EXECUTE PROCEDURE DELETE_EMPLOYEE (:EMP_NUMBER);

Another procedure ADD_EMP_PROJ takes two input arguments, keys for an employee and a project, respectively. An instance might be called like this:

EXECUTE PROCEDURE ADD_EMP_PROJ (32, 'MKTPR');

Replaceable parameters are used for input arguments when calling this procedure from a client application:

EXECUTE PROCEDURE ADD_EMP_PROJ (?, ?);

Outputs and Exits

If an output parameter has not been assigned a value, its value is unpredictable and this can lead to errors, sometimes subtle enough to compromise data integrity. A procedure should ensure that all output parameters are initialized to a default final value, in advance of the processing that will assign values, to ensure that valid output is available when SUSPEND or EXIT is executed.

EXIT and SUSPEND

In both select and executable procedures, EXIT causes execution to jump immediately to the final END statement in the procedure, without executing anything.

What happens when a procedure reaches the final END statement depends on the type of procedure:

• In a SELECT procedure, the SQLCODE 100 is set to indicate that there are no more rows to retrieve, and control returns to the caller.
• In an executable procedure, control returns to the caller, passing the final output values, if any. Calling triggers or procedures receive the output into variables, as RETURNING_VALUES. Applications receive them in a record structure. In executable procedures, SUSPEND has exactly the same effect as EXIT.

Recursive Procedures

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.

Stored procedures can be nested up to 1,000 levels deep. This limitation helps to prevent infinite loops that can occur when a recursive procedure provides no absolute terminating condition. However, memory and stack limitations of the server can restrict nesting to fewer than 1,000 levels.

The procedure DEPT _BUDGET, in the example employee database, illustrates how a recursive procedure might work. It accepts as input a code DNO, equivalent to DEPT_NO, the key of the DEPARTMENT table. DEPARTMENT has a multiple-level tree structure: Each department that is not a head department has a foreign key HEAD _DEPT pointing to the DEPT_NO of its immediate “parent.”

The procedure queries for the targeted DEPARTMENT by the input key. It saves the BUDGET value for this row into the output variable TOT. It also performs a count of the number of departments immediately below this one in the departmental structure. If there are no sub-departments, the EXIT statement causes execution to jump right through to the final END ^ statement. The current value of TOT is output and the procedure ends.

SET TERM ^ ;
CREATE PROCEDURE DEPT_BUDGET (
DNO CHAR(3) )
RETURNS (
TOT DECIMAL(12,2) )
AS
DECLARE VARIABLE sumb DECIMAL(12, 2);
DECLARE VARIABLE rdno CHAR(3);
DECLARE VARIABLE cnt INTEGER;
BEGIN
tot = 0;
SELECT budget FROM department WHERE dept_no = :dno INTO :tot;
SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;
IF (cnt = 0) THEN
EXIT;

If there are sub-departments, execution continues. The input code DNO is used in the WHERE clause of a FOR . .. SELECT cursor (see the section “Cursors in PSQL”) to target each DEPARTMENT row, in turn, that has this DNO as its HEAD _DEPT code and place its DEPT_NO into local variable, RDNO:

FOR SELECT dept_no FROM department
WHERE head_dept = :dno
INTO :rdno DO
BEGIN

This local variable now becomes the input code for a recursive procedure call. At each recursion, the output value TOT is incremented by the returned budget value, until all of the eligible rows have been processed:

EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;
tot = tot + sumb;
END

Finally, the return value accumulated by the recursions is passed to the caller and the procedure is done:

EXIT; /* the EXIT statement is optional */
END^
COMMIT^

Calling the Procedure

This time, our procedure has input parameters. Our simple DSQL call might look like this:

EXECUTE PROCEDURE DEPT_BUDGET ('600');

Or, we can use a replaceable parameter:

EXECUTE PROCEDURE DEPT_BUDGET (?);

Cursors in PSQL

Cursors consist of three main elements:

• A set of rows defined by a SELECT expression
• A pointer that passes through the set from the first row to the last, isolating that row for some kind of activity
• A set of variables—defined as local variables, output arguments, or both—to receive the columns returned in each row as it is touched by the pointer

The passage of the pointer through the set can be considered a “looping” operation. The operations that occur during the “loop” when a row has the pointer can be simple or complex.

PSQL has two cursor implementations, one well known, the other not previously documented:

• The better -known implementation is surfaced in the FOR ... SELECT construct, which fully implements looping syntax and is widely used for selectable procedures. It is discussed in detail in the next section. It is, however, a perfectly valid and common usage to run FOR . .. SELECT cursor loops inside an executable procedure, as we saw in the preceding example.
• The lesser-known implementation is an earlier implementation, sometimes known as an updatable or named cursor, that was inherited from ESQL and has long been considered as deprecated. It surfaces a cursor object and allows positioned updates and deletes. Its syntax will be more familiar to those who have used cursors in the procedure languages of other DBMSs. We take a brief look at it now.

The syntax and machinery for the named cursor have been undergoing a reimplementation during the ongoing development for Firebird 2. This brief description is the swan song of the old cursor, since the new syntax is unlikely to be compatible with the old.

The “Old” Updatable Cursor

The syntax pattern looks like this:

...
FOR SELECT <column-list>
FROM <named-table>
FOR UPDATE
INTO <variables>
AS CURSOR <cursor-name>
DO
/* either UPDATE ... */
BEGIN
UPDATE <named-table>
SET ... WHERE CURRENT OF <cursor-name>;
...
END
/* or DELETE */
BEGIN
DELETE FROM <named-table>
WHERE CURRENT OF <cursor-name>
END
...

Use it for now, but expect enhancements in a later version of Firebird. It is a very fast way to do bulk operations in a stored procedure, since it uses the RDB$DB_KEY to locate its targets. RDB$DB_KEY (or just “the DBKEY”) is an internal feature that can be used with care in a number of situations. See the section “Optimization Topic: Using the Internal RDB\$DB_KEY” at the end of this chapter.

The latter implementation of cursors, discussed next, is much more flexible.