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

Read syntax diagramSkip visual syntax diagramSQL-control-statementALLOCATE CURSOR statementALTER DATABASE statement1ALTER FUNCTION statement (external scalar, external table, sourced, SQL scalar, or SQL table)12ALTER INDEX statement1ALTER MASK statement1ALTER PERMISSION statement1ALTER PROCEDURE statement (external, SQL - external, or SQL - native)12ALTER SEQUENCE statement1ALTER STOGROUP statement1ALTER TABLE statement1ALTER TABLESPACE statement1ALTER TRIGGER statement1ALTER TRUSTED CONTEXT statement1ALTER VIEW statement1ASSOCIATE LOCATORS statementCALL statementCLOSE statementCOMMENT statement1COMMIT statement3CONNECT statement4CREATE ALIAS statement1CREATE DATABASE statement1CREATE FUNCTION statement (external scalar, external table, or sourced)1CREATE GLOBAL TEMPORARY TABLE statement1CREATE INDEX statement1CREATE PROCEDURE statement (external)1CREATE ROLE statement1CREATE SEQUENCE statement1CREATE STOGROUP statement1CREATE SYNONYM statement1CREATE TABLE statement15CREATE TABLESPACE statement1CREATE TRUSTED CONTEXT statement1CREATE TYPE (array) statement1CREATE TYPE (distinct) statement1CREATE VARIABLE statement1CREATE VIEW statement1
Notes:
  • 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.
Read syntax diagramSkip visual syntax diagramDECLARE CURSOR statementDECLARE GLOBAL TEMPORARY TABLE statement1DELETE statementDROP statement1EXCHANGE statement1EXECUTE statement1EXECUTE IMMEDIATE statement1FETCH statement2GET DIAGNOSTICS statementGRANT statement1INSERT statementLABEL statement1LOCK TABLE statement1MERGE statementOPEN statementPREPARE statement1REFRESH TABLE statementRELEASE statement3RELEASE SAVEPOINT statement1RENAME statement1REVOKE statement1ROLLBACK statement (with TO SAVEPOINT clause)1ROLLBACK statement (without TO SAVEPOINT clause)4SAVEPOINT statement1SELECT INTO statementSET assignment-statement statementSET CONNECTION statement3SET special-register statementTRUNCATE statementUPDATE statementVALUES INTO statement
Notes:
  • 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.