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 statement1ALTER DATABASE statement12ALTER FUNCTION statement (external scalar, external table, sourced, SQL scalar, or SQL table)34ALTER INDEX statement12ALTER MASK statement12ALTER PERMISSION statement12ALTER PROCEDURE statement (external, SQL - external, or SQL - native)2ALTER SEQUENCE statement12ALTER STOGROUP statement12ALTER TABLE statement12ALTER TABLESPACE statement12ALTER TRIGGER statement12ALTER TRUSTED CONTEXT statement12ALTER VIEW statement12ASSOCIATE LOCATORS statementCALL statementCLOSE statementCOMMENT statement14COMMIT statement5CONNECT statement6CREATE ALIAS statement12CREATE DATABASE statement12CREATE FUNCTION statement (external scalar, external table, or sourced)12CREATE GLOBAL TEMPORARY TABLE statement12CREATE INDEX statement14CREATE PROCEDURE statement (external)12CREATE ROLE statement12CREATE SEQUENCE statement12CREATE STOGROUP statement12CREATE TABLE statement7CREATE TABLESPACE statement12CREATE TRUSTED CONTEXT statement12CREATE TYPE (array) statement12CREATE TYPE (distinct) statement12CREATE VARIABLE statement12CREATE VIEW statement14
Syntax (continued)
Read syntax diagramSkip visual syntax diagramDECLARE CURSOR statementDECLARE GLOBAL TEMPORARY TABLE statement12DELETE statement4DROP statement18EXCHANGE statement12EXECUTE statementEXECUTE IMMEDIATE statementEXPLAIN statement1FETCH statement3GET DIAGNOSTICS statementGRANT statement12INSERT statement4LABEL statement14LOCK TABLE statement14MERGE statement4OPEN statementPREPARE statementREFRESH TABLE statement4RELEASE statement6RELEASE SAVEPOINT statementRENAME statementREVOKE statementROLLBACK statement (with TO SAVEPOINT clause)12ROLLBACK statement (without TO SAVEPOINT clause)5SAVEPOINT statement14SELECT INTO statementSET assignment-statement statementSET CONNECTION statement6SET special-register statement9TRUNCATE statement4UPDATE statement4VALUES INTO statement
Notes:
  • 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 any trigger, and a procedure containing the statement must not be invoked directly or indirectly from the body of a BEFORE trigger.
  • 3 A FETCH statement must not specify a fetch-orientation clause, multiple-row-fetch clause, the WITH CONTINUE or the CURRENT CONTINUE clauses.
  • 4 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.
  • 5 COMMIT and ROLLBACK (without the TO SAVEPOINT clause) statements must only be specified within the body of an SQL procedure.
  • 6 CONNECT, RELEASE connection, and SET CONNECTION statements must only be specified within the body of an SQL procedure.
  • 7 CREATE TABLE statements must not include LOB or XML columns.
  • 8 Only DROP TABLE, DROP VIEW, and DROP INDEX statements are allowed.
  • 9 SET special-register statements are allowed in an SQL-trigger-body for a trigger, except for SET CURRENT PACKAGE PATH and SET CURRENT PACKAGESET.

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. A check mark indicates that a statement is supported in each routine type.

Table 1. Where each SQL-statement can be issued in SQL PL routines.
Statement SQL-procedure-body? SQL-function-body? SQL-trigger-body?
SQL-control-statement ✓ ✓ ✓
ALLOCATE CURSOR ✓   ✓
ALTER DATABASE ✓    
ALTER FUNCTION (external scalar, external table, sourced, SQL scalar, or SQL table) ✓ ✓ ✓4
ALTER MASK ✓    
ALTER PERMISSION ✓    
ALTER PROCEDURE (external, SQL - external, or SQL - native) ✓ ✓  
ALTER SEQUENCE ✓    
ALTER STOGROUP ✓    
ALTER TABLE ✓    
ALTER TABLESPACE ✓    
ALTER TRIGGER ✓    
ALTER TRUSTED CONTEXT ✓    
ALTER VIEW ✓    
ASSOCIATE LOCATORS ✓ ✓ ✓
CALL ✓ ✓ ✓
CLOSE ✓ ✓ ✓
COMMENT ✓   ✓4
COMMIT ✓    
CONNECT ✓ ✓  
CREATE ALIAS ✓    
CREATE DATABASE ✓    
CREATE FUNCTION ✓    
CREATE GLOBAL TEMPORARY TABLE ✓    
CREATE INDEX ✓   ✓4
CREATE PROCEDURE (external) ✓    
CREATE ROLE ✓    
CREATE SEQUENCE ✓    
CREATE STOGROUP ✓    
CREATE TABLE ✓ 7 ✓ 7 ✓ 7
CREATE TABLESPACE ✓    
CREATE TRUSTED CONTEXT ✓    
CREATE TYPE (array) ✓    
CREATE TYPE (distinct) ✓    
CREATE VARIABLE ✓    
CREATE VIEW ✓   ✓ 4
DECLARE CURSOR ✓ ✓ ✓
DELETE ✓4   ✓4
DROP ✓8   ✓ 8
EXCHANGE ✓    
EXECUTE ✓ ✓ ✓
EXECUTE IMMEDIATE ✓ ✓ ✓
EXPLAIN ✓   ✓
FETCH ✓ 3 ✓ 3 ✓ 3
GET DIAGNOSTICS ✓ ✓ ✓
GRANT ✓   ✓ 4, 7
INSERT ✓ ✓ ✓4
LABEL ✓   ✓ 4
LOCK TABLE ✓   ✓ 4
MERGE ✓   ✓ 4
OPEN ✓ ✓ ✓
PREPARE ✓ ✓ ✓
REFRESH TABLE ✓ ✓ ✓ 4
RELEASE connection ✓    
RELEASE SAVEPOINT ✓    
RENAME ✓ ✓ ✓
REVOKE ✓ ✓ ✓
ROLLBACK (with TO SAVEPOINT clause) ✓    
ROLLBACK (without TO SAVEPOINT clause) ✓ ✓  
SAVEPOINT ✓   ✓4
SELECT INTO ✓ ✓ ✓
SET assignment-statement ✓ ✓ ✓
SET CONNECTION ✓    
SET special-register ✓ ✓ ✓ 9
TRUNCATE ✓ ✓ ✓ 4
UPDATE ✓ ✓ ✓4
VALUES INTO ✓ ✓ ✓
Notes:

The following notes are used both in the preceding syntax diagram and sometimes also in the preceding 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 any trigger, and a procedure containing the statement must not be invoked directly or indirectly from the body of a BEFORE trigger.
  3. A FETCH statement must not specify a fetch-orientation clause, multiple-row-fetch clause, the WITH CONTINUE or the CURRENT CONTINUE clauses.
  4. 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.
  5. COMMIT and ROLLBACK (without the TO SAVEPOINT clause) statements must only be specified within the body of an SQL procedure.
  6. CONNECT, RELEASE connection, and SET CONNECTION statements must only be specified within the body of an SQL procedure.
  7. CREATE TABLE statements must not include LOB or XML columns.
  8. Only DROP TABLE, DROP VIEW, and DROP INDEX statements are allowed.
  9. SET special-register statements are allowed in an SQL-trigger-body for a trigger, except for SET CURRENT PACKAGE PATH and SET CURRENT PACKAGESET.

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.