ALLBASE/SQL Reference Manual (36216-90216)

146 Chapter4
Constraints, Procedures, and Rules
Using Procedures
• Error Handling in Procedures
• Using RAISE ERROR in Procedures
• Recommended Coding Practices for Procedures
Understanding Procedures
Procedures (defined either in ISQL or through applications) can include many of the
operations available inside application programs. Within a procedure, you can use local
variables, issue most SQL statements, create looping and control structures, test error
conditions, print messages, and return data or status information to the caller. You can
pass data to and from a procedure through parameters. You create a procedure with the
CREATE PROCEDURE statement and execute it using an EXECUTE PROCEDURE statement.
When it is no longer needed, you remove a procedure from the DBEnvironment with the
DROP PROCEDURE statement. You cannot execute a procedure from within another
procedure; however, a procedure can contain a statement that fires a rule that executes
another procedure. This is called chaining of rules. Refer to “Using Rules,” below.
To create a procedure, you must have RESOURCE or DBA authority. In order to invoke a
procedure, you need EXECUTE or OWNER authority for the procedure or DBA authority.
If the procedure is invoked through a rule, the rule owner needs EXECUTE or OWNER
authority for the procedure or DBA authority.
Creating Procedures
The following is a very simple example of procedure creation:
CREATE PROCEDURE ManufDB.FailureList
(Operator CHAR(20) NOT NULL,
FailureTime DATETIME NOT NULL,
BatchStamp DATETIME NOT NULL) AS
BEGIN
INSERT INTO ManufDB.TestMonitor
VALUES (:Operator, :FailureTime,
:BatchStamp);
END;
This example shows the definition of a procedure named FailureList owned by user
ManufDB. This procedure enters a row into the ManufDB.TestMonitor table when a
failure occurs during testing.
Three input parameters are declared with names and data types assigned--Operator,
FailureTime, and BatchStamp.At run time, these parameters accept actual values into the
procedure from the caller. The procedure body starts with the BEGIN keyword and
concludes with the END keyword. The procedure body consists of a single INSERT
statement that uses the parameters just as you would use host variables in an embedded
SQL program. The effect of a call to the procedure is to create a new row in a table named
ManufDB.TestMonitor containing a record of the current date and time along with the
name of the operator, and the batch stamp (unique identifier) of the batch of parts that
failed during testing.