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
object specified inside the body of compound statement.
Syntax
.-NOT ATOMIC-.
>>-+--------+--BEGIN--+------------+---------------------------->
'-label:-' '-ATOMIC-----'
>--+-----------------------------------------+------------------>
| .-------------------------------------. |
| V | |
'---+-| SQL-variable-declaration |-+--;-+-'
+-| condition-declaration |----+
'-| return-codes-declaration |-'
>--+----------------------------------+------------------------->
| .------------------------------. |
| V | |
'---| statement-declaration |--;-+-'
>--+---------------------------------+-------------------------->
| .-----------------------------. |
| V | |
'---DECLARE-CURSOR-statement--;-+-'
>--+--------------------------------+--------------------------->
| .----------------------------. |
| V | |
'---| handler-declaration |--;-+-'
>--+------------------------------------+--END--+-------+------><
| .--------------------------------. | '-label-'
| V | |
'---| SQL-procedure-statement |--;-+-'
SQL-variable-declaration
.-,-----------------.
V |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL-----------------------------------------. (1)
>--+-| data-type1 |--+------------------------------------------------------+-----+--|
| +-CONSTANT NULL----------------------------------------+ |
| '-+-DEFAULT--+--+-constant---------------------------+-' |
| '-CONSTANT-' '-(--| cursor-value-constructor |--)-' |
'-RESULT_SET_LOCATOR--VARYING--------------------------------------------------'
data-type1
|--+-| built-in-type |---------------+--------------------------|
+-| anchored-variable-data-type |-+
| (2) |
+-array-type-name-----------------+
+-cursor-type-name----------------+
+-distinct-type-name--------------+
| (3) |
'-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)-' | '-FOR BIT DATA-' | |
| | '-+-VARCHAR----------------+--(integer)-' | |
| | '-+-CHARACTER-+--VARYING-' | |
| | '-CHAR------' | |
| | .-(1M)-------------. | |
| '-+-CLOB------------------------+--+------------------+-------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-)-' |
| '-CHAR------' +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1)-------. |
+-+-GRAPHIC--+-----------+-------+--------------------------------+
| | '-(integer)-' | |
| +-VARGRAPHIC--(integer)--------+ |
| | .-(1M)-------------. | |
| '-DBCLOB--+------------------+-' |
| '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+-------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------------+--------------------------------+
| +-TIME-------------------------+ |
| | .-(--6--)-------. | |
| '-TIMESTAMP--+---------------+-' |
| '-(--integer--)-' |
+-XML-------------------------------------------------------------+
+-BOOLEAN---------------------------------------------------------+
'-CURSOR----------------------------------------------------------'
anchored-variable-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
+-table-name.column-name----------------+
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-name-'
cursor-value-constructor
|--CURSOR--+-------------------------------------+--| holdability |--FOR--+-select-statement---+--|
| .-,-------------------------. | | (4) |
| V | | '-statement-name-----'
'-(----| parameter-declaration |-+--)-'
parameter-declaration
|--parameter-name--| data-type2 |-------------------------------|
data-type2
|--+-built-in-type--------------------+-------------------------|
+-| anchored-parameter-data-type |-+
'-distinct-type-name---------------'
anchored-parameter-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name----------+----|
'-table-name.column-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-'
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:
- If data-type1 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 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.
- 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-type1
- 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-variable-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 (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-typed
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. If array-type-name is
specified without a schema name, the array type is resolved by searching
the schemas in the SQL path.
- cursor-type-name
- Specifies the name of a cursor type. If cursor-type-name is
specified without a schema name, the cursor type is resolved by searching
the schemas in the SQL path.
- distinct-type-name
- Specifies the name of a distinct 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. If distinct-type-name is
specified without a schema name, the distinct type is resolved by
searching the schemas in the SQL path.
- row-type-name
- Specifies the name of a user-defined row type. The fields of the variable are the fields of the row type. If row-type-name is
specified without a schema name, the row type is resolved by searching
the schemas in the SQL path.
- 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-type1 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.
- (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-type2
- 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-parameter-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 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-typed
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 quotes, 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 system-defined 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 on
declaring a cursor, see "DECLARE CURSOR".
- 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 (SQLSTATE 428HS):
a nickname, typed table, typed view, declared temporary table, row
definition 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
The following example
illustrates a simple stand-alone compound statement that outputs the
word 'Hello':
SET SERVEROUTPUT ON;
BEGIN
CALL DBMS_OUTPUT.PUT_LINE ( 'Hello' );
END
The following example
illustrates 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
Create a procedure body 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
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
-- 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