HP 3000 Manuals HP 3000 Manuals
Using Procedures
An ALLBASE/SQL procedure consists of control flow and status statements
together with SQL statements that are stored as sections in the system
catalog for later execution at the user's request or through the firing
of a rule. You can create a procedure through ISQL or through an
application program; and you can execute the procedure through ISQL,
through an application program, or through rules that are created
separately. For more information about rules, refer to the section
"Using Rules," later in this chapter.
Procedures offer the following features:
* They reduce communication between applications and the
DBEnvironment, thereby improving performance.
* They provide additional security by controlling exactly which
operations users can perform on database objects.
* Along with rules, they enable you to store business rules in the
database itself rather than coding them in application programs.
* They let you protect application programs from changes in the
database schema.
Often, procedures are built to accommodate a set of rules defined on
particular tables. Although you can use procedures without rules, rules
always operate in conjunction with procedures. When you create a rule,
the referenced procedure must already exist. So you must create
procedures first, then rules.
The following sections describe the use of procedures:
* Understanding Procedures
* Creating Procedures
* Executing Procedures
* Procedures and Transaction Management
* Using SQL Statements in Procedures
* Queries inside Procedures
* Using a Procedure Cursor in ISQL
* 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.
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 application, and if a transaction is not
already in progress, a transaction is begun. If a transaction is already
in progress at the time EXECUTE PROCEDURE is issued, and the procedure
issues either a COMMIT or a ROLLBACK statement to end the transaction,
the entire transaction, including the portion in the application, is
affected.
In all cases, it is important to document procedures carefully. Refer to
the section "Recommended Coding Practices for Procedures" later in this
chapter.
When a procedure is executed from within a rule, all the transaction
management statements are disallowed and result in an error.
Using SQL Statements in Procedures
Within a procedure, you can use most of the SQL statements that are
allowed in embedded SQL application programs, including COMMIT WORK,
ROLLBACK WORK, and ROLLBACK WORK TO SAVEPOINT. The following (including
dynamic SQL statements) are not allowed in procedures:
ADVANCE
BEGIN DECLARE SECTION
BULK statements
CLOSE USING
COMMIT WORK RELEASE
CONNECT
CREATE PROCEDURE (including inside CREATE SCHEMA)
DECLARE CURSOR for EXECUTE PROCEDURE
DESCRIBE
DISCONNECT
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
EXECUTE PROCEDURE
GENPLAN
INCLUDE
OPEN USING
PREPARE
RELEASE
ROLLBACK WORK RELEASE
SET CONNECTION
SET DML ATOMICITY
SET MULTITRANSACTION
SET SESSION
SET TRANSACTION
SQLEXPLAIN
START DBE
STOP DBE
In procedures that are invoked by execution of rules, the following
statements result in an error:
BEGIN WORK
COMMIT WORK
ROLLBACK WORK
ROLLBACK WORK TO SAVEPOINT
SAVEPOINT
Another set of statements is provided for use only within procedures:
Assignment (=)
BEGIN...END
DECLARE Variable
GOTO
IF...THEN...ELSEIF...ELSE...ENDIF
Labeled Statements
PRINT
RETURN
WHILE...DO...ENDWHILE
Inside procedures, statements are terminated with a semicolon (;).
You can define parameters for passing information into and out of a
procedure. In addition, procedures let you store data in local
variables, which are declared inside the procedure with the DECLARE
Variable statement.
Specifying Parameters.
A parameter represents a value that is passed between a procedure and an
invoking application or rule. You define formal parameters with the
CREATE PROCEDURE statement.
When executing a procedure directly, you pass input parameter values in
the EXECUTE PROCEDURE statement, and output parameter values are returned
when the procedure terminates. However, when using a procedure cursor,
input parameter values must be set before opening the cursor, and output
parameter values are returned when the CLOSE statement executes.
Within the body of the procedure, a parameter name is prefixed with a
colon (:).
You can specify up to 1023
parameters of any SQL data type except the LONG data types. Default
values and nullability may be defined just as in a CREATE TABLE
statement. If a language is specified for a parameter defined as a CHAR
or VARCHAR type, it must be either the language of the DBEnvironment or
else NATIVE-3000. The following shows a procedure with a single
parameter:
CREATE PROCEDURE Process10 (PartNumber CHAR(16)) AS
BEGIN
.
.
.
END;
If you wish to return values to a calling application program, specify
the parameter for OUTPUT in both the CREATE PROCEDURE and EXECUTE
PROCEDURE statements. If no input value is required for a parameter,
specify OUTPUT ONLY. Note that no OUTPUT option is allowed in the EXECUTE
PROCEDURE statement in ISQL nor in the EXECUTE PROCEDURE clause of the
CREATE RULE statement.
Using Local Variables in Procedures.
A local variable holds a data value within a procedure. Local variable
declarations must appear at the beginning of the main body of the
procedure using the DECLARE statement, and they must specify a data type
and size. Optionally, the DECLARE statement can
include nullability, language, and a default value. The
following are typical examples:
DECLARE LastName CHAR(40);
DECLARE SalesPrice DECIMAL(6,2);
DECLARE LowPrice, HighPrice DECIMAL(6,2) NOT NULL;
DECLARE LocationCode INTEGER NOT NULL;
DECLARE Quantity INTEGER DEFAULT 0;
Types and sizes are the same as for column definitions, except that you
cannot specify a LONG local variable. You can declare several variables
in the same DECLARE statement by separating them with a comma provided
they share the same data type, size, nullability, native language, and
default value. Within the body of the procedure, a local variable name
is prefixed with a colon (:). A local variable name cannot duplicate a
parameter name.
Local variables function in procedures much as host variables function in
application programs, but the two are not interchangeable. That is,
you cannot use host variables from the application within the body of the
procedure definition nor can you use local variables in the application.
Since the application's host variables cannot be directly accessed from
within the procedure, you must use local variables or parameters in the
INTO clause of any FETCH, REFETCH, or SELECT statement within a
procedure. Then, if necessary, you transfer data to a calling
application through output parameters. If multiple rows must be returned
to the calling application, a SELECT statement with no INTO clause should
be used in conjunction with a procedure cursor. Further information
regarding procedure cursors is found in the "Using Procedures in
Application Programs" chapter of the ALLBASE/SQL Advanced Application
Programming Guide and in this manual under related syntax statements
(ADVANCE, CLOSE, CREATE PROCEDURE, DECLARE CURSOR, DESCRIBE, EXECUTE
PROCEDURE, FETCH, OPEN).
In contrast to host variables, local variables do not use indicator
variables to handle NULL values. A local variable itself contains the
null indicator, if the variable is nullable. Declaring a local variable
to be NOT NULL makes it work like a host variable that is used without an
indicator variable.
Using Built-in Variables in Procedures.
The following built-in variables can be used in error handling:
Table 4-1. Built-in Variables in Procedures
-----------------------------------------------------------------------------------------------
| | | |
| Variable | Data Type | Description |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| ::sqlcode | INTEGER | DBERR number returned after the execution of an SQL |
| | | statement, 0 if no errors. |
| | | |
| ::sqlerrd2 | INTEGER | Number of rows processed in an SQL statement. |
| | | |
| ::sqlwarn0 | CHAR(1) | Set to "W" if an SQL warning was detected. |
| | | |
| ::sqlwarn1 | CHAR(1) | Set to "W" if a character string value was |
| | | truncated when being stored in a variable or |
| | | parameter. |
| | | |
| ::sqlwarn2 | CHAR(1) | Set to "W" if a null value was eliminated from the |
| | | argument set of an aggregate function. |
| | | |
| ::sqlwarn6 | CHAR(1) | Set to "W" if the current transaction was rolled |
| | | back. |
| | | |
| ::activexact | CHAR(1) | indicates whether a transaction is in progress |
| | | ("Y") or not ("N"). For information about |
| | | transactions, see "Managing Transactions" in the |
| | | chapter "Using ALLBASE/SQL." |
| | | |
-----------------------------------------------------------------------------------------------
The built-in variables are read-only, and are not available outside of
procedures. The first six of these have the same meaning that they have
as fields in the SQLCA in application programs. They are always prefixed
by a double colon to differentiate them from any local variables or
parameters.
Note that in procedures, sqlerrd2 returns the number of rows processed
for all host languages. However, in application programs, sqlerrd3 is
used in COBOL, Fortran, and Pascal, while sqlerrd2 is used in C.
For procedures returning multiple row result set(s), note that the
built-in variables in the procedure do not reflect the status of any
FETCH or ADVANCE statements issued by the application to manipulate a
procedure cursor. After issuing such a statement, the application should
examine the appropriate fields of the SQLCA to determine status and
handle any errors.
Queries inside Procedures
Within a procedure, you can declare parameters or local variables to
process either single row or multiple row query results. Multiple row
query results within a procedure must be processed one row at a time, by
means of a select cursor. A select cursor is a pointer indicating the
current row in a set of rows retrieved by a SELECT statement. Bulk
processing is not available for a select cursor within a procedure.
Multiple row query results for queries within a procedure can be
processed by means of a procedure cursor declared in a calling
application. A procedure cursor is a pointer used to indicate the
current row in a set of rows retrieved by a set of SELECT statements
within a procedure. When you issue an EXECUTE PROCEDURE statement in
ISQL, and the procedure contains queries with no INTO clause, ISQL uses a
procedure cursor to process the query results. Further information
regarding procedure cursors is found in the "Using Procedures in
Application Programs" chapter of the ALLBASE/SQL Advanced Application
Programming Guide and in this manual in the following section, "Using a
Procedure Cursor in ISQL," and under related syntax statements (ADVANCE,
CLOSE, CREATE PROCEDURE, DECLARE CURSOR, DESCRIBE, EXECUTE, EXECUTE
IMMEDIATE EXECUTE PROCEDURE, FETCH, OPEN).
The following sections discuss the use of a simple select, a select
cursor, and an ISQL procedure cursor.
Using a Simple SELECT.
A simple SELECT statement with an INTO clause returns only a single row.
If more than one row qualifies for the query result, only the first row
is put into the parameter or local variable specified in the INTO clause,
and a warning is issued. Example:
CREATE PROCEDURE PurchDB.DiscountPart(PartNumber CHAR(16))
AS BEGIN
DECLARE SalesPrice DECIMAL(6,2);
SELECT SalesPrice INTO :SalesPrice
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
IF ::sqlcode = 0 THEN
IF :SalesPrice > 100. THEN
:SalesPrice = :SalesPrice*.80;
INSERT INTO PurchDB.Discounts
VALUES (:PartNumber, :SalesPrice);
ENDIF;
ENDIF;
END;
The procedure inserts a row into the PurchDB.Discounts table containing
the part number and 80% of the sales price if the current price of a
given part is over $100. The parameter PartNumber supplies a value for
the predicate in the SELECT statement and later supplies a value for the
VALUES clause in the INSERT statement. The local variable :SalesPrice is
used for the single-row result of the query on the Parts table, and it is
also used in the expression in the VALUES clause of the INSERT statement.
The procedure tests if the built-in variable ::sqlcode = 0 to ensure
that the SELECT was successful before inserting data into the
PurchDB.Discounts table.
Using a Select Cursor.
If your procedure must process a set of rows one at a time, you can use a
cursor to loop through the set and perform desired operations, as in the
following:
CREATE PROCEDURE PurchDB.DiscountAll(Percentage DECIMAL(4,2))
AS BEGIN
DECLARE SalesPrice DECIMAL(6,2);
DECLARE C1 CURSOR FOR SELECT SalesPrice FROM PurchDB.Parts
FOR UPDATE OF SalesPrice;
OPEN C1;
WHILE ::sqlcode = 0 DO
FETCH C1 INTO :SalesPrice;
IF ::sqlcode = 0 THEN
IF :SalesPrice < 1000. THEN
UPDATE PurchDB.Parts
SET SalesPrice = :SalesPrice*:Percentage
WHERE CURRENT OF C1;
ELSEIF :SalesPrice >= 1000. THEN
UPDATE PurchDB.Parts
SET SalesPrice = :SalesPrice*(:Percentage - .05)
WHERE CURRENT OF C1;
ENDIF;
ENDIF;
ENDWHILE;
IF ::sqlcode = 100 THEN
PRINT 'Success';
CLOSE C1;
RETURN;
ELSE
PRINT 'Error in Fetch or Update';
CLOSE C1;
RETURN;
ENDIF;
END;
This procedure discounts the prices of all part numbers by a specified
percentage if the current sales price is less than $1000, and it
discounts prices by five percentage points for part numbers whose current
price is greater than or equal to $1000. The procedure displays a
message indicating success or failure.
The use of select cursors for multiple row query results is presented in
great detail in the ALLBASE/SQL application programming guides. Refer to
the chapter "Processing with Cursors" in the guide for the programming
language you use.
Using a Procedure Cursor in ISQL
When you issue an EXECUTE PROCEDURE statement in ISQL for a procedure
containing one or more SELECT statements with no INTO clause, ISQL uses a
procedure cursor to display the query results.
For example, create a procedure as follows:
CREATE PROCEDURE PurchDB.PartNo2 AS
BEGIN
SELECT *
FROM PurchDB.Parts
WHERE PartNumber LIKE '11%';
SELECT PartNumber, BinNumber, QtyOnHand
FROM PurchDB.Inventory
WHERE PartNumber LIKE '11%';
END;
When you execute the procedure, the following is displayed:
execute procedure purchdb.partno2;
----------------+------------------------------+------------------
PARTNUMBER |PARTNAME |SALESPRICE
----------------+------------------------------+------------------
1123-P-01 |Central Processor | 500.00
1133-P-01 |Communication Processor | 200.00
1143-P-01 |Video Processor | 180.00
1153-P-01 |Graphics Processor | 220.00
---------------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, e[nd] or n[ext] >
Entering n[ext] moves you from one SELECT statement to the next. You
would see the following:
execute procedure purchdb.partno2;
----------------+---------+---------
PARTNUMBER |BINNUMBER|QTYONHAND
----------------+---------+---------
1123-P-01 | 4003| 5
1133-P-01 | 4007| 11
1143-P-01 | 4016| 8
1153-P-01 | 4027| 5
---------------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, e[nd] or n[ext] >
Entering n[ext] when the last result set is displayed produces a message
like the following:
End of procedure result sets.
Procedure return status is 0.
isql=>
Note that although you can move back and forward through the current
result set, you cannot move back to redisplay a previous result set.
Error Handling in Procedures Not Invoked by Rules
You must provide explicit mechanisms for error handling inside
procedures. The techniques you use for this depend on whether or not the
procedure is invoked by the firing of a rule. This section describes
error handling within a procedure that is not invoked by a rule. For
information about error handling in procedures invoked by rules, see the
section "Error Handling in Procedures Invoked by Rules," below. For
information about error handling in an application that invokes a
procedure, see the section "Using Procedures in Application Programs" in
the ALLBASE/SQL Advanced Application Programming Guide.
By default, when an error occurs in an SQL statement in a procedure, the
effects of the SQL statement are undone, but the procedure continues on
to the next statement. If you want errors in SQL statements to cause an
immediate error return from the procedure, use the WHENEVER statement
with the STOP option. The syntax for the WHENEVER is as follows:
{SQLERROR } {STOP }
WHENEVER {SQLWARNING} {CONTINUE }
{NOT FOUND } {GOTO [:]Label }
{GO TO [:]Label}
The STOP option causes the current transaction to be rolled back, and the
procedure's execution is terminated. If an error occurs in evaluating
the condition in an IF or WHILE statement, or in evaluating the
expression in a parameter or variable assignment statement, the execution
of the procedure terminates, and control is returned to the caller with
SQLCODE set to the last error encountered inside the procedure.
Within the procedure, the entire message buffer is not available. That
is, SQLEXPLAIN cannot be used. The built-in variable ::sqlcode holds
only the error code from the first message in the message buffer
(guaranteed to be the most severe error).
In procedures, as elsewhere in ALLBASE/SQL, the message buffer is cleared
out only before executing an SQL statement. That is, execution of the
following do not cause the message buffer to be reset:
* Assignment
* GOTO
* IF
* PRINT
* RETURN
* WHILE
The argument of any PRINT statement is passed back to the caller in the
message buffer. When the message buffer is reset, PRINT statements are
not removed.
Runtime errors are accompanied by a
generic error message indicating, by number, which procedure statement
caused the error. All SQL statements in a procedure and all non-SQL
statements except variable declarations, ENDIF, ELSE, ENDWHILE, END, and
THEN, are numbered
consecutively from the beginning of the procedure. The following is an
example of a sequence of errors returned when an EXECUTE PROCEDURE
statement fails:
Integer divide by zero. (DBERR 2601)
Error occurred executing procedure PURCHDB.DISCOUNT statement 2. (DBERR 2235)
Error occurred during evaluation of the condition in an IF or WHILE
statement or the expression in a parameter or variable assignment.
Procedure execution terminated. (DBERR 2238)
Using RAISE ERROR in Procedures
You can use the RAISE ERROR statement to generate an error within a
procedure and make a message available to users, as in the following
example:
RAISE ERROR 7500 MESSAGE 'Error Condition';
RETURN 1;
The RAISE ERROR statement causes the message to be stored in the message
buffer, and the RETURN statement causes an immediate return from the
procedure following the error. Following the return from a procedure, an
application program can retrieve the messages from raised errors by using
the SQLEXPLAIN statement. Since SQLCODE is 0 in this case (because the
procedure executed correctly; it was an SQL statement within it that
received the error), you should execute SQLEXPLAIN in a loop that tests
SQLWARN[0], as follows:
while (sqlwarn[0]=='W')
EXEC SQL SQLEXPLAIN :SQLMessage;
However, SQLEXPLAIN cannot be used within the procedure itself. You
should document the cause of all errors generated by the RAISE ERROR
statement in a procedure so that the procedure caller can understand the
error condition.
NOTE The behavior of errors, including RAISE ERROR, in procedures called
by rules differs somewhat from that described here. Refer to
"Using RAISE ERROR in Procedures Invoked by Rules" for more
information.
Recommended Coding Practices for Procedures
The use of procedures can have indirect consequences that the procedure
writer and the procedure caller may not anticipate. Problems are most
likely to arise in the areas of transaction management, cursor
management, error handling, and DBEnvironment settings. In order to
minimize difficulty, good communication between the procedure writer and
the caller of the procedure is essential. Thus procedures should be
carefully documented as to what is expected from the calling application,
and applications should be carefully documented as to what they expect a
called procedure to do and not to do.
Within a procedure, you can use ISQL comments or comment notation for the
programming language of an application that invokes a procedure. See the
ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL or the appropriate
ALLBASE/SQL application programming guide for information about comments.
The following practices are suggested to ensure that a procedure is
always called under the same conditions and with the same expectations:
* If the procedure might execute a COMMIT or ROLLBACK, the
application should issue a COMMIT or ROLLBACK before calling the
procedure. Any cursors opened in the application with the KEEP
cursor option and subsequently committed should be closed and
committed before the application calls the procedure.
* Documentation of the calling application should clearly state the
following:
* Whether the procedure will be called with a transaction
open.
* Whether the procedure is expected to have COMMIT or
ROLLBACK statements.
* Whether the procedure is expected to be atomic.
The following practices are suggested to ensure that a procedure will
always execute as expected:
* Procedure execution should not span transaction boundaries.
Either the procedure should be treated as an atomic transaction,
that is, it should always issue a COMMIT or ROLLBACK statement
upon completion of work and before termination; or it should be
entirely contained within a transaction, that is, it should not
contain any COMMIT or ROLLBACK statements.
* If the procedure executes any COMMIT or ROLLBACK statements, it
should be treated as a transaction. This means that the last
statement accessing the DBEnvironment within the procedure should
be a COMMIT WORK or a ROLLBACK WORK statement.
* If the procedure uses any cursors, they should be closed before
termination. If the procedure opens any cursors with the KEEP
option, and subsequently executes any COMMIT statements, the
cursors should be closed and committed before termination.
* A procedure should not change the application's environment
without restoring it upon termination. The application's
environment includes settings for isolation level, constraint
checking, timeout values, and rule firing.
* Documentation of the procedure should clearly state the following:
* Whether or not a transaction should already exist at the
time of procedure execution.
* Whether any COMMIT or ROLLBACK statements will be executed
by the procedure.
* Whether the procedure modifies any environment settings.
* What types of errors are handled by the procedure and how
they are handled.
* Meanings of all possible return status values.
* Meaning of any errors returned by RAISE ERROR statements.