Using WHERE and ORDER BY clauses
A SELECT from a stored procedure can contain WHERE and ORDER BY clauses, just
as in a SELECT from a table or view.
The WHERE clause limits the results returned by the procedure to rows matching
the search condition. For example, the following statement returns only those
rows where the HEAD_DEPT is Sales and Marketing:
SELECT * FROM ORG_CHART WHERE HEAD_DEPT = 'Sales and Marketing';
The stored procedure then returns only the matching rows, for example:
--------------------------------------------------
The ORDER BY clause can be used to order the results returned by the procedure.
For example, the following statement orders the results by EMP_CNT, the number
of employees in each department, in ascending order (the default):
SELECT * FROM ORG_CHART ORDER BY EMP_CNT;
Selecting aggregates from procedures
In addition to selecting values from a procedure, you can use aggregate functions.
For example, to use ORG_CHART to display a count of the number of departments,
use the following statement:
SELECT COUNT(DEPARTMENT) FROM ORG_CHART;
The results are:
COUNT
============
24
Similarly, to use ORG_CHART to display the maximum and average number of
employees in each department, use the following statement:
SELECT MAX(EMP_CNT), AVG(EMP_CNT) FROM ORG_CHART;
The results are:
If a procedure encounters an error or exception, the aggregate functions do not
return the correct values, since the procedure terminates before all rows are
processed.
Viewing arrays with stored procedures
If a table contains columns defined as arrays, you cannot view the data in the
column with a simple SELECT statement, since only the array ID is stored in the
table. Arrays can be used to display array values, as long as the dimensions and
datatype of the array column are known in advance.
For example, in the employee database, the JOB table has a column named
LANGUAGE_REQ containing the languages required for the position. The column is
defined as an array of five VARCHAR(15).
In isql, if you perform a simple SELECT statement, such as:
SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY, LANGUAGE_REQ FROM JOB;
MAX
=======
AVG
=======
5 2
If a procedure encounters an error or exception, the aggregate functions do not
return the correct values, since the procedure terminates before all rows are
processed.
Viewing arrays with stored procedures
If a table contains columns defined as arrays, you cannot view the data in the
column with a simple SELECT statement, since only the array ID is stored in the
table. Arrays can be used to display array values, as long as the dimensions and
datatype of the array column are known in advance.
For example, in the employee database, the JOB table has a column named
LANGUAGE_REQ containing the languages required for the position. The column is
defined as an array of five VARCHAR(15).
In isql, if you perform a simple SELECT statement, such as:
SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY, LANGUAGE_REQ FROM JOB;
part of the results look like this:
To view the contents of the LANGUAGE_REQ column, use a stored procedure, such
as the following:
CREATE PROCEDURE VIEW_LANGS
RETURNS (code VARCHAR(5), grade SMALLINT, cty VARCHAR(15),
lang VARCHAR(15))
AS
DECLARE VARIABLE i INTEGER;
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY
FROM JOB
WHERE LANGUAGE_REQ IS NOT NULL
INTO :code, :grade, :cty
DO
BEGIN
i = 1;
WHILE (i <= 5) DO
BEGIN
SELECT LANGUAGE_REQ[:i] FROM JOB
WHERE ((JOB_CODE = :code) AND (JOB_GRADE = :grade)
AND (JOB_COUNTRY = :cty)) INTO :lang;
i = i + 1;
SUSPEND;
END
END
END ;
This procedure, VIEW_LANGS, uses a FOR … SELECT loop to retrieve each row from
JOB for which LANGUAGE_REQ is not NULL. Then a WHILE loop retrieves each
element of the LANGUAGE_REQ array and returns the value to the calling
application (in this case, isql).
For example, if this procedure is invoked with:
SELECT * FROM VIEW_LANGS;
This procedure can easily be modified to return only the language requirements
for a particular job, when passed JOB_CODE, JOB_GRADE, and JOB_COUNTRY as
input parameters.
Exceptions
An exception is a named error message that can be raised from a stored procedure.
Exceptions are created with CREATE EXCEPTION, modified with ALTER EXCEPTION,
and dropped with DROP EXCEPTION. A stored procedure raises an exception with
EXCEPTION name.
When raised, an exception returns an error message to the calling program and
terminates execution of the procedure that raised it, unless the exception is
handled by a WHEN statement.
Important Like procedures, exceptions are created and stored in a database, where they can
be used by any procedure that needs them. Exceptions must be created and
committed before they can be raised.
For more information on raising and handling exceptions, see “Raising an exception in
a stored procedure” on page 9-28.
Creating exceptions
To create an exception, use the following CREATE EXCEPTION syntax:
CREATE EXCEPTION name '<message>';
For example, the following statement creates an exception named
REASSIGN_SALES:
CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records
before deleting this employee.';
Altering exceptions
To change the message returned by an exception, use the following syntax:
ALTER EXCEPTION name '<message>';
Only the creator of an exception can alter it. For example, the following statement
changes the text of the exception created in the previous section:
ALTER EXCEPTION REASSIGN_SALES 'Can’t delete employee--Reassign
Sales';
You can alter an exception even though a database object depends on it. If the
exception is raised by a trigger, you cannot drop the exception unless you first
drop the trigger or stored procedure. Use ALTER EXCEPTION instead.
Dropping exceptions
To delete an exception, use the following syntax:
DROP EXCEPTION name;
For example, the following statement drops the exception, REASSIGN_SALES:
DROP EXCEPTION REASSIGN_SALES;
The following restrictions apply to dropping exceptions:
• Only the creator of an exception can drop it.
• Exceptions used in existing procedures and triggers cannot be dropped.
• Exceptions currently in use cannot be dropped.
Tip In isql, SHOW PROCEDURES displays a list of dependencies, the procedures,
exceptions, and tables which the stored procedure uses. SHOW PROCEDURE name
displays the body and header information for the named procedure. SHOW
TRIGGERS table displays the triggers defined for table. SHOW TRIGGER name displays
the body and header information for the named trigger.
Raising an exception in a stored procedure
To raise an exception in a stored procedure, use the following syntax:
EXCEPTION name;
where name is the name of an exception that already exists in the database.
When an exception is raised, it does the following:
• Terminates the procedure in which it was raised and undoes any actions
performed (directly or indirectly) by the procedure.
• Returns an error message to the calling application. In isql, the error message is
displayed on the screen.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-29
H a n d l i n g e r r o r s
Note If an exception is handled with a WHEN statement, it behaves differently. For more
information on exception handling, see “Handling exceptions” on page 9-29.
The following statements raise the exception, REASSIGN_SALES:
IF (any_sales > 0) THEN
EXCEPTION REASSIGN_SALES;
Handling errors
Procedures can handle three kinds of errors with a WHEN … DO statement:
• Exceptions raised by EXCEPTION statements in the current procedure, in a
nested procedure, or in a trigger fired as a result of actions by such a procedure.
• SQL errors reported in SQLCODE.
• InterBase errors reported in GDSCODE.
The WHEN ANY statement handles any of the three types of errors.
For more information about InterBase error codes and SQLCODE values, see the
Language Reference.
The syntax of the WHEN … DO statement is:
WHEN {<error> [, <error> …] | ANY}
DO <compound_statement>
<error> =
{EXCEPTION exception_name | SQLCODE number | GDSCODE errcode}
Important If used, WHEN must be the last statement in a BEGIN … END block. It should come
after SUSPEND, if present.
Handling exceptions
Instead of terminating when an exception occurs, a procedure can respond to and
perhaps correct the error condition by handling the exception. When an exception
is raised, it does the following:
• Seeks a WHEN statement that handles the exception. If one is not found, it
terminates execution of the BEGIN … END block containing the exception and
undoes any actions performed in the block.
• Backs out one level to the surrounding BEGIN … END block and seeks a WHEN
statement that handles the exception, and continues backing out levels until one
is found. If no WHEN statement is found, the procedure is terminated and all its
actions are undone.
• Performs the ensuing statement or block of statements specified by the WHEN
statement that handles the exception.
• Returns program control to the block in the procedure following the WHEN
statement.
Note An exception that is handled does not return an error message.
Handling SQL errors
Procedures can also handle error numbers returned in SQLCODE. After each SQL
statement executes, SQLCODE contains a status code indicating the success or
failure of the statement. SQLCODE can also contain a warning status, such as when
there are no more rows to retrieve in a FOR SELECT loop.
For example, if a procedure attempts to insert a duplicate value into a column
defined as a PRIMARY KEY, InterBase returns SQLCODE -803. This error can be
handled in a procedure with the following statement:
WHEN SQLCODE -803
DO
BEGIN
. . .
The following procedure includes a WHEN statement to handle SQLCODE -803
(attempt to insert a duplicate value in a UNIQUE key column). If the first column in
TABLE1 is a UNIQUE key, and the value of parameter A is the same as one already in
the table, then SQLCODE -803 is generated, and the WHEN statement sets an error
message returned by the procedure.
CREATE PROCEDURE NUMBERPROC (A INTEGER, B INTEGER)
RETURNS (E CHAR(60)) AS
BEGIN
BEGIN
INSERT INTO TABLE1 VALUES (:A, :B);
WHEN SQLCODE -803 DO
E = 'Error Attempting to Insert in TABLE1 - Duplicate Value.';
END;
END;!
For more information about SQLCODE, see the Language Reference.
Handling InterBase errors
Procedures can also handle InterBase errors. For example, suppose a statement in
a procedure attempts to update a row already updated by another transaction, but
not yet committed. In this case, the procedure might receive an InterBase error
LOCK_CONFLICT. If the procedure retries its update, the other transaction might
have rolled back its changes and released its locks. By using a WHEN GDSCODE
statement, the procedure can handle lock conflict errors and retry its operation.
To handle InterBase error codes, use the following syntax:
WHEN GDSCODE errcode DO <compound_statement>;
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-31
H a n d l i n g e r r o r s
For more information about InterBase error codes, see the Language Reference.
Examples of error behavior and handling
When a procedure encounters an error—either a SQLCODE error, GDSCODE error,
or user-defined exception—the statements since the last SUSPEND are undone.
SUSPEND should not be used in executable procedures. EXIT should be used to
terminate the procedure. If this recommendation is followed, then when an
executable procedure encounters an error, the entire procedure is undone. Since
select procedures can have multiple SUSPENDs, possibly inside a loop statement,
only the actions since the last SUSPEND are undone.
For example, here is a simple executable procedure that attempts to insert the
same values twice into the PROJECT table.
CREATE PROCEDURE NEW_PROJECT
(id CHAR(5), name VARCHAR(20), product VARCHAR(12))
RETURNS (result VARCHAR(80))
AS
BEGIN
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values inserted OK.';
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values Inserted Again.';
EXIT;
WHEN SQLCODE -803 DO
BEGIN
result = 'Could Not Insert Into Table - Duplicate Value';
EXIT;
END
END ;
This procedure can be invoked with a statement such as:
EXECUTE PROCEDURE NEW_PROJECT 'XXX', 'Project X', 'N/A';
The second INSERT generates an error (SQLCODE -803, “invalid insert—no two
rows can have duplicate values.”). The procedure returns the string, “Could Not
Insert Into Table - Duplicate Value,” as specified in the WHEN clause, and the
entire procedure is undone.
The next example is written as a select procedure, and invoked with the SELECT
statement that follows it:
. . .
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values inserted OK.';
SUSPEND;
9-32 D a t a D e f i n i t i o n G u i d e
H a n d l i n g e r r o r s
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values Inserted Again.';
SUSPEND;
WHEN SQLCODE -803 DO
BEGIN
result = 'Could Not Insert Into Table - Duplicate Value';
EXIT;
END
SELECT * FROM SIMPLE('XXX', 'Project X', 'N/A');
The first INSERT is performed, and SUSPEND returns the result string, “Values
Inserted OK.” The second INSERT generates the error because there have been no
statements performed since the last SUSPEND, and no statements are undone. The
WHEN statement returns the string, “Could Not Insert Into Table - Duplicate
Value”, in addition to the previous result string.
The select procedure successfully performs the insert, while the executable
procedure does not.
The next example is a more complex stored procedure that demonstrates SQLCODE
error handling and exception handling. It is based on the previous example of a
select procedure, and does the following:
• Accepts a project ID, name, and product type, and ensures that the ID is in all
capitals, and the product type is acceptable.
• Inserts the new project data into the PROJECT table, and returns a string
confirming the operation, or an error message saying the project is a duplicate.
• Uses a FOR … SELECT loop with a correlated subquery to get the first three
employees not assigned to any project and assign them to the new project using
the ADD_EMP_PROJ procedure.
• If the CEO’s employee number is selected, raises the exception, CEO, which is
handled with a WHEN statement that assigns the CEO’s administrative assistant
(employee number 28) instead to the new project.
Note that the exception, CEO, is handled within the FOR … SELECT loop, so that
only the block containing the exception is undone, and the loop and procedure
continue after the exception is raised.
CREATE EXCEPTION CEO 'Can’t Assign CEO to Project.';
CREATE PROCEDURE NEW_PROJECT
(id CHAR(5), name VARCHAR(20), product VARCHAR(12))
RETURNS (result VARCHAR(30), num smallint)
AS
DECLARE VARIABLE emp_wo_proj smallint;
DECLARE VARIABLE i smallint;
BEGIN
id = UPPER(id); /* Project id must be in uppercase. */
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'New Project Inserted OK.';
SUSPEND;
/* Add Employees to the new project */
i = 0;
result = 'Project Got Employee Number:';
FOR SELECT EMP_NO FROM EMPLOYEE
WHERE EMP_NO NOT IN (SELECT EMP_NO FROM EMPLOYEE_PROJECT)
INTO :emp_wo_proj
DO
BEGIN
IF (i < 3) THEN
BEGIN
IF (emp_wo_proj = 5) THEN
EXCEPTION CEO;
EXECUTE PROCEDURE ADD_EMP_PROJ :emp_wo_proj, :id;
num = emp_wo_proj;
SUSPEND;
END
ELSE
EXIT;
i = i + 1;
WHEN EXCEPTION CEO DO
BEGIN
EXECUTE PROCEDURE ADD_EMP_PROJ 28, :id;
num = 28;
SUSPEND;
END
END
/* Error Handling */
WHEN SQLCODE -625 DO
BEGIN
IF ((:product <> 'software') OR (:product <> 'hardware') OR
(:product <> 'other') OR (:product <> 'N/A')) THEN
result = 'Enter product: software, hardware, other, or N/A';
END
WHEN SQLCODE -803 DO
result = 'Could not insert into table - Duplicate Value';
END ;
This procedure can be called with a statement such as:
SELECT * FROM NEW_PROJECT('XYZ', 'Alpha project', 'software');
With results such as the following:
RESULT NUM
=========================== ======
New Project Inserted OK. <null>
Project Got Employee Number: 28
Project Got Employee Number: 29
Project Got Employee Number: 36
of NUM, the input parameter.
CREATE PROCEDURE FACTORIAL (NUM INT)
RETURNS (N_FACTORIAL DOUBLE PRECISION)
AS
DECLARE VARIABLE NUM_LESS_ONE INT;
BEGIN
IF (NUM = 1) THEN
BEGIN /**** BASE CASE: 1 FACTORIAL IS 1 ****/
N_FACTORIAL = 1;
SUSPEND;
END
ELSE
9-16 D a t a D e f i n i t i o n G u i d e
C r e a t i n g p r o c e d u r e s
BEGIN /**** RECURSION: NUM FACTORIAL = NUM * (NUM-1) FACTORIAL ****/
NUM_LESS_ONE = NUM - 1;
EXECUTE PROCEDURE FACTORIAL NUM_LESS_ONE
RETURNING_VALUES N_FACTORIAL;
N_FACTORIAL = N_FACTORIAL * NUM;
SUSPEND;
END
END ;
The following C code demonstrates how a host-language program would call
FACTORIAL:
. . .
printf('\nCalculate factorial for what value? ');
scanf('%d', &pnum);
EXEC SQL
EXECUTE PROCEDURE FACTORIAL :pnum RETURNING_VALUES :pfact;
printf('%d factorial is %d.\n', pnum, pfact);
. . .
Recursion nesting restrictions would not allow this procedure to calculate
factorials for numbers greater than 1,001. Arithmetic overflow, however, occurs
for much smaller numbers.
Using SUSPEND, EXIT, and END with procedures
The SUSPEND statement suspends execution of a select procedure, passes control
back to the program, and resumes execution from the next statement when the
next FETCH is executed. SUSPEND also returns values in the output parameters of a
stored procedure.
SUSPEND should not be used in executable procedures, since the statements that
follow it will never execute. Use EXIT instead to indicate to the reader explicitly
that the statement terminates the procedure.
In a select procedure, the SUSPEND statement returns current values of output
parameters to the calling program and continues execution. If an output
parameter has not been assigned a value, its value is unpredictable, which can
lead to errors. A procedure should ensure that all output parameters are assigned
values before a SUSPEND.
In both select and executable procedures, EXIT jumps program control to the final
END statement in the procedure.
What happens when a procedure reaches the final END statement depends on the
type of procedure:
• In a select procedure, the final END statement returns control to the application
and sets SQLCODE to 100, which indicates there are no more rows to retrieve.
• In an executable procedure, the final END statement returns control and values
of output parameters, if any, to the calling application.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-17
C r e a t i n g p r o c e d u r e s
The behavior of these statements is summarized in the following table:
Consider the following procedure:
CREATE PROCEDURE P RETURNS (R INTEGER)
AS
BEGIN
R = 0;
WHILE (R < 5) DO
BEGIN
R = R + 1;
SUSPEND;
IF (R = 3) THEN
EXIT;
END
END ;
If this procedure is used as a select procedure, for example:
SELECT * FROM P;
then it returns values 1, 2, and 3 to the calling application, since the SUSPEND
statement returns the current value of R to the calling application. The procedure
terminates when it encounters EXIT.
If the procedure is used as an executable procedure, for example:
EXECUTE PROCEDURE P;
then it returns 1, since the SUSPEND statement terminates the procedure and
returns the current value of R to the calling application. This is not recommended,
but is included here for comparison.
Note If a select procedure has executable statements following the last SUSPEND in the
procedure, all of those statements are executed, even though no more rows are
returned to the calling program. The procedure terminates with the final END
statement.
Table 9.3 SUSPEND, EXIT, and END
Procedure type SUSPEND EXIT END
Select procedure • Suspends execution of
procedure until next
FETCH
• Returns values
Jumps to final END • Returns control to
application
• Sets SQLCODE to 100
Executable procedure • Jumps to final END
• Not recommended
Jumps to final END • Returns values
• Returns control to
application
9-18 D a t a D e f i n i t i o n G u i d e
A l t e r i n g a n d d r o p p i n g s t o r e d p r o c e d u r e s
Error behavior
When a procedure encounters an error—either a SQLCODE error, GDSCODE error,
or 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 stored procedures
This section describes techniques and issues for changing and deleting
procedures.
Tip To see a list of database procedures and their dependencies, use the isql
command:
SHOW PROCEDURES;
Altering stored procedures
To change a stored procedure, use ALTER PROCEDURE. This statement changes the
definition of an existing stored procedure while preserving its dependencies
according to which metadata objects reference the stored procedure, and which
objects the stored procedure references.
Changes made to a procedure are transparent to all client applications that use the
procedure; you do not have to rebuild the applications. However, see “Altering and
dropping procedures in use” on page 9-20 for issues of managing versions of stored
procedures.
Only SYSDBA and the owner of a procedure can alter it.
Important Be careful about changing the type, number, and order of input and output
parameters to a procedure, since existing code might assume that the procedure
has its original format.
When you alter a procedure, the new procedure definition replaces the old one. To
alter a procedure, follow these steps:
1 Copy the original data definition file used to create the procedure.
Alternatively, use isql -extract to extract a procedure from the database to a file.
2 Edit the file, changing CREATE to ALTER, and changing the procedure definition
as desired. Retain whatever is still useful.
ALTER PROCEDURE syntax
The syntax for ALTER PROCEDURE is similar to CREATE PROCEDURE as shown in the
following syntax:
ALTER PROCEDURE name
[(var datatype [, var datatype …])]
[RETURNS (var datatype [, var datatype …])]
AS
procedure_body;
The procedure name must be the name of an existing procedure. The arguments of
the ALTER PROCEDURE statement are the same as those for CREATE PROCEDURE
(see “Arguments of the CREATE PROCEDURE statement” on page 9-5).
Dropping procedures
The DROP PROCEDURE statement deletes an existing stored procedure from the
database. DROP PROCEDURE can be used interactively with isql or in a data
definition file.
The following restrictions apply to dropping procedures:
• Only SYSDBA and the owner of a procedure can drop it.
• You can’t drop a procedure used by other procedures, triggers, or views; alter
the other metadata object so that it does not reference the procedure, then drop
the procedure.
• You can’t drop a procedure that is recursive or in a cyclical dependency with
another procedure; you must alter the procedure to remove the cyclical
dependency, then drop the procedure.
• You can’t drop a procedure that is currently in use by an active transaction;
commit the transaction, then drop the procedure.
• You can’t drop a procedure with embedded SQL; use dynamic SQL.
If you attempt to drop a procedure and receive an error, make sure you have
entered the procedure name correctly.
Drop procedure syntax
The syntax for dropping a procedure is:
DROP PROCEDURE name;
The procedure name must be the name of an existing procedure. The following
statement deletes the ACCOUNTS_BY_CLASS procedure:
DROP PROCEDURE ACCOUNTS_BY_CLASS;
9-20 D a t a D e f i n i t i o n G u i d e
A l t e r i n g a n d d r o p p i n g s t o r e d p r o c e d u r e s
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.
Tip You can minimize the maintenance period by performing the procedure alteration
while the database is in use, and then briefly closing all client applications. It is
safe to alter procedures while the database is in use.
Internals of the technology
Below is a detailed description of the internal maintenance of stored procedure
versions, to help explain the behavior of the technology.
When any request invokes a stored procedure, the current definition for that stored
procedure is copied at that moment to a metadata cache. This copy persists for the
lifetime of the request that invoked the stored procedure.
A request is one of the following:
• A client application that executes the stored procedure directly
• A trigger that executes the stored procedure; this includes system triggers that
are part of referential integrity or check constraints
• Another stored procedure that executes the stored procedure
Altering or dropping a stored procedure takes effect immediately; new requests
that invoke the altered stored procedure see the latest version. However,
outstanding requests continue to see the version of the stored procedure that they
first saw, even if a newer version has been created after the request’s first
invocation of the stored procedure. There is no method to force these outstanding
requests to update their metadata cache.
A trigger or stored procedure request persists in the metadata cache while there
are one or more clients connected to the database, regardless of whether the client
makes use of the trigger or stored procedure. These requests never update as long
as any client is connected to the database. These requests are emptied from the
metadata cache only when the last client disconnects from the database.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-21
U s i n g s t o r e d p r o c e d u r e s
Important The only way to guarantee that all copies of a stored procedure are purged from
the metadata cache is for all connections to the database to terminate. Only then
are all metadata objects emptied from the metadata cache. Subsequent connections
and triggers spawned by them are new requests, and they see the newest version
of the stored procedure.
Using stored procedures
Stored procedures can be used in applications in a variety of ways. Select
procedures are used in place of a table or view in a SELECT statement. Executable
procedures are used with an EXECUTE PROCEDURE statement.
Both kinds of procedures are defined with CREATE PROCEDURE and have the same
syntax. The difference is in how the procedure is written and how it is intended to
be used. Select procedures always return one or more rows, so that to the calling
program they appear as a table or view. Executable procedures are simply
routines invoked by the calling program and only optionally return values.
In fact, a single procedure can be used as a select procedure or an executable
procedure, but this is not recommended. A procedure should be written
specifically to be used in a SELECT statement (a select procedure) or to be used in
an EXECUTE PROCEDURE statement (an executable procedure).
During application development, create and test stored procedures in isql. Once a
stored procedure has been created, tested, and refined, it can be used in
applications. For more information on using stored procedures in applications, see
the Embedded SQL Guide.
Using executable procedures in isql
An executable procedure is invoked with EXECUTE PROCEDURE. It can return at
most one row. To execute a stored procedure in isql, use the following syntax:
EXECUTE PROCEDURE name [(] [param [, param …]] [)];
The procedure name must be specified, and each param is an input parameter value
(a constant). All input parameters required by the procedure must be supplied.
Important In isql, do not supply output parameters or use RETURNING_VALUES in the
EXECUTE PROCEDURE statement, even if the procedure returns values. isql
automatically displays output parameters.
To execute the procedure, DEPT_BUDGET, from isql, use:
EXECUTE PROCEDURE DEPT_BUDGET 110;
isql displays this output:
TOT
====================
1700000.00
9-22 D a t a D e f i n i t i o n G u i d e
U s i n g s t o r e d p r o c e d u r e s
Using select procedures in isql
A select procedure is used in place of a table or view in a SELECT statement and can
return a single row or multiple rows.
The advantages of select procedures over tables or views are:
• They can take input parameters that can affect the output.
• They can contain logic not available in normal queries or views.
• They can return rows from multiple tables using UNION.
The syntax of SELECT from a procedure is:
SELECT <col_list> from name ([param [, param …]])
WHERE <search_condition>
ORDER BY <order_list>;
The procedure name must be specified, and in isql each param is a constant passed
to the corresponding input parameter. All input parameters required by the
procedure must be supplied. The col_list is a comma-delimited list of output
parameters returned by the procedure, or * to select all rows.
The WHERE clause specifies a search_condition that selects a subset of rows to
return. The ORDER BY clause specifies how to order the rows returned. For more
information on SELECT, see the Language Reference.
Note The following code defines the procedure, GET_EMP_PROJ, which returns EMP_PROJ,
the project numbers assigned to an employee, when it is passed the employee
number, EMP_NO, as the input parameter.
CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (EMP_PROJ SMALLINT) AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :EMP_PROJ
DO
SUSPEND;
END ;
The following statement selects from GET_EMP_PROJ in isql:
SELECT * FROM GET_EMP_PROJ(24);
The output is:
PROJ_ID
=======
DGPII
GUIDE
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-23
U s i n g s t o r e d p r o c e d u r e s
The following select procedure, ORG_CHART, displays an organizational chart:
CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
AS
DECLARE VARIABLE MNGR_NO INTEGER;
DECLARE VARIABLE DNO CHAR(3);
BEGIN
FOR SELECT H.DEPARTMENT, D.DEPARTMENT, D.MNGR_NO, D.DEPT_NO
FROM DEPARTMENT D
LEFT OUTER JOIN DEPARTMENT H ON D.HEAD_DEPT = H.DEPT_NO
ORDER BY D.DEPT_NO
INTO :HEAD_DEPT, :DEPARTMENT, :MNGR_NO, :DNO
DO
BEGIN
IF (:MNGR_NO IS NULL) THEN
BEGIN
MNGR_NAME = '--TBH--';
TITLE = '';
END
ELSE
SELECT FULL_NAME, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NO = :MNGR_NO
INTO :MNGR_NAME, :TITLE;
SELECT COUNT(EMP_NO)
FROM EMPLOYEE
WHERE DEPT_NO = :DNO
INTO :EMP_CNT;
SUSPEND;
END
END ;
ORG_CHART is invoked from isql as follows:
SELECT * FROM ORG_CHART;
For each department, the procedure displays the department name, the
department’s “head department” (managing department), the department
manager’s name and title, and the number of employees in the department.
ORG_CHART must be used as a select procedure to display the full organization. If
called with EXECUTE PROCEDURE, then the first time it encounters the SUSPEND
statement, the procedure terminates, returning the information for Corporate
Headquarters only.
SELECT can specify columns to retrieve from a procedure. For example, if
ORG_CHART is invoked as follows:
SELECT DEPARTMENT FROM ORG_CHART;
then only the second column, DEPARTMENT, is displayed.
Declaring input parameters
Use input parameters to pass values from an application to a procedure. Any
input parameters are given in a comma-delimited list enclosed in parentheses
immediately after the procedure name, as follows:
CREATE PROCEDURE name
(var datatype [, var datatype …])
. . .
Each input parameter declaration has two parts: a name and a datatype. The name
of the parameter must be unique within the procedure, and the datatype can be
any standard SQL datatype except BLOB and arrays of datatypes. The name of an
input parameter need not match the name of any host parameter in the calling
program.
Note No more than 1,400 input parameters can be passed to a stored procedure.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-9
C r e a t i n g p r o c e d u r e s
Declaring output parameters
Use output parameters to return values from a procedure to an application. The
RETURNS clause in the procedure header specifies a list of output parameters. The
syntax of the RETURNS clause is:
. . .
[RETURNS (var datatype [, var datatype …])]
AS
. . .
Each output parameter declaration has two parts: a name and a datatype. The
name of the parameter must be unique within the procedure, and the datatype can
be any standard SQL datatype except BLOB and arrays.
The procedure body
Everything following the AS keyword in the CREATE PROCEDURE statement forms
the procedure body. The body consists of an optional list of local variable
declarations followed by a block of statements.
A block is composed of statements in the InterBase procedure and trigger
language, bracketed by BEGIN and END. A block can itself include other blocks, so
that there can be many levels of nesting.
InterBase procedure and trigger language includes all standard InterBase SQL
statements except data definition and transaction statements, plus statements
unique to procedure and trigger language.
Features of InterBase procedure and trigger language include:
• Assignment statements, to set values of local variables and input/output
parameters.
• SELECT statements, to retrieve column values. SELECT statements must have an
INTO clause as the last clause.
• Control-flow statements, such as FOR SELECT … DO, IF … THEN, and WHILE …
DO, to perform conditional or looping tasks.
• EXECUTE PROCEDURE statements, to invoke other procedures. Recursion is
allowed.
• Comments to annotate procedure code.
• Exception statements, to return error messages to applications, and WHEN
statements to handle specific error conditions.
• SUSPEND and EXIT statements, that return control—and return values of output
parameters—to the calling application.
9-10 D a t a D e f i n i t i o n G u i d e
C r e a t i n g p r o c e d u r e s
BEGIN … END statements
Each block of statements in the procedure body starts with a BEGIN statement and
ends with an END statement. BEGIN and END are not followed by a semicolon.
Using variables
There are three types of variables that can be used in the body of a procedure:
• Input parameters, used to pass values from an application to a stored
procedure.
• Output parameters, used to pass values from a stored procedure back to the
calling application.
• Local variables, used to hold values used only within a procedure.
Any of these types of variables can be used in the body of a stored procedure
where an expression can appear. They can be assigned a literal value, or assigned a
value derived from queries or expression evaluations.
Note In SQL statements, precede variables with a colon (:) to signify that they are
variables rather than column names. In procedure and trigger language extension
statements, you need not precede variables with a colon.
Local variables
Local variables are declared and used within a stored procedure. They have no
effect outside the procedure.
Local variables must be declared at the beginning of a procedure body before they
can be used. Declare a local variable as follows:
DECLARE VARIABLE var datatype;
where var is the name of the local variable, unique within the procedure, and
datatype is the datatype, which can be any SQL datatype except BLOB or an array.
Each local variable requires a separate DECLARE VARIABLE statement, followed by
a semicolon (;).
The following header declares the local variable, ANY_SALES:
CREATE PROCEDURE DELETE_EMPLOYEE (EMP_NUM INTEGER)
AS
DECLARE VARIABLE ANY_SALES INTEGER;
BEGIN
. . .
Input parameters
Input parameters are used to pass values from an application to a procedure. They
are declared in a comma-delimited list in parentheses following the procedure
name. Once declared, they can be used in the procedure body anywhere an
expression can appear.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-11
C r e a t i n g p r o c e d u r e s
Input parameters are passed by value from the calling program to a stored
procedure. This means that if the procedure changes the value of an input
parameter, the change has effect only within the procedure. When control returns
to the calling program, the input parameter still has its original value.
The following procedure header declares two input parameters, EMP_NO and
PROJ_ID:
CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))
AS
. . .
For more information on declaring input parameters in stored procedures, see
“Declaring input parameters” on page 9-8.
Output parameters
Output parameters are used to return values from a procedure to the calling
application. Declare them in a comma-delimited list in parentheses following the
RETURNS keyword in the procedure header. Once declared, they can be used in the
procedure body anywhere an expression can appear. For example, the following
procedure header declares five output parameters, HEAD_DEPT, DEPARTMENT,
MNGR_NAME, TITLE, and EMP_CNT:
CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
If you declare output parameters in the procedure header, the procedure must
assign them values to return to the calling application. Values can be derived from
any valid expression in the procedure.
For more information on declaring output parameters in stored procedures, see
“Declaring output parameters” on page 9-9.
A procedure returns output parameter values to the calling application with a
SUSPEND statement. For more information about SUSPEND, see “Using SUSPEND,
EXIT, and END with procedures” on page 9-16.
In a SELECT statement that retrieves values from a procedure, the column names
must match the names and datatypes of the procedure’s output parameters. In an
EXECUTE PROCEDURE statement, the output parameters need not match the names
of the procedure’s output parameters, but the datatypes must match.
Using assignment statements
A procedure can assign values to variables with the syntax:
variable = expression;
where expression is any valid combination of variables, operators, and expressions,
and can include user-defined functions (UDFs) and generators.
A colon need not precede the variable name in an assignment statement. For
example, the following statement assigns a value of zero to the local variable,
ANY_SALES:
any_sales = 0;
Variables should be assigned values of the datatype that they are declared to be.
Numeric variables should be assigned numeric values, and character variables
assigned character values. InterBase provides automatic type conversion. For
example, a character variable can be assigned a numeric value, and the numeric
value is automatically converted to a string. For more information on type
conversion, see the Embedded SQL Guide.
Using SELECT statements
In a stored procedure, use the SELECT statement with an INTO clause to retrieve a
single row value from the database and assign it to a host variable. The SELECT
statement must return at most one row from the database, like a standard
singleton SELECT. The INTO clause is required and must be the last clause in the
statement.
For example, the following statement is a standard singleton SELECT statement in
an application:
EXEC SQL
SELECT SUM(BUDGET), AVG(BUDGET)
INTO :tot_budget, :avg_budget
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept;
To use this SELECT statement in a procedure, move the INTO clause to the end as
follows:
SELECT SUM(BUDGET), AVG(BUDGET)
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept
INTO :tot_budget, :avg_budget;
For a complete discussion of SELECT statement syntax, see the Language Reference.
Using FOR SELECT … DO statements
To retrieve multiple rows in a procedure, use the FOR SELECT … DO statement. The
syntax of FOR SELECT is:
FOR
<select_expr>
DO
<compound_statement>;
FOR SELECT differs from a standard SELECT as follows:
• It is a loop statement that retrieves the row specified in the select_expr and
performs the statement or block following DO for each row retrieved.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-13
C r e a t i n g p r o c e d u r e s
• The INTO clause in the select_expr is required and must come last. This syntax
allows FOR … SELECT to use the SQL UNION clause, if needed.
For example, the following statement from a procedure selects department
numbers into the local variable, RDNO, which is then used as an input parameter
to the DEPT_BUDGET procedure:
FOR SELECT DEPT_NO
FROM DEPARTMENT
WHERE HEAD_DEPT = :DNO
INTO :RDNO
DO
BEGIN
EXECUTE PROCEDURE DEPT_BUDGET :RDNO RETURNS :SUMB;
TOT = TOT + SUMB;
END
… ;
Using WHILE … DO statements
WHILE … DO is a looping statement that repeats a statement or block of statements
as long as a condition is true. The condition is tested at the start of each loop.
WHILE … DO uses the following syntax:
WHILE (<condition>) DO
<compound_statement>
<compound_statement> = {<block> | statement;}
The compound_statement is executed as long as condition remains TRUE.
A block is one or more compound statements enclosed by BEGIN and END.
For example, the following procedure uses a WHILE … DO loop to compute the sum
of all integers from one up to the input parameter, I:
CREATE PROCEDURE SUM_INT (I INTEGER) RETURNS (S INTEGER)
AS
BEGIN
s = 0;
WHILE (i > 0) DO
BEGIN
s = s + i;
i = i - 1;
END
END ;
If this procedure is called from isql with the command:
EXECUTE PROCEDURE SUM_INT 4;
9-14 D a t a D e f i n i t i o n G u i d e
C r e a t i n g p r o c e d u r e s
then the results are:
S
==========
10
Using IF … THEN … ELSE statements
The IF … THEN … ELSE statement selects alternative courses of action by testing a
specified condition. The syntax of IF … THEN … ELSE is as follows:
IF (<condition>)
THEN <compound_statement>
[ELSE <compound_statement>]
<compound_statement> = {<block> | statement;}
The condition clause is an expression that must evaluate to TRUE to execute the
statement or block following THEN. The optional ELSE clause specifies an
alternative statement or block to be executed if condition is FALSE.
The following lines of code illustrate the use of IF … THEN, assuming the variables
LINE2, FIRST, and LAST have been previously declared:
. . .
IF (FIRST IS NOT NULL)
THEN LINE2 = FIRST || ' ' || LAST;
ELSE LINE2 = LAST;
. . .
Using event alerters
To use an event alerter in a stored procedure, use the following syntax:
POST_EVENT <event_name>;
The parameter, event_name, can be either a quoted literal or string variable.
Note Variable names do not need to be—and must not be—preceded by a colon in stored
procedures except in SELECT, INSERT, UPDATE, and DELETE clauses where they
would be interpreted as column names without the colon.
When the procedure is executed, this statement notifies the event manager, which
alerts applications waiting for the named event. For example, the following
statement posts an event named “new_order”:
POST_EVENT 'new_order';
Alternatively, a variable can be used for the event name:
POST_EVENT event_name;
So, the statement can post different events, depending on the value of the string
variable, event_name.
For more information on events and event alerters, see the Embedded SQL Guide.
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-15
C r e a t i n g p r o c e d u r e s
Adding comments
Stored procedure code should be commented to aid debugging and application
development. Comments are especially important in stored procedures since they
are global to the database and can be used by many different application
developers.
Comments in stored procedure definitions are exactly like comments in standard
C code, and use the following syntax:
/* comment_text */
comment_text can be any number of lines of text. A comment can appear on the
same line as code. For example:
x = 42; /* Initialize value of x. */
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.
Note 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. Nested procedure calls can be restricted to fewer
than 1,000 levels by memory and stack limitations of the server.
The following example illustrates a recursive procedure, FACTORIAL, which
calculates factorials. The procedure calls itself recursively to calculate the factorial
Working with Stored Procedures
This chapter describes the following:
• How to create, alter, and drop procedures
• The InterBase procedure and trigger language
• How to use stored procedures
• How to create, alter, drop, and raise exceptions
• How to handle errors
Overview of stored procedures
A stored procedure is a self-contained program written in InterBase procedure
and trigger 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.
InterBase procedure and trigger language includes SQL data manipulation
statements and some powerful extensions, including IF … THEN … ELSE, WHILE …
DO, FOR SELECT … DO, exceptions, and error handling.
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
Working with procedures
With isql, you can create, alter, and drop procedures and exceptions. Each of these
operations is explained in the corresponding sections in this chapter.
There are two ways to create, alter, and drop procedures with isql:
• Interactively
• With an input file containing data definition statements
It is usually preferable to use data definition files, because they are easier to
modify and provide separate documentation of the procedure. For simple changes
to existing procedures or exceptions, the interactive interface can be convenient.
The user who creates a procedure is the owner of the procedure, and can grant the
privilege to execute the procedure to other users, triggers, and stored
procedures.
Using a data definition file
To create or alter a procedure through a data definition file, follow these steps:
1 Use a text editor to write the data definition file.
2 Save the file.
3 Process the file with isql. Use this command:
isql -input filename database_name
where filename is the name of the data definition file and database_name is the
name of the database to use. Alternatively, from within isql, you can process
the file using the command:
SQL> input filename;
If you do not specify the database on the command line or interactively, the data
definition file must include a statement to create or open a database.
The data definition file can include:
• Statements to create, alter, or drop procedures. The file can also include
statements to create, alter, or drop exceptions. Exceptions must be created
before they can be referenced in procedures.
• Any other isql statements.
Calling stored procedures
Applications can call stored procedures from SQL and DSQL. You can also use
stored procedures in isql. For more information on calling stored procedures from
applications, see the Embedded SQL Guide.
There are two types of stored procedures:
• 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.
• Executable procedures that an application can call directly with the EXECUTE
PROCEDURE statement. An executable procedure can optionally return values to
the calling program.
Both kinds of procedures are defined with CREATE PROCEDURE 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 conceivably 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. An extension to the GRANT statement assigns the
EXECUTE privilege, and an extension to the REVOKE statement eliminates the
privilege.
Stored procedures themselves sometimes need access to tables or views for which
a user does not—or should not—have privileges. For more information about
granting privileges to users and procedures, see Chapter 12, “Planning Security.”
Creating procedures
You can define a stored procedure with the CREATE PROCEDURE statement in isql.
You cannot create stored procedures in embedded SQL. A stored procedure is
composed of a header and a body.
The header contains:
• The name of the stored procedure, which must be unique among procedure,
view, and table names in the database.
• An optional list of input parameters and their datatypes that a procedure
receives from the calling program.
• If the procedure returns values to the calling program, RETURNS followed by a
list of output parameters and their datatypes.
The procedure body contains:
• An optional list of local variables and their datatypes.
• A block of statements in InterBase procedure and trigger language, bracketed
by BEGIN and END. A block can itself include other blocks, so that there can be
many levels of nesting.
CREATE PROCEDURE syntax
CREATE PROCEDURE name
[(param datatype [, param datatype …])]
[RETURNS (param datatype [, param datatype …])]
AS
<procedure_body>;
<procedure_body> = [<variable_declaration_list>]
<block>
<variable_declaration_list> =
DECLARE VARIABLE var datatype;
[DECLARE VARIABLE var datatype; …]
<block> =
BEGIN
<compound_statement>
[<compound_statement> …]
END
<compound_statement> = {<block> | statement;}
C h a p t e r 9 Wo r k i n g w i t h S t o r e d P r o c e d u r e s 9-5
C r e a t i n g p r o c e d u r e s
Procedure and trigger language
The InterBase procedure and trigger language is a complete programming
language for stored procedures and triggers. It includes:
• SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton
SELECT. Cursors are allowed.
• SQL operators and expressions, including UDFs linked with the database
server and generators.
• Powerful extensions to SQL, including assignment statements, control-flow
statements, context variables, event-posting statements, exceptions, and errorhandling
statements.
Table 9.1 Arguments of the CREATE PROCEDURE statement
Argument Description
name Name of the procedure; must be unique among procedure, table,
and view names in the database
param datatype Input parameters that the calling program uses to pass values to
the procedure
• param: Name of the input parameter, unique for variables in the
procedure
• datatype: An InterBase datatype
RETURNS
param datatype
Output parameters that the procedure uses to return values to the
calling program
• param: Name of the output parameter, unique for variables
within the procedure
• datatype: An InterBase datatype
• The procedure returns the values of output parameters when it
reaches a SUSPEND statement in the procedure body.
AS Keyword that separates the procedure header and the procedure
body
DECLARE VARIABLE
var datatype
Declares local variables used only in the procedure
• Each declaration must be preceded by DECLARE VARIABLE and
followed by a semicolon (;).
• var: Name of the local variable, unique for variables in the
procedure
statement • Any single statement in InterBase procedure and trigger
language
• Each statement except BEGIN and END must be followed by a
semicolon (;).
Although stored procedures and triggers are used in different ways and for
different purposes, they both use the procedure and trigger language. Both
triggers and stored procedures can use any statements in the procedure and
trigger language, with some exceptions:
• Context variables are unique to triggers.
• Input and output parameters, and the SUSPEND and EXIT statements, which
return values and are unique to stored procedures.
The stored procedure and trigger language does not include many of the
statement types available in DSQL or gpre. The following statement types are not
supported in triggers or stored procedures:
• Data definition language statements: CREATE, ALTER, DROP, DECLARE
EXTERNAL FUNCTION, and DECLARE FILTER
• Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK
• Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE
• CONNECT/DISCONNECT, and sending SQL statements to another database
• GRANT/REVOKE
• SET GENERATOR
• EVENT INIT/WAIT
• BEGIN/END DECLARE SECTION
• BASED ON
• WHENEVER
• DECLARE CURSOR
• OPEN
• FETCH
The following table summarizes the language extensions for stored procedures.
Table 9.2 Procedure and trigger language extensions
Statement Description
BEGIN … END Defines a block of statements that executes as one; the
BEGIN keyword starts the block, the END keyword
terminates it. Neither should be followed by a
semicolon.
variable = expression Assignment statement which assigns the value of
expression to variable, a local variable, input parameter,
or output parameter
/* comment_text */ Programmer’s comment, where comment_text can be
any number of lines of text
EXCEPTION
exception_name
Raises the named exception
Exception: A user-defined error that can be handled
with WHEN
Table 9.2 Procedure and trigger language extensions (continued)
EXECUTE PROCEDURE proc_name
[var [, var …]]
[RETURNING_VALUES var
[, var …]]
Executes stored procedure, proc_name, with the input
arguments listed following the procedure name,
returning values in the output arguments listed
following RETURNING_VALUES
Enables nested procedures and recursion
Input and output parameters must be variables defined
within the procedure.
EXIT Jumps to the final END statement in the procedure
FOR select_statement
DO compound_statement
Repeats the statement or block following DO for every
qualifying row retrieved by select_statement
select_statement: a normal SELECT statement, except that
the INTO clause is required and must come last
compound_statement Either a single statement in procedure and trigger
language or a block of statements bracketed by BEGIN
and END
IF (condition)
THEN compound_statement
[ELSE compound_statement]
Tests condition and if it is TRUE, performs the statement
or block following THEN. Otherwise, performs the
statement or block following ELSE, if present.
condition: a Boolean expression (TRUE, FALSE, or
UNKNOWN), generally two expressions as operands of
a comparison operator.
POST_EVENT event_name Posts the event, event_name.
SUSPEND In a SELECT procedure:
Suspends execution of procedure until next FETCH is
issued by the calling application
Returns output values, if any, to the calling application.
Not recommended for executable procedures.
WHILE (condition)
DO compound_statement
While condition is TRUE, keep performing
compound_statement. First condition is tested, and if it is
TRUE, then compound_statement is performed. This
sequence is repeated until condition is no longer TRUE.
WHEN
{error [, error …] | ANY}
DO compound_statement
Error-handling statement. When one of the specified
errors occurs, performs compound_statement. WHEN
statements, if present, must come at the end of a block,
just before END.
error: EXCEPTION exception_name, SQLCODE errcode or
GDSCODE number.
ANY: Handles any errors.
Syntax errors in stored procedures
InterBase generates errors during parsing if there is incorrect syntax in a CREATE
PROCEDURE statement. Error messages look similar to this:
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 4, char 9
-tmp
The line numbers are counted from the beginning of the CREATE PROCEDURE
statement, not from the beginning of the data definition file. Characters are
counted from the left, and the unknown token indicated is either the source of the
error, or immediately to the right of the source of the error. When in doubt,
examine the entire line to determine the source of the syntax error.
The procedure header
Everything before AS in the CREATE PROCEDURE statement forms the procedure
header. The header contains:
• The name of the stored procedure, which must be unique among procedure and
table names in the database.
• An optional list of input parameters and their datatypes. The procedure
receives the values of the input parameters from the calling program.
• Optionally, the RETURNS keyword followed by a list of output parameters and
their datatypes. The procedure returns the values of the output parameters to
the calling program.