ALLBASE/SQL Reference Manual
> Chapter 4 Constraints, Procedures, and RulesUsing Procedures |
||||||||||||||||||||||||||||||||||||||||||||||||||
|
Understanding ProceduresProcedures (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 ProceduresThe 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 ProceduresYou 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 ManagementA 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 SAVEPOINTSince 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 ProceduresWithin 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 DBEIn 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 SAVEPOINTAnother 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...ENDWHILEInside 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 ParametersA 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 ProceduresA 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 ProceduresThe following built-in variables can be used in error handling: Table 4-1 Built-in Variables in Procedures
Queries inside ProceduresWithin 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 SELECTA 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 CursorIf 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 ISQLWhen 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 RulesYou 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:
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 ProceduresYou 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.
Recommended Coding Practices for ProceduresThe 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:
|