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
.-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:
- 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).
- 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 .
- Only DEFAULT NULL can be explicitly specified for array-type-name.
- Only DEFAULT NULL can be explicitly specified for row-type-name.
- 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
- 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.
If an exception condition occurs for which there is no appropriate
handler, the SQL procedure containing the failing statement is terminated
with an unhandled exception condition. If a completion condition occurs
for which there is no appropriate handler, execution continues with
the next SQL statement.
- 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