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.
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
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 (
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
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
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
The Colon (:) Prefix for Variables
In this procedure, we noted two different ways in which the colon prefix was used on variables:
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:
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:
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 ^ ;
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
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;
Finally, the return value accumulated by the recursions is passed to the caller and the procedure is done:EXIT; /* the EXIT statement is optional */
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:
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 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:...
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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.