compound-statement

A compound statement contains a group of statements and declarations for SQL variables, cursors, and condition handlers.

Syntax

Read syntax diagramSkip visual syntax diagramlabel:1BEGINNOT ATOMICSQL-variable-declarationSQL-condition-declarationreturn-codes-declaration;DECLARE-CURSOR-statement;handler-declaration;SQL-procedure-statement;ENDlabel
Notes:
  • 1 Only one label: can be specified for each SQL-procedure-statement. If an ending label is specified for this beginning label, the labels must be the same.

SQL-variable-declaration:

Read syntax diagramSkip visual syntax diagramDECLARE,SQL-variable-namedata-typeDEFAULT NULLDEFAULTconstantRESULT_SET_LOCATOR VARYING

SQL-condition-declaration:

Read syntax diagramSkip visual syntax diagramDECLARESQL-condition-nameCONDITIONFORSQLSTATEVALUEstring-constant

return-codes-declaration:

Read syntax diagramSkip visual syntax diagramDECLARE SQLSTATECHAR(5)CHARACTER(5)DEFAULT '00000'DEFAULTstring-constantSQLCODEINTEGERINTDEFAULT 0DEFAULTinteger-constant

handler-declaration:

Read syntax diagramSkip visual syntax diagramDECLARECONTINUEEXITHANDLERFOR specific-condition-valuegeneral-condition-valueSQL-procedure-statement

specific-condition-value:

Read syntax diagramSkip visual syntax diagram,SQLSTATEVALUEstring-constantSQL-condition-name

general-condition-value:

Read syntax diagramSkip visual syntax diagramSQLEXCEPTIONSQLWARNINGNOT FOUND

Description

label
Defines the label for the code block. If the beginning label is specified, it can be used to qualify SQL variables declared in the compound statement and can also be specified on a LEAVE statement. If the ending label is specified, it must be the same as the beginning label.
NOT ATOMIC
NOT ATOMIC indicates that an error within the compound statement does not cause the compound statement to be rolled back.
SQL-variable-declaration
Declares a variable that is local to the compound statement.
SQL-variable-name
A qualified or unqualified name that designates a variable in an SQL procedure body. The unqualified form of SQL-variable-name is an SQL identifier and must not be a delimited identifier that contains lowercase letters or special characters. The qualified form is an SQL procedure statement label followed by a period (.) and an SQL identifier.

Db2 folds all SBCS SQL variable names to uppercase. SQL variable names should not be the same as column names. If an SQL statement contains an SQL variable or parameter and a column reference with the same name, Db2 interprets the name as an SQL variable or parameter name. To refer to the column, qualify the column name with the table name. Further, to avoid ambiguous variable references and to ensure compatibility with other Db2 platforms, qualify the SQL variable or parameter name with the label of the SQL procedure statement.

data-type
Specifies the data type and length of the variable. SQL variables follow the same rules for default lengths and maximum lengths as SQL procedure parameters. See CREATE PROCEDURE statement (SQL - external procedure) (deprecated) for a description of SQL data types and lengths.
DEFAULT constant or NULL
Defines the default for the SQL variable. The variable is initialized when the SQL procedure is called. If a default value is not specified, the variable is initialized to NULL.
RESULT_SET_LOCATOR VARYING
Specifies the data type for a result set locator variable.
SQL-condition-declaration
Declares a condition name and corresponding SQLSTATE value.
SQL-condition-name
Specifies the name of the condition. The condition name is an SQL identifier and must not be a delimited identifier that includes lowercase letters or special characters. SQL-condition-name must be unique within the procedure body and can be referenced only within the compound statement in which it is declared.
FOR SQLSTATE string-constant
Specifies the SQLSTATE that is associated with the condition. The string must be specified as five characters enclosed in single quotes, and cannot be '00000'.
return-codes-declaration
Declares special variables called SQLSTATE and SQLCODE that are set automatically to the value returned after processing an SQL statement. Both the SQLSTATE and SQLCODE variables can be declared only in the outermost compound statement of the SQL procedure. Assignment to these variables is not prohibited; however, assignment is ignored by exception handlers, and processing the next SQL statement replaces the assigned value.
DECLARE-CURSOR-statement
Declares a cursor. Each cursor in the procedure body must have a unique name. An OPEN statement must be specified to open the cursor, and a FETCH statement can be specified to read rows. The cursor can be referenced only from within the compound statement. For more information on declaring a cursor, see DECLARE CURSOR statement.
handler-declaration
Specifies a set of statements to execute when an exception or completion condition occurs in the compound statement. SQL-procedure-statement is the set of statements that execute when the handler receives control. See SQL-procedure-statement (external) for information on SQL-procedure-statement.

A handler is active only within the compound statement in which it is declared.

The actions that a handler can perform are:

CONTINUE
Specifies that after the condition handler is activated and completes successfully, control is returned to the SQL statement that follows the statement that raised the condition. However, if the condition is an error condition and it was encountered while evaluating a search condition, as in a CASE, IF, REPEAT or WHILE statement, control returns to the statement that follows the corresponding END CASE, END IF, END REPEAT, or END WHILE.
EXIT
After the handler is invoked successfully, control is returned to the end of the compound statement.

The conditions that can cause the handler to gain control are:

SQLSTATE string-constant
Specifies an SQLSTATE for which the handler is invoked. The SQLSTATE cannot be '00000'.
SQL-condition-name
Specifies a condition name for which the handler is invoked. The condition name must be previously defined in a condition declaration.
SQLEXCEPTION
Specifies that the handler is invoked when an SQLEXCEPTION occurs. An SQLEXCEPTION is an SQLSTATE in which the class code is a value other than '00', '01', or '02'. For more information on SQLSTATE values, see SQLSTATE values and common error codes.
SQLWARNING
Specifies that the handler is invoked when an SQLWARNING occurs. An SQLWARNING is an SQLSTATE value with a class code of '01'.
NOT FOUND
Specifies that the handler is invoked when a NOT FOUND condition occurs. NOT FOUND corresponds to an SQLSTATE value with a class code of '02'.

Notes

The order of statements in a compound statement must be:

  1. SQL variable, condition declarations, and return codes declarations
  2. Cursor declarations
  3. Handler declarations
  4. SQL procedure statements

Compound statements cannot be nested.

Unlike host variables, SQL variables are not preceded by colons when they are used in SQL statements.

The following rules apply to handlers:

  • A handler declaration that contains SQLEXCEPTION, SQLWARNING, or NOT FOUND cannot contain additional SQLSTATE or condition names.
  • Handler declarations within the same compound statement cannot contain duplicate conditions.
  • A handler declaration cannot contain the same condition code or SQLSTATE value more than once, and cannot contain an SQLSTATE value and a condition name that represent the same SQLSTATE value.
  • A handler is activated when it is the most appropriate handler for an exception or completion condition.
  • If there is no handler for an SQL error, the error is passed to the caller in the SQLCA.
  • A handler cannot be activated by an assignment statement that assigns a value to SQLSTATE.

The following rules and recommendations apply to the SQLCODE and SQLSTATE SQL variables:

  • A null value cannot be assigned to SQLSTATE or SQLCODE.
  • The SQLSTATE and SQLCODE variable values should be saved immediately to temporary variables if there is any intention to use the values. If a handler exists for SQLSTATE, this assignment must be done as the first statement to be processed in the handler to avoid having the value replaced by the next SQL procedure statement. If the condition raised by the SQL statement is handled, the value is changed by the first SQL statement contained in the handler.

Considerations for the SQLSTATE and SQLCODE SQL variables: The compound statement itself does not affect the SQLSTATE and SQLCODE SQL variables. However, SQL statements contained within the compound statement can affect the SQLSTATE and SQLCODE SQL variables. At the end of the compound statement, the SQLSTATE and SQLCODE SQL variables reflect the result of the last SQL statement executed within the compound statement that caused a change to the SQLSTATE and SQLCODE SQL variables. If the SQLSTATE and SQLCODE SQL variables were not changed within the compound statement, they contain the same values as when the compound statement was entered.

Examples

Create a procedure body with a compound statement that performs the following actions:

  • Declares SQL variables, a condition for SQLSTATE '02000', a handler for the condition, and a cursor
  • Opens the cursor, fetches a row, and closes the cursor
CREATE PROCEDURE PROC1(OUT NOROWS INT) LANGUAGE SQL
BEGIN
 DECLARE v_firstnme VARCHAR(12);
 DECLARE v_midinit CHAR(1);
 DECLARE v_lastname VARCHAR(15);
 DECLARE v_edlevel SMALLINT;
 DECLARE v_salary DECIMAL(9,2);
 DECLARE at_end INT DEFAULT 0;
 DECLARE not_found
  CONDITION FOR '02000';
 DECLARE c1 CURSOR FOR
  SELECT FIRSTNME, MIDINIT, LASTNAME,
   EDLEVEL, SALARY
  FROM EMP;
 DECLARE CONTINUE HANDLER FOR not_found SET NOROWS=1;
 OPEN c1;
 FETCH c1 INTO v_firstnme, v_midinit,
  v_lastname, v_edlevel, v_salary;
END