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

Read syntax diagramSkip visual syntax diagram
>>-+-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:
  1. A COMMIT, ROLLBACK, CONNECT, DISCONNECT, SET CONNECTION, or SET RESULT SETS statement is only allowed in an SQL procedure.
  2. 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.
  3. A SET TRANSACTION statement is only allowed in an SQL function or trigger.
  4. 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.

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.