SQL-procedure-statement

An SQL control statement may allow multiple SQL statements to be specified within the SQL control statement. These statements are defined as SQL procedure statements.

Syntax


>>-+-SQL-control-statement--------------------------+----------><
   +-ALLOCATE CURSOR-statement----------------------+   
   +-ALLOCATE DESCRIPTOR-statement------------------+   
   +-ALTER FUNCTION-statement--(2)------------------+   
   +-ALTER PROCEDURE-statement--(2)-----------------+   
   +-ALTER SEQUENCE-statement-----------------------+   
   +-ALTER TABLE-statement--------------------------+   
   +-ASSOCIATE LOCATORS-statement-------------------+   
   +-CLOSE-statement--------------------------------+   
   +-COMMENT-statement------------------------------+   
   +-COMMIT-statement--(1)--------------------------+   
   +-CONNECT-statement--(1)-------------------------+   
   +-CREATE ALIAS-statement-------------------------+   
   +-CREATE FUNCTION (External Scalar)-statement----+   
   +-CREATE FUNCTION (External Table)-statement-----+   
   +-CREATE FUNCTION (Sourced)-statement------------+   
   +-CREATE INDEX-statement-------------------------+   
   +-CREATE PROCEDURE (External)-statement----------+   
   +-CREATE SCHEMA-statement------------------------+   
   +-CREATE SEQUENCE-statement----------------------+   
   +-CREATE TABLE-statement-------------------------+   
   +-CREATE TYPE-statement--------------------------+   
   +-CREATE VIEW-statement--------------------------+   
   +-DEALLOCATE DESCRIPTOR-statement----------------+   
   +-DECLARE GLOBAL TEMPORARY TABLE-statement-------+   
   +-DELETE-statement-------------------------------+   
   +-DESCRIBE-statement-----------------------------+   
   +-DESCRIBE CURSOR-statement----------------------+   
   +-DESCRIBE INPUT-statement-----------------------+   
   +-DESCRIBE PROCEDURE-statement-------------------+   
   +-DESCRIBE TABLE-statement-----------------------+   
   +-DISCONNECT-statement--(1)----------------------+   
   +-DROP-statement---------------------------------+   
   +-EXECUTE-statement------------------------------+   
   +-EXECUTE IMMEDIATE-statement--------------------+   
   +-FETCH-statement--------------------------------+   
   +-GET DESCRIPTOR-statement-----------------------+   
   +-GRANT-statement--------------------------------+   
   +-INSERT-statement-------------------------------+   
   +-LABEL-statement--------------------------------+   
   +-LOCK TABLE-statement---------------------------+   
   +-MERGE-statement--------------------------------+   
   +-OPEN-statement---------------------------------+   
   +-PREPARE-statement------------------------------+   
   +-REFRESH TABLE-statement------------------------+   
   +-RELEASE-statement------------------------------+   
   +-RELEASE SAVEPOINT-statement--------------------+   
   +-RENAME-statement-------------------------------+   
   +-REVOKE-statement-------------------------------+   
   +-ROLLBACK-statement--(1)------------------------+   
   +-SAVEPOINT-statement----------------------------+   
   +-SELECT INTO-statement--------------------------+   
   +-SET CONNECTION-statement--(1)------------------+   
   +-SET CURRENT DEBUG MODE-statement---------------+   
   +-SET CURRENT DECFLOAT ROUNDING MODE-statement---+   
   +-SET CURRENT DEGREE-statement-------------------+   
   +-SET CURRENT IMPLICIT XMLPARSE OPTION-statement-+   
   +-SET DESCRIPTOR-statement-----------------------+   
   +-SET ENCRYPTION PASSWORD-statement--------------+   
   +-SET PATH-statement-----------------------------+   
   +-SET RESULT SETS-statement--(1)-----------------+   
   +-SET SCHEMA-statement---------------------------+   
   +-SET TRANSACTION-statement--(3)-----------------+   
   +-SET transition-variable-statement--(4)---------+   
   +-UPDATE-statement-------------------------------+   
   '-VALUES INTO-statement--------------------------'

Notes:

A COMMIT, ROLLBACK, CONNECT, DISCONNECT, SET CONNECTION, or SET RESULT SETS statement is only allowed in an SQL procedure.
An ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL Scalar), or ALTER FUNCTION (SQL Table) statement with a REPLACE keyword is not allowed in an SQL-routine-body.
A SET TRANSACTION statement is only allowed in an SQL function or trigger.
A SET transition-variable-statement is only allowed in a trigger. A fullselect and VALUES-statement can also be specified in a trigger.

Notes

Comments: Comments can be included within the body of an SQL procedure. 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 a 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, the database manager 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 indicates 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 in a compound statement to execute when an exception condition, a warning condition, or a not found condition occurs. The declaration of a condition handler includes the code that is to be executed when the condition handler is activated. When a condition other than a successful completion occurs in the processing of SQL-procedure-statement, if a condition handler that could 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, not found condition, or exception condition and 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 condition information. See GET DIAGNOSTICS statement.
Test the SQL variables SQLSTATE and SQLCODE.

If the condition is a warning and there is not a handler for the condition, the above two methods can also be used outside of the body of a condition handler immediately following the statement for which the condition is wanted. If the condition is an error and there is not a handler for the condition, the routine or trigger terminates with the error condition.