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
- 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.
-
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.
- 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.
- 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).
- 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).
- 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 aCREATE 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 following statements, which are not executable statements, are supported in compound SQL (compiled) statements:- ALLOCATE CURSOR
- ASSOCIATE LOCATORS
Rules
- ATOMIC compound statements cannot be nested.
- The following rules apply to handler declarations:
- A handler declaration cannot contain the same condition-name or SQLSTATE value more than once, and cannot contain an SQLSTATE value and a condition-name that represent the same SQLSTATE value.
- Where two or more condition handlers are declared in a compound
statement:
- No two handler declarations may specify the same general condition category (SQLEXCEPTION, SQLWARNING, NOT FOUND).
- No two handler declarations may specify the same specific condition, either as an SQLSTATE value or as a condition-name that represents the same value.
- A handler is activated when it is the most appropriate handler
for an exception or completion condition. The most appropriate handler
is determined based on the following considerations:
- The scope of a handler declaration H is the list of SQL-procedure-statement that follows the handler declarations contained within the compound statement in which H appears. This means that the scope of H does not include the statements contained in the body of the condition handler H, implying that a condition handler cannot handle conditions that arise inside its own body. Similarly, for any two handlers H1 and H2 declared in the same compound statement, H1 will not handle conditions arising in the body of H2, and H2 will not handle conditions arising in the body of H1.
- A handler for a specific-condition-value or a general-condition-value C declared in an inner scope takes precedence over another handler for C declared in an enclosing scope.
- When a specific handler for condition C and a general handler which would also handle C are declared in the same scope, the specific handler takes precedence over the general handler.
- When a handler for a module condition that has no associated SQLSTATE value and a handler for SQLSTATE 45000 are declared in the same scope, the handler for the module condition takes precedence over the handler for SQLSTATE 45000.
- Referencing variables or parameters of data type XML in SQL procedures after a commit or rollback operation occurs, without first assigning new values to these variables, is not supported (SQLSTATE 560CE).
- Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.
- If named parameter markers are used in a compound SQL (compiled) statement that is dynamically prepared or executed, every parameter marker name must be unique (SQLSTATE 42997).
Notes
- XML assignments: Assignment to
parameters and variables of data type XML is done by reference.
Passing parameters of data type XML in a CALL statement to an SQL procedure is done by reference. When XML values are passed by reference, any input node trees are used directly from the XML argument. This direct usage preserves all properties, including document order, the original node identities, and all parent properties.
Examples
- Example 1: A simple stand-alone compound
statement that outputs the word 'Hello':
SET SERVEROUTPUT ON; BEGIN CALL DBMS_OUTPUT.PUT_LINE ( 'Hello' ); END
- Example 2: A simple stand-alone compound
statement that counts the number of records in
staff
and outputs the result:SET SERVEROUTPUT ON; BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; SELECT COUNT(*) INTO v_numRecords FROM staff; CALL DBMS_OUTPUT.PUT_LINE (v_numRecords); END
- Example 3: Create a procedure with a compound SQL (compiled)
statement that performs the following actions:
- Declares SQL variables
- Declares a cursor to return the salary of employees in a department determined by an IN parameter. In the SELECT statement, casts the data type of the salary column from a DECIMAL into a DOUBLE.
- Declares an EXIT handler for the condition NOT FOUND (end of file)
which assigns the value '6666' to the OUT parameter
medianSalary
- Select the number of employees in the given department into the
SQL variable
numRecords
- Fetch rows from the cursor in a WHILE loop until 50% + 1 of the employees have been retrieved
- Return the median salary
CREATE PROCEDURE DEPT_MEDIAN (IN deptNumber SMALLINT, OUT medianSalary DOUBLE) LANGUAGE SQL BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; DECLARE v_counter INTEGER DEFAULT 0; DECLARE c1 CURSOR FOR SELECT CAST(salary AS DOUBLE) FROM staff WHERE DEPT = deptNumber ORDER BY salary; DECLARE EXIT HANDLER FOR NOT FOUND SET medianSalary = 6666; -- initialize OUT parameter SET medianSalary = 0; SELECT COUNT(*) INTO v_numRecords FROM staff WHERE DEPT = deptNumber; OPEN c1; WHILE v_counter < (v_numRecords / 2 + 1) DO FETCH c1 INTO medianSalary; SET v_counter = v_counter + 1; END WHILE; CLOSE c1; END
- Example 4: The following example illustrates the flow of
execution in a hypothetical case where an UNDO handler is activated
from another condition as the result of RESIGNAL:
CREATE PROCEDURE A() LANGUAGE SQL CS1: BEGIN ATOMIC DECLARE C CONDITION FOR SQLSTATE '12345'; DECLARE D CONDITION FOR SQLSTATE '23456'; DECLARE UNDO HANDLER FOR C H1: BEGIN -- Perform rollback after error, perform final cleanup, and exit -- procedure A. -- ... -- When this handler completes, execution continues after -- compound statement CS1; procedure A will terminate. END; -- Perform some work here ... CS2: BEGIN DECLARE CONTINUE HANDLER FOR D H2: BEGIN -- Perform local recovery, then forward the error -- condition to the outer handler for additional -- processing. -- ... RESIGNAL C; -- will activate UNDO handler H1; execution -- WILL NOT return here. Any local cursors -- declared in H2 and CS2 will be closed. END; -- Perform some more work here ... -- Simulate raising of condition D by some SQL statement -- in compound statement CS2: SIGNAL D; -- will activate H2 END; END