DB2 10.5 for Linux, UNIX, and Windows

Compound SQL (compiled) statement

A compound SQL (compiled) statement can contain SQL control statements and SQL statements. Compound SQL (compiled) statements can be used to implement procedural logic through a sequence of SQL statements with a local scope for variables, conditions, cursors, and handlers.

Invocation

This statement can be embedded in a trigger, SQL function, or SQL procedure; or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

For an SQL-variable-declaration that specifies a cursor-value-constructor that uses a select-statement, the privileges held by the authorization ID of the statement must include the privileges necessary to execute the select-statement. See the Authorization section in "SQL queries".

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the compound statement.

Only PUBLIC group privileges are considered for any SQL objects specified inside the body of compound statement.

Syntax

Read syntax diagramSkip visual syntax diagram
                      .-NOT ATOMIC-.   
>>-+--------+--BEGIN--+------------+---------------------------->
   '-label:-'         '-ATOMIC-----'   

>--+-----------------------------+------------------------------>
   | .-------------------------. |   
   | V                         | |   
   '---| type-declaration |--;-+-'   

>--+-----------------------------------------+------------------>
   | .-------------------------------------. |   
   | V                                     | |   
   '---+-| SQL-variable-declaration |-+--;-+-'   
       +-| condition-declaration |----+          
       '-| return-codes-declaration |-'          

>--+----------------------------------+------------------------->
   | .------------------------------. |   
   | V                              | |   
   '---| statement-declaration |--;-+-'   

>--+---------------------------------+-------------------------->
   | .-----------------------------. |   
   | V                             | |   
   '---DECLARE-CURSOR-statement--;-+-'   

>--+----------------------------------+------------------------->
   | .------------------------------. |   
   | V                              | |   
   '---| procedure-declaration |--;-+-'   

>--+--------------------------------+--------------------------->
   | .----------------------------. |   
   | V                            | |   
   '---| handler-declaration |--;-+-'   

>--+------------------------------------+--END--+-------+------><
   | .--------------------------------. |       '-label-'   
   | V                                | |                   
   '---| SQL-procedure-statement |--;-+-'                   

type-declaration

|--DECLARE--+-TYPE--type-name-+--AS--+-| array-type-definition |----+--|
            '-type-name--TYPE-'      +-| distinct-type-definition |-+   
                                     '-| row-type-definition |------'   

array-type-definition

                             .-2147483647-------.      
|--| data-type1 |--ARRAY--[--+------------------+--]------------|
                             +-integer-constant-+      
                             '-| data-type2 |---'      

data-type1

|--+-| built-in-type |------+-----------------------------------|
   +-| anchored-data-type |-+   
   '-row-type-name----------'   

built-in-type

|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
   | +-+-INTEGER-+-+                                                                      |   
   | | '-INT-----' |                                                                      |   
   | '-BIGINT------'                                                                      |   
   |                  .-(5,0)-------------------.                                         |   
   +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                                         |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                                         |   
   |   '-NUM-----'               '-,integer-'                                             |   
   |          .-(53)------.                                                               |   
   +-+-FLOAT--+-----------+--+------------------------------------------------------------+   
   | |        '-(integer)-'  |                                                            |   
   | +-REAL------------------+                                                            |   
   | |         .-PRECISION-. |                                                            |   
   | '-DOUBLE--+-----------+-'                                                            |   
   |           .-(34)-.                                                                   |   
   +-DECFLOAT--+------+-------------------------------------------------------------------+   
   |           '-(16)-'                                                                   |   
   |                    .-(1)------------------------.                                    |   
   +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+   
   | | | '-CHAR------'  '-(integer-+-------------+-)-'          |  |              (1) | | |   
   | | |                           +-OCTETS------+              |  '-FOR BIT DATA-----' | |   
   | | |                           '-CODEUNITS32-'              |                       | |   
   | | '-+-VARCHAR----------------+--(integer-+-------------+-)-'                       | |   
   | |   '-+-CHARACTER-+--VARYING-'           +-OCTETS------+                           | |   
   | |     '-CHAR------'                      '-CODEUNITS32-'                           | |   
   | |                                  .-(1M)-----------------------------.            | |   
   | '-+-CLOB------------------------+--+----------------------------------+------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-+-------------+-)-'              |   
   |     '-CHAR------'                             +-K-+ +-OCTETS------+                  |   
   |                                               +-M-+ '-CODEUNITS32-'                  |   
   |                                               '-G-'                                  |   
   |            .-(1)------------------------.                                            |   
   +-+-GRAPHIC--+----------------------------+------+-------------------------------------+   
   | |          '-(integer-+-------------+-)-'      |                                     |   
   | |                     +-CODEUNITS16-+          |                                     |   
   | |                     '-CODEUNITS32-'          |                                     |   
   | +-VARGRAPHIC--(integer-+-------------+-)-------+                                     |   
   | |                      +-CODEUNITS16-+         |                                     |   
   | |                      '-CODEUNITS32-'         |                                     |   
   | |         .-(1M)-----------------------------. |                                     |   
   | '-DBCLOB--+----------------------------------+-'                                     |   
   |           '-(integer-+---+-+-------------+-)-'                                       |   
   |                      +-K-+ +-CODEUNITS16-+                                           |   
   |                      +-M-+ '-CODEUNITS32-'                                           |   
   |                      '-G-'                                                           |   
   |                          .-(1M)-------------.                                        |   
   +-+-BLOB----------------+--+------------------+----------------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                        |   
   |                                     +-K-+                                            |   
   |                                     +-M-+                                            |   
   |                                     '-G-'                                            |   
   +-+-DATE-------------------------+-----------------------------------------------------+   
   | +-TIME-------------------------+                                                     |   
   | |            .-(--6--)-------. |                                                     |   
   | '-TIMESTAMP--+---------------+-'                                                     |   
   |              '-(--integer--)-'                                                       |   
   +-XML----------------------------------------------------------------------------------+   
   +-BOOLEAN------------------------------------------------------------------------------+   
   '-CURSOR-------------------------------------------------------------------------------'   

anchored-data-type

           .-DATA TYPE-.  .-TO-.                                              
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
                                  +-table-name.column-name----------------+   
                                  |      .-OF-.                           |   
                                  '-ROW--+----+--+-table-name-----------+-'   
                                                 +-view-name------------+     
                                                 '-cursor-variable-name-'     

data-type2

|--+-+-INTEGER-+-----------------------------------+------------|
   | '-INT-----'                                   |   
   +---+-VARCHAR----------------+----(--integer--)-+   
   |   '-+-CHARACTER-+--VARYING-'                  |   
   |     '-CHAR------'                             |   
   '-| anchored-non-row-data-type |----------------'   

anchored-non-row-data-type

           .-DATA TYPE-.  .-TO-.                               
|--ANCHOR--+-----------+--+----+--+-variable-name----------+----|
                                  '-table-name.column-name-'   

distinct-type-definition

|--| source-data-type |--WITH WEAK TYPE RULES--+----------+----->
                                               '-NOT NULL-'   

>--+----------------------------------+-------------------------|
   '-CHECK--(--| check-condition |--)-'   

source-data-type

|--+-| built-in-type |--------------+---------------------------|
   '-| anchored-non-row-data-type |-'   

row-type-definition

             .-,--------------------------.        
             V                            |        
|--ROW--+-(----field-name--| data-type3 |-+--)-+----------------|
        '-| anchored-row-data-type |-----------'   

data-type3

|--+-| built-in-type |--------------+---------------------------|
   +-| anchored-non-row-data-type |-+   
   '-distinct-type-name-------------'   

anchored-row-data-type

           .-DATA TYPE-.  .-TO-.                                              
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
                                  |      .-OF-.                           |   
                                  '-ROW--+----+--+-table-name-----------+-'   
                                                 +-view-name------------+     
                                                 '-cursor-variable-name-'     

SQL-variable-declaration

            .-,-----------------.   
            V                   |   
|--DECLARE----SQL-variable-name-+------------------------------->

                     .-DEFAULT NULL-----------------------------------------. (2)     
>--+-| data-type4 |--+------------------------------------------------------+-----+--|
   |                 +-CONSTANT NULL----------------------------------------+     |   
   |                 '-+-DEFAULT--+--+-constant---------------------------+-'     |   
   |                   '-CONSTANT-'  '-(--| cursor-value-constructor |--)-'       |   
   '-RESULT_SET_LOCATOR--VARYING--------------------------------------------------'   

data-type4

|--+-| built-in-type |------+-----------------------------------|
   +-| anchored-data-type |-+   
   |                 (3)    |   
   +-array-type-name--------+   
   +-cursor-type-name-------+   
   +-distinct-type-name-----+   
   |               (4)      |   
   '-row-type-name----------'   

cursor-value-constructor

|--CURSOR--+--------------------------------------------+--| holdability |--FOR--+-select-statement---+--|
           |    .-,--------------------------------.    |                        |                (5) |   
           |    V                                  |    |                        '-statement-name-----'   
           '-(----| cursor-parameter-declaration |-+--)-'                                                 

cursor-parameter-declaration

|--parameter-name--| data-type5 |-------------------------------|

data-type5

|--+-built-in-type------------------+---------------------------|
   +-| anchored-non-row-data-type |-+   
   '-distinct-type-name-------------'   

holdability

   .-WITHOUT HOLD-.   
|--+--------------+---------------------------------------------|
   '-WITH HOLD----'   

condition-declaration

|--DECLARE--condition-name--CONDITION--+-----------------------------------------------+--|
                                       |                  .-VALUE-.                    |   
                                       |      .-SQLSTATE--+-------+-.                  |   
                                       '-FOR--+---------------------+--string-constant-'   

statement-declaration

            .-,--------------.              
            V                |              
|--DECLARE----statement-name-+--STATEMENT-----------------------|

return-codes-declaration

|--DECLARE------------------------------------------------------>

                                 .-DEFAULT '00000'----------.     
>--+-SQLSTATE--+-CHARACTER(5)-+--+--------------------------+-+--|
   |           '-CHAR(5)------'  '-DEFAULT--string-constant-' |   
   |                       .-DEFAULT 0-----------------.      |   
   '-SQLCODE--+-INTEGER-+--+---------------------------+------'   
              '-INT-----'  '-DEFAULT--integer-constant-'          

procedure-declaration

|--DECLARE--+-PROCEDURE--procedure-name-+----------------------->
            '-procedure-name--PROCEDURE-'   

>--(--+---------------------------+--)--| SQL-procedure-body |--|
      | .-,---------------------. |                              
      | V                       | |                              
      '---parameter-declaration-+-'                              

SQL-procedure-body

|--SQL-procedure-statement--------------------------------------|

handler-declaration

|--DECLARE--+-CONTINUE-+--HANDLER--FOR-------------------------->
            +-EXIT-----+                 
            '-UNDO-----'                 

>--+-| specific-condition-value |-+--| SQL-procedure-statement |--|
   '-| general-condition-value |--'                                

specific-condition-value

   .-,----------------------------------------.   
   V             .-VALUE-.                    |   
|----+-SQLSTATE--+-------+--string-constant-+-+-----------------|
     '-condition-name-----------------------'     

general-condition-value

   .-,----------------.   
   V                  |   
|----+-SQLEXCEPTION-+-+-----------------------------------------|
     +-SQLWARNING---+     
     '-NOT FOUND----'     

SQL-procedure-statement

|--+--------+--SQL-statement------------------------------------|
   '-label:-'                  

Notes:
  1. The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
  2. If data-type4 specifies a CURSOR built-in type or cursor-type-name, only NULL or cursor-value-constructor can be specified. Only DEFAULT NULL can be explicitly specified for array-type-name or row-type-name .
  3. Only DEFAULT NULL can be explicitly specified for array-type-name.
  4. Only DEFAULT NULL can be explicitly specified for row-type-name.
  5. statement-name cannot be specified if cursor-parameter-declaration is specified.

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.
ATOMIC or NOT ATOMIC
ATOMIC indicates that if an unhandled exception condition occurs in the compound statement, all SQL statements in the compound statement will be rolled back.

NOT ATOMIC indicates that an unhandled exception condition within the compound statement does not cause the compound statement to be rolled back.

If the ATOMIC keyword is specified in a dynamically prepared compound statement or an SQL function that is not within a module, the compound statement is processed as a compound SQL (inlined) statement.

A compound statement that is used in the function body of a module table function can only be defined as NOT ATOMIC.

type-declaration
Declares a user-defined data type that is local to the compound statement.
type-name
Specifies the name of a local user-defined data type. The name cannot be the same as any other type declared within the current compound statement (SQLSTATE 42734). The unqualified type-name has the same restrictions as described in any CREATE TYPE statement (SQLSTATE 42939).
array-type-definition
Specifies the attributes of an array data type to associate with the type-name. See "CREATE TYPE (array)" for a description of the syntax elements. The row-type-name can refer to a declared row type that is previously declared and in the scope of the current compound SQL (compiled) statement. The variable-name specified in an anchored-data-type clause can refer to a local variable in the scope of the current compound SQL (compiled) statement.
distinct-type-definition
Specifies the source type and optional data type constraints of a weakly typed distinct type to associate with the type-name. See "CREATE TYPE (distinct)" for a complete description of the syntax elements. The variable-name specified in anchored-non-row-data-type clause can refer to a local variable in the scope of the current compound SQL (compiled) statement. The data type of the anchor variable-name or column-name must be a built-in data type.
row-type-definition
Specifies the fields of a row data type to associate with the type-name. See "CREATE TYPE (row)" for a complete description of the syntax elements. The variable-name specified in anchored-non-row-data-type or anchored-row-data-type clauses can refer to a local variable in the scope of the current compound SQL (compiled) statement.
SQL-variable-declaration
Declares a variable that is local to the compound statement.
SQL-variable-name
Defines the name of a local variable. All SQL variable names are converted to uppercase. The name cannot be the same as another SQL variable within the same compound statement and cannot be the same as a parameter name. An SQL variable name must not be the same as a column name. If an SQL statement contains an identifier with the same name as an SQL variable and a column reference, the identifier is interpreted as a column. If the compound statement in which the variable is declared has a label, then references to the variable can be qualified with the label. For example, variable V declared in a compound statement with a label C can be referred to as C.V.
data-type4
Specifies the data type of the variable. A structured type or reference type cannot be specified (SQLSTATE 429BB).
built-in-type
Specifies a built-in data type. For a more complete description of each built-in data type except BOOLEAN and CURSOR, which cannot be specified for a table, see "CREATE TABLE". The XML data type cannot be specified in a compound SQL (compiled) statement used in a trigger, in a function, or as a stand-alone statement (SQLSTATE 429BB). The XML data type can be specified when the compound SQL (compiled) statement is used in an SQL procedure body.
BOOLEAN
For a Boolean.
CURSOR
For a cursor.
anchored-data-type
Identifies another object used to determine the data type of the SQL variable. The data type of the anchor object has the same limitations that apply to specifying the data type directly, or in the case of a row, to creating a row type.
ANCHOR DATA TYPE TO
Indicates an anchored data type is used to specify the data type.
variable-name
Identifies an SQL variable, SQL parameter, or global variable. The data type of the referenced variable is used as the data type for SQL-variable-name.
table-name.column-name
Identifies a column name of an existing table or view. The data type of the column is used as the data type for SQL-variable-name.
ROW OF table-name or view-name
Specifies a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data type of SQL-variable-name is an unnamed row type.
ROW OF cursor-variable-name
Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following elements (SQLSTATE 428HS):
  • An SQL variable or global variable with a strongly typed cursor data type
  • An SQL variable or global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.
If the cursor type of the cursor variable is not strongly rtyped using a named row type, the data type of SQL-variable-name is an unnamed row type.
array-type-name
Specifies the name of a user-defined array type. The array data type can be a locally declared data type, a schema data type, or a module data type.
cursor-type-name
Specifies the name of a cursor type. The cursor data type can be a schema data type or a module data type.
distinct-type-name
Specifies the name of a distinct type. The distinct data type can be a schema data type or a module data type. The length, precision, and scale of the declared variable are, respectively, the length, precision, and scale of the source type of the distinct type.
row-type-name
Specifies the name of a user-defined row type. The row data type can be a locally declared data type, a schema data type or a module data type. The fields of the variable are the fields of the row type.
DEFAULT or CONSTANT
Specifies a value for the SQL variable when the compound SQL (compiled) statement is referenced. If neither is specified, the default for the SQL variable is the null value. Only DEFAULT NULL can be explicitly specified if array-type-name or row-type-name is specified.
DEFAULT
Defines the default for the SQL variable. The variable is initialized when the compound SQL (compiled) statement is referenced. The default value must be assignment-compatible with the data type of the variable.
CONSTANT
Specifies that the SQL variable has a fixed value that cannot be changed. An SQL variable that is defined using CONSTANT cannot be used as the target of any assignment operation. The fixed value must be assignment-compatible with the data type of the variable.
NULL
Specifies NULL as the default for the SQL variable.
constant
Specifies a constant as the default for the SQL variable. If data-type4 specifies a CURSOR built-in type or cursor-type-name, constant cannot be specified (SQLSTATE 42601).
cursor-value-constructor
A cursor-value-constructor specifies the select-statement that is associated with the SQL variable. The assignment of a cursor-value-constructor to a cursor variable defines the underlying cursor of that cursor variable.
(cursor-parameter-declaration, ...)
Specifies the input parameters of the cursor, including the name and the data type of each parameter. Named input parameters can be specified only if select-statement is also specified in cursor-value-constructor (SQLSTATE 428HU).
parameter-name
Names the cursor parameter for use as an SQL variable within select-statement. The name cannot be the same as any other parameter name for the cursor. Names should also be chosen to avoid any column names that could be used in select-statement, since column names are resolved before parameter names.
data-type5
Specifies the data type of the cursor parameter used within select-statement. Structured types, and reference types cannot be specified (SQLSTATE 429BB).
built-in-type
Specifies a built-in data type. For a more complete description of each built-in data type, see "CREATE TABLE". The BOOLEAN and CURSOR built-in types cannot be specified (SQLSTATE 429BB).
anchored-non-row-data-type
Identifies another object used to determine the data type of the cursor parameter. The data type of the anchor object has the same limitations that apply to specifying the data type directly.
ANCHOR DATA TYPE TO
Indicates an anchored data type is used to specify the data type.
variable-name
Identifies a local SQL variable, an SQL parameter, or a global variable. The data type of the referenced variable is used as the data type for the cursor parameter.
table-name.column-name
Identifies a column name of an existing table or view. The data type of the column is used as the data type for the cursor parameter.
distinct-type-name
Specifies the name of a distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.
holdability
Specifies whether the cursor is prevented from being closed as a consequence of a commit operation. See "DECLARE CURSOR" for more information. The default is WITHOUT HOLD.
WITHOUT HOLD
Does not prevent the cursor from being closed as a consequence of a commit operation.
WITH HOLD
Maintains resources across multiple units of work. Prevents the cursor from being closed as a consequence of a commit operation.
select-statement
Specifies the SELECT statement of the cursor. See "select-statement" for more information. If cursor-parameter-declaration is included in cursor-value-constructor, then select-statement must not include any local SQL variables or routine SQL parameters (SQLSTATE 42704).
statement-name
Specifies the prepared select-statement of the cursor. See "PREPARE" for an explanation of prepared statements. The target cursor variable must not have a data type that is a strongly rtyped user-defined cursor type (SQLSTATE 428HU). Named input parameters must not be specified in cursor-value-constructor if statement-name is specified (SQLSTATE 428HU).
RESULT_SET_LOCATOR VARYING
Specifies the data type for a result set locator variable.
condition-declaration
Declares a condition name with an optional associated SQLSTATE value.
condition-name
Specifies the name of the condition. The condition name must be unique within the compound statement in which it is declared, excluding any declarations in compound statements that are nested within that compound statement (SQLSTATE 42734). A condition name can only be referenced within the compound statement in which it is declared, including any compound statements that are nested within that compound statement (SQLSTATE 42737).
CONDITION FOR SQLSTATE VALUEstring-constant
Specifies the SQLSTATE that is associated with the condition. The string constant must be specified as five characters enclosed in single quotation marks, and the SQLSTATE class (the first two characters) must not be '00'. If this clause is not specified, the condition has no associated SQLSTATE value.
statement-declaration
Declares a list of one or more names that are local to the compound statement. Each name in statement-name must not be the same as any other statement name declared in the same compound statement.
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 only be declared in the outermost compound statement when there are nested compound SQL (compiled) statements; for example in an SQL procedure body. These variables may be declared only once per SQL procedure.
declare-cursor-statement
Declares a built-in cursor in the procedure body. Variables of user-defined cursor data types are declared using SQL-variable-declaration statements.

Each declared cursor must have a unique name within the compound statement in which it is declared, excluding any declarations in compound statements that are nested within that compound statement (SQLSTATE 42734). The cursor can be referenced only from within the compound statement in which it is declared, including any compound statements that are nested within that compound statement (SQLSTATE 34000).

Use an OPEN statement to open the cursor, and a FETCH statement to read rows using the cursor. To return result sets from the SQL procedure to the client application, the cursor must be declared using the WITH RETURN clause. The following example returns one result set to the client application:
   CREATE PROCEDURE RESULT_SET()
     LANGUAGE SQL
     RESULT SETS 1
     BEGIN
       DECLARE C1 CURSOR WITH RETURN FOR
         SELECT id, name, dept, job
           FROM staff;
       OPEN C1;
   END

Note: To process result sets, you must write your client application using one of the DB2® Call Level Interface (DB2 Call Level Interface), Open Database Connectivity (ODBC), Java™ Database Connectivity (JDBC), or embedded SQL for Java (SQLJ) application programming interfaces.

For more information about declaring a cursor, see "DECLARE CURSOR".

procedure-declaration
Declares a procedure that is local to the compound statement. The definition of a local procedure does not include the specification of any of the options possible in a "CREATE PROCEDURE (SQL)" statement. The options default as they would for a "CREATE PROCEDURE (SQL)" statement with the exception of MODIFIES SQL DATA. The data access level for the procedure is automatically determined to be the minimum level required to process the SQL procedure body.
procedure-name
Defines the names of a local procedure. The name must be specified without any qualification (SQLSTATE 42601). The procedure signature, consisting of the procedure-name and the number of declared parameters, must be unique within the current compound statement. Outer compound statements within which the current compound statement is nested cannot contain a procedure with the same name.
parameter-declaration
Specifies the parameters of the local procedure. See "CREATE PROCEDURE (SQL)" for a description of the syntax elements. The parameter data type can be a locally declared data type in the scope of the current compound statement.
SQL-procedure-body
Specifies the SQL statement that is the body of the SQL procedure. Names referenced in the SQL-procedure-body can refer to declared objects (such as declared variables, data types, and procedures) that are previously declared and in the scope of the compound statement in which the local procedure is declared.
handler-declaration
Specifies a handler, and a set of one or more SQL-procedure-statements to execute when an exception or completion condition occurs in the compound statement.SQL-procedure-statement is a statement that executes when the handler receives control.

A handler is said to be active for the duration of the execution of the set of SQL-procedure-statements that follow the set of handler-declarations within the compound statement in which the handler is declared, including any nested compound statements.

There are three types of condition handlers:

CONTINUE
After the handler is invoked successfully, control is returned to the SQL statement that follows the statement that raised the exception. If the error that raised the exception is a FOR, IF, CASE, WHILE, or REPEAT statement (but not an SQL-procedure-statement within one of these), then control returns to the statement that follows END FOR, END IF, END CASE, END WHILE, or END REPEAT.
EXIT
After the handler is invoked successfully, control is returned to the end of the compound statement that declared the handler.
UNDO
Before the handler is invoked, any SQL changes that were made in the compound statement are rolled back. After the handler is invoked successfully, control is returned to the end of the compound statement that declared the handler. If UNDO is specified, the compound statement where the handler is declared must be ATOMIC.

The conditions that cause the handler to be activated are defined in the handler-declaration as follows:

specific-condition-value
Specifies that the handler is a specific condition handler.
SQLSTATE VALUEstring-constant
Specifies an SQLSTATE for which the handler is invoked. The first two characters of the SQLSTATE value must not be '00'.
condition-name
Specifies a condition name for which the handler is invoked. The condition name must be previously defined in a condition declaration or it must identify a condition that exists at the current server.
general-condition-value
Specifies that the handler is a general condition handler.
SQLEXCEPTION
Specifies that the handler is invoked when an exception condition occurs. An exception condition is represented by an SQLSTATE value whose first two characters are not '00', '01', or '02'.
SQLWARNING
Specifies that the handler is invoked when a warning condition occurs. A warning condition is represented by an SQLSTATE value whose first two characters are '01'.
NOT FOUND
Specifies that the handler is invoked when a NOT FOUND condition occurs. A NOT FOUND condition is represented by an SQLSTATE value whose first two characters are '02'.
SQL-procedure-statement
Specifies the SQL procedure statement.
label
Specifies a label for the SQL procedure statement. The label must be unique within a list of SQL procedure statements, including any compound statements nested within the list. Note that compound statements that are not nested can use the same label. A list of SQL procedure statements is possible in a number of SQL control statements.
SQL-statement
All executable SQL statements except for:
  • ALTER
  • CONNECT
  • CREATE
  • DESCRIBE
  • DISCONNECT
  • DROP
  • FLUSH EVENT MONITOR
  • FREE LOCATOR
  • GRANT
  • REFRESH TABLE
  • RELEASE (connection only)
  • RENAME TABLE
  • RENAME TABLESPACE
  • REVOKE
  • SET CONNECTION
  • SET INTEGRITY
  • SET PASSTHRU
  • SET SERVER OPTION
  • TRANSFER OWNERSHIP
The following executable statements are not supported in stand-alone compound SQL (compiled) statements, but are supported in compound SQL (compiled) statements used within an SQL function, SQL procedure, or trigger:
  • CREATE of an index, table, or view
  • DECLARE GLOBAL TEMPORARY TABLE
  • DROP of an index, table, or view
  • GRANT
  • ROLLBACK
The ROLLBACK statement is also not supported in any nested statement invoked within the stand-alone compound SQL (compiled) statement.
The following statements, which are not executable statements, are supported in compound SQL (compiled) statements:
  • ALLOCATE CURSOR
  • ASSOCIATE LOCATORS

Rules

Notes

Examples