SQL-procedure-statement
An SQL control statement can allow multiple SQL statements to be specified within the SQL control statement. These statements are defined as SQL procedure statements.
Syntax
- 1 The statement is not allowed in an SQL-routine-body for an SQL function.
- 2 An ALTER FUNCTION statement (SQL scalar) or an ALTER PROCEDURE statement (SQL - native) with an ADD VERSION or REPLACE clause is not allowed in an SQL-routine-body.
- 3 The COMMIT and ROLLBACK (without the TO SAVEPOINT clause) statements must only be specified within the body of an SQL procedure. A procedure containing one of these statements must not be invoked directly, or indirectly, from the body of a function.
- 4 The CONNECT, RELEASE connection, and SET CONNECTION statements must only be specified within the body of an SQL procedure.
- 5 A CREATE TABLE statement must not include a column defined as a LOB or XML.
- 1 The statement is not allowed in an SQL-routine-body for an SQL function.
- 2 A FETCH statement must not specify a fetch-orientation clause, multiple-row-fetch clause, the WITH CONTINUE or the CURRENT CONTINUE clauses.
- 3 The CONNECT, RELEASE connection, and SET CONNECTION statements must only be specified within the body of an SQL procedure.
- 4 The COMMIT and ROLLBACK (without the TO SAVEPOINT clause) statements must only be specified within the body of an SQL procedure.
Description
- SQL-control-statement
- Specifies an SQL statement that provides the capability to control logic flow, declare and set variables, and handle warnings and exceptions, as defined in this section. Control statements are supported in SQL routines.
- SQL-statement
- Specifies an SQL statement. These statements are described in Statements.
Notes
Comments: Comments can be included within the body of an SQL routine. In addition to the double-dash form of comments (--), a comment can begin with /* and end with */. The following rules apply to this form of comment:
- The beginning characters /* must be adjacent and on the same line.
- The ending characters */ must be adjacent and on the same line.
- Comments can be started wherever a space is valid.
- Comments can be continued to the next line.
Detecting and processing error and warning conditions: As an SQL statement is executed, Db2 stores information about the processing of the statement in a diagnostics area (including the SQLSTATE and SQLCODE), unless otherwise noted in the description of the SQL statement. A completion condition can indicate that the SQL statement completed successfully, completed with a warning condition, or completed with a not found condition. An exception condition indicates that the SQL statement was not successful.
A condition handler can be defined to execute when an exception condition, a warning condition, or a not found condition occurs in a compound statement. The declaration of a condition handler includes the code that is executed when the condition handler is activated. When a condition other than a successful completion occurs in the processing of SQL-procedure-statement and a condition handler that can handle the condition is within scope, one such condition handler will be activated to process the condition. See compound-statement for information about defining condition handlers. The code in the condition handler can check for a warning condition, a not found condition, or an exception condition and can take the appropriate action. Use one of the following methods at the beginning of the body of a condition handler to check the condition in the diagnostics area that caused the handler to be activated.
- Issue a GET DIAGNOSTICS statement to request the information from the diagnostics area. See GET DIAGNOSTICS.
- Test the SQLSTATE and SQLCODE SQL variables.
If the condition is a warning and no handler exists for the condition, the previous two methods can be used outside of the body of a condition handler, if they are used immediately following the statement for which the condition is wanted. If the condition is an error and no handler exists for the condition, the routine terminates with the error condition.
