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 n-computer. 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:

  WHENEVER (SQLERROR 
            SQLWARNING 
            NOT FOUND}{STOP 
                       CONTINUE 
                       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 ALLBASE/ISQL Reference Manual 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.