ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 147
Constraints, Procedures, and Rules
Using Procedures
Executing Procedures
You execute the procedure using an EXECUTE PROCEDURE statement. The EXECUTE
PROCEDURE statement can be issued directly in ISQL or in an application program, or the
EXECUTE PROCEDURE clause can appear inside a CREATE RULE statement. The following
shows an invocation of a procedure in an ISQL session:
isql=> EXECUTE PROCEDURE
> ManufDB.FailureList (USER, CURRENT_DATETIME,
> '1984-06-14 11:13:15.437');
isql=>
The following shows an invocation of the same procedure within an application program:
EXECUTE PROCEDURE
:ReturnCode = ManufDB.FailureList (:Operator,
CURRENT_DATETIME, :BatchStamp)
This example shows the use of a return status and host variables, which cannot be
employed in ISQL or with rules. For more information about using host variables and
return status with procedures, refer to the ALLBASE/SQL Advanced Application
Programming Guide chapter “Using Procedures in Application Programs.”
The next example shows an invocation of the ManufDB.FailureList procedure through a
CREATE RULE statement:
isql=> CREATE RULE AFTER INSERT TO ManufDB.TestData
> WHERE PassQty < TestQty
> EXECUTE PROCEDURE
> ManufDB.FailureList(USER, CURRENT_DATETIME, BATCHSTAMP);
isql=>
In this case, the invocation of the procedure takes place when an INSERT operation is
performed on ManufDB.TestData for a batch of parts in which there were some failures.
When executing the procedure from within a rule, you can refer to the names of columns in
the table on which the rule is triggered. More information about invoking procedures from
rules appears in the section “Techniques for Using Procedures with Rules,” later in this
chapter.
Procedures and Transaction Management
A procedure that is not executed from within a rule can execute any of the following
transaction management statements:
BEGIN WORK
COMMIT WORK
ROLLBACK WORK
ROLLBACK WORK TO SAVEPOINT
SAVEPOINT
Since there are no restrictions on the use of these statements, you must ensure that
transactions begin and end in appropriate ways. One recommended practice is to code
procedures that are atomic, that is, completely contained in a transaction which the
procedure ends with either a COMMIT or a ROLLBACK as its final statement. An alternative
recommended practice is to code procedures without any transaction management
statements at all.
Note that when you issue the EXECUTE PROCEDURE statement in an