SQL-procedure-statement (SQL PL)

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

Read syntax diagramSkip visual syntax diagramSQL-control-statementALLOCATE CURSOR statementALTER DATABASE statement(1, 2)ALTER FUNCTION statement (external scalar, external table, sourced, SQL scalar, or SQL table)(2, 3)ALTER INDEX statement(1, 2)ALTER MASK statement(1, 2)ALTER PERMISSION statement(1, 2)ALTER PROCEDURE statement (external, SQL - external, or SQL - native)(2)ALTER SEQUENCE statement(1, 2)ALTER STOGROUP statement(1, 2)ALTER TABLE statement(1, 2)ALTER TABLESPACE statement(1, 2)ALTER TRIGGER statement(1, 2)ALTER TRUSTED CONTEXT statement(1, 2)ALTER VIEW statement(1, 2)ASSOCIATE LOCATORS statementCALL statementCLOSE statementCOMMENT statement(1, 3)COMMIT statement(6)CONNECT statement(7)CREATE ALIAS statement(1, 2)CREATE DATABASE statement(1, 2)CREATE FUNCTION statement (external scalar, external table, or sourced)(1, 2)CREATE GLOBAL TEMPORARY TABLE statement(1, 2)CREATE INDEX statement(1,3)CREATE PROCEDURE statement (external)(1, 2)CREATE ROLE statement(1, 2)CREATE SEQUENCE statement(1, 2)CREATE STOGROUP statement(1, 2)CREATE SYNONYM statement(1, 2)CREATE TABLE statement(9)CREATE TABLESPACE statement(1, 2)CREATE TRUSTED CONTEXT statement(1, 2)CREATE TYPE (array) statement(1, 2)CREATE TYPE (distinct) statement(1, 2)CREATE VARIABLE statement(1, 2)CREATE VIEW statement(1, 3)

Syntax (continued)

Read syntax diagramSkip visual syntax diagramDECLARE CURSOR statementDECLARE GLOBAL TEMPORARY TABLE statement(1, 2)DELETE statement(3)DROP statement(4)EXCHANGE statement(1, 2)EXECUTE statementEXECUTE IMMEDIATE statementFETCH statement(5)GET DIAGNOSTICS statementGRANT statement(1, 2)INSERT statement(3)LABEL statement(1,3)LOCK TABLE statement(1,3)MERGE statement(3)OPEN statementPREPARE statementREFRESH TABLE statement(3)RELEASE statement(7)RELEASE SAVEPOINT statementRENAME statementREVOKE statementROLLBACK statement (with TO SAVEPOINT clause)(1, 2)ROLLBACK statement (without TO SAVEPOINT clause)(6)SAVEPOINT statement(1, 3)SELECT INTO statementSET assignment-statement statementSET CONNECTION statement(7)SET special-register statement(8)TRUNCATE statement(3)UPDATE statement(3)VALUES INTO statement
Notes:

The following notes are used both in the preceding syntax diagram and sometimes also in the following table.

  1. The statement is not allowed in an SQL-routine-body for an SQL function.
  2. The statement is not allowed in an SQL-trigger-body for a trigger, and a procedure containing the statement must not be invoked directly, or indirectly, from the body of a BEFORE trigger.
  3. The statement is not allowed in an SQL-trigger-body for a BEFORE trigger, and a procedure containing the statement must not be invoked directly, or indirectly, from the body of a BEFORE trigger.
  4. The only DROP statements that are allowed are DROP TABLE, DROP VIEW, and DROP INDEX.
  5. A FETCH statement must not specify a fetch-orientation clause, multiple-row-fetch clause, the WITH CONTINUE or the CURRENT CONTINUE clauses.
  6. The COMMIT and ROLLBACK (without the TO SAVEPOINT clause) statements must only be specified within the body of an SQL procedure.
  7. The CONNECT, RELEASE connection, and SET CONNECTION statements must only be specified within the body of an SQL procedure.
  8. SET special-register statements are allowed in an SQL-trigger-body for a trigger, except for SET CURRENT PACKAGE PATH and SET CURRENT PACKAGESET.
  9. A CREATE TABLE statement must not include a column defined as a LOB or XML column.

The following table summarizes which SQL statements can be used in SQL-procedure-body, SQL-function-body, or in a SQL-trigger-body or procedure referenced in a BEFORE trigger. (That is, it corresponds to notes 1, 2, and 3 in the syntax diagram.)

Statement Allowed in SQL procedure body? Allowed in SQL function body? Allowed in SQL trigger body or procedure invoked by a BEFORE trigger?
SQL-control-statement Yes Yes Yes
ALLOCATE CURSOR Yes Yes Yes
ALTER DATABASE Yes No No
ALTER FUNCTION (external scalar, external table, sourced, SQL scalar, or SQL table) Yes3 Yes3, No
ALTER MASK Yes No No
ALTER PERMISSION Yes No No
ALTER PROCEDURE (external, SQL - external, or SQL - native) Yes Yes No
ALTER SEQUENCE Yes No No
ALTER STOGROUP Yes No No
ALTER TABLE Yes No No
ALTER TABLESPACE Yes No No
ALTER TRIGGER Yes No No
ALTER TRUSTED CONTEXT Yes No No
ALTER VIEW Yes No No
ASSOCIATE LOCATORS Yes Yes Yes
CALL Yes Yes Yes
CLOSE Yes Yes Yes
COMMENT Yes No Yes 3
COMMIT No No Yes
CONNECT Yes Yes No
CREATE ALIAS Yes No No
CREATE DATABASE Yes No No
CREATE FUNCTION Yes No No
CREATE GLOBAL TEMPORARY TABLE Yes No No
CREATE INDEX Yes No No
CREATE PROCEDURE (external) Yes No No
CREATE ROLE Yes No No
CREATE SEQUENCE Yes No No
CREATE STOGROUP Yes No No
CREATE SYNONYM Yes No No
CREATE TABLE Yes 9 Yes 9 Yes 3, 9
CREATE TABLESPACE Yes No No
CREATE TRUSTED CONTEXT Yes No No
CREATE TYPE (array) Yes No No
CREATE TYPE (distinct) Yes No No
CREATE VARIABLE Yes No No
CREATE VIEW Yes No Yes 3
DECLARE CURSOR Yes Yes Yes
DELETE Yes No Yes 3
DROP Yes No Yes
EXCHANGE Yes No No
EXECUTE Yes Yes Yes
EXECUTE IMMEDIATE Yes Yes Yes
FETCH Yes 4 Yes 4 Yes 4
GET DIAGNOSTICS Yes Yes Yes
GRANT Yes No No
INSERT Yes Yes Yes 3
LABEL Yes No Yes 3
LOCK TABLE Yes No Yes 3
MERGE Yes Yes Yes 3
OPEN Yes Yes Yes
PREPARE Yes Yes Yes
REFRESH TABLE Yes Yes Yes 3
RELEASE connection Yes No No
RELEASE SAVEPOINT Yes No No
RENAME Yes No No
REVOKE Yes No No
ROLLBACK (with TO SAVEPOINT clause) Yes No No
ROLLBACK (without TO SAVEPOINT clause) Yes Yes No
SAVEPOINT Yes No No
SELECT INTO Yes Yes Yes
SET assignment-statement Yes Yes Yes 8
SET CONNECTION Yes No No
SET special-register Yes Yes 8 Yes
TRUNCATE Yes Yes Yes 3
UPDATE Yes Yes Yes 3
VALUES INTO Yes Yes Yes

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 SQL statements in Db2 for z/OS.

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 statement.
  • 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.