The Compound statement defines a block of related SQL statements. In a compound block, local variables, condition names, cursors, and condition handlers can be defined.
►►──┬──────────────┬── BEGIN ──┬────────────────┬──────────────────────────────► └─ beg-label: ─┘ ├── ATOMIC ──────┤ └── NOT ATOMIC ◄─┘ ►───┬───────────────────────────────────────┬─────────────────────────────────► │ ┌──────────────────────────────────┐ │ └─ ▼ ─┬─ variable-declaration ────┬─;─┴─┘ └─ condition-declaration ───┘ ►───┬───────────────────────────────────────┬─────────────────────────────────► │ ┌──────────────────────────────────┐ │ └─ ▼ ─── cursor-declaration ────── ; ─┴─┘ ►─┬─────────────────────────────────┬─────────────────────────────────────────► │ ┌─────────────────────────────┐│ └─ ▼ ── handler-declaration ─ ; ─┴┘ ┌──────────────────────────┐ ►─── ▼ ─ procedure-statement ─;─┴── END ───────┬─────────────┬────────────────►◄ └─ end-label ─┘
Expansion of variable-declaration
┌───── , ──────┐ ►─ DECLARE ─ ▼ ── variable ─┴─ data-type ─┬─────────────────────┬─────────────►◄ └─ DEFAULT ─┬─ NULL ──┤ └─ const ─┘
Expansion of condition-declaration
►─ DECLARE ─ condition-name CONDITION FOR SQLSTATE ─┬─────────┬── const ──────►◄ └─ VALUE ─┘
Expansion of handler-declaration
┌──────────── , ────────────────────┐ ►─ DECLARE ─┬─ CONTINUE ─┬─ HANDLER FOR ─ ▼ ┬─ SQLEXCEPTION ─────────────────┬┴► ├─ EXIT ─────┤ ├─ SQLWARNING ───────────────────┤ └─ UNDO ─────┘ ├─ NOT FOUND ────────────────────┤ ├─ SQLSTATE value ─── 'sqlstate' ┤ └─ condition-name ───────────────┘ ►─── procedure-statement ──────────────────────────────────────────────────────►◄
Specifies a 1- through 32-character SQL identifier that labels the compound statement. The value must be different from any other label used in the compound statement.
Specifies that an unhandled exception raised while executing the compound statement causes a rollback of the effects of the compound statement.
Specifies that an unhandled exception raised while executing the compound statement does not cause a rollback of the effects of the compound statement. This is the default.
Defines a local variable.
Defines a name for a condition for the purposes of referencing it in other statements.
Defines a local cursor for use within the compound statement. For a description of this clause, see DECLARE CURSOR.
Defines a handler routine for SQL exception or completion conditions. A handler routine receives control when the execution of an SQL statement fails or terminates with a condition for which the handler has been defined. The three types of handlers (CONTINUE, EXIT, UNDO) and the conditions under which they are invoked are described in Parameters for Expansion of handler-declaration in this section.
Defines the SQL procedure statement that is to be executed when the handler routine is invoked. Procedure-statement may be any statement except a compound statement.
Specifies an SQL identifier that labels the end of the compound statement. If specified, a beg-label must also have been specified and both labels must be equal.
Parameters for Expansion of variable-declaration
Specifies the name of the local variable. Variable must be a 1- through 32-character name that follows the conventions for SQL identifiers. The names of all local variables declared within a compound statement must be unique.
Specifies a set of values that share processing characteristics. See Expansion of Data-type.
Specifies the initial value of the local variable.
Initializes the local variable to NULL.
Initializes the local variable to the value of const. Const must be a literal whose value is compatible for assignment to the local variable.
Note: If DEFAULT is not specified, the local variable is not initialized.
Parameters for Expansion of condition-declaration
Defines a name for a condition. This name can be used in other statements to refer to the condition.
Specifies the name to be assigned to the condition. Condition-name must be a 1- through 32-character name that follows the conventions for SQL identifiers. The names of all conditions declared within a compound statement must be unique.
Specifies an optional keyword without semantic meaning.
Specifies the value of SQLSTATE that constitutes the condition. const is a 5-character string-literal that consists of only digits (0-9) and capital alphabetic characters (A-Z). const cannot be '00000', the value of SQLSTATE for successful completion.
Parameters for Expansion of handler-declaration
After executing the handler action, a CONTINUE handler returns control to the statement following the one that caused the event. If this statement is contained in an IF, CASE, LOOP, WHILE, or REPEAT statement, control is returned to the statement following the IF, CASE, LOOP, WHILE, or REPEAT statement.
After executing the handler action, an EXIT handler returns control to the statement following the compound statement. If there is no statement following the compound statement, control is returned to the invoker of the routine.
Before executing the handler action, an UNDO handler will rollback the database changes caused by the execution of the compound statement that caused the handler to be activated. After the handler actions have been executed, control is returned to the statement following the compound statement. If there is no statement after the compound statement, control is returned to the invoker of the routine. An UNDO handler requires its defining compound statement to be ATOMIC.
Specifies that the handler is to be activated for all events except those of classes "Successful completion" (SQLSTATE = '00xxx'), "Completed with Warning" (SQLSTATE ='01xxx'), and "Completed with No Data" (SQLSTATE = '02xxx').
Specifies that the handler is to be activated for events of the class, "Completed with Warning" (SQLSTATE = '01xxx').
Specifies that the handler is to be activated for events of the class, "Completed with No Data" (SQLSTATE = '02xxx').
Specifies a value of SQLSTATE for which the handler is activated. 'Sqlstate' must be a 5-character string-literal that consists of only digits (0-9) and capital alphabetic characters (A-Z). 'Sqlstate' cannot be '00000', the value of SQLSTATE for successful completion.
Specifies the name of a condition for which the handler is activated. Condition-name must identify a condition declared in the compound statement.
Defines an SQL procedure statement to be included in the compound statement. Procedure-statement may be any statement including a compound statement. For more information, see the expansion for this syntax in CREATE FUNCTION or CREATE PROCEDURE.
Variables, Parameters, and Column Names
When ambiguity exists in referencing local variables, parameters and column names, qualification is required to resolve the ambiguity.
Note: For more information, see Expansion of Local-variable and Expansion of Routine-parameter.
Nesting of Compound Statement
A compound statement cannot contain other compound statements with the exception of handlers. A handler, which necessarily is contained in a compound statement, can have a compound statement as its procedure statement procedure-statement.
Handlers
When both a generic class handler (a handler for SQLEXCEPTION or SQLWARNING) and a specific handler cover the same event, the more specific handler is invoked when the event occurs.
Only one handler for a specific event can be defined.
Handlers cannot be defined with duplicate conditions.
If an SQL exception occurs in a compound statement for which there is no handler defined, control returns to the statement following the compound statement that caused the exception and an implicit RESIGNAL is executed. The exception is passed in the SQLSTATE. Database changes made by compound statements defined as ATOMIC will be rolled back before control returns.
Atomic Compound Statements
Compound statements defined as ATOMIC cannot contain the transaction management statements, COMMIT and ROLLBACK, or the session management statement, RELEASE.
Cursor state upon exiting from a compound statement
When execution of a compound statement ends, all cursors defined within the compound statement that are still open are automatically closed, except for returnable cursors.
Note: For more information about returnable cursors, see DECLARE CURSOR.
The procedure USER01.TCOMP01 retrieves an employee for a given EMP_ID and returns a formatted name. An exit handler for NOT FOUND handles the NOT FOUND condition. An exit handler for SQLEXCEPTION handles generic database errors.
set options command delimiter '++';
create procedure USER01.TCOMP01
( P_ID numeric(4)
, P_NAME char(30)
, RESULT varchar(30)
)
external name TCOMP01 language SQL
Label_400:
/*
** Return formatted name of employee with given EMP_ID
*/
begin not atomic
declare L_FNAME char(50);
declare L_LNAME char(50);
declare exit handler for SQLEXCEPTION
label_8888:
begin not atomic
set RESULT = 'Unexpected SQLSTATE: ' || SQLSTATE;
set P_NAME = '** Error **';
end;
declare exit handler for NOT FOUND
set RESULT = 'No employee for EMP_ID: '
|| cast(P_ID as char(4));
set RESULT = ' ';
set P_NAME = ' ';
select EMP_FNAME, EMP_LNAME into L_FNAME, L_LNAME
from DEMOEMPL.EMPLOYEE
where EMP_ID = P_ID;
set P_NAME = trim(L_FNAME) || ' ' || trim(L_LNAME);
set RESULT = 'All OK';
End label_400
++
set options command delimiter default++
commit;
call user01.TCOMP01(1003);
*+
*+ P_ID P_NAME RESULT
*+ ---- ------ ------
*+ 1003 Jim Baldwin ALL OK
call user01.TCOMP01(9);
*+
*+ P_ID P_NAME RESULT
*+ ---- ------ ------
*+ 9 NO EMPLOYEE FOR EMP_ID: 9
call user01.TCOMP01(-2000);
*+
*+ P_ID P_NAME RESULT
*+ ---- ------ ------
*+ -2000 ** ERROR ** UNEXPECTED SQLSTATE: 22005
|
Copyright © 2014 CA.
All rights reserved.
|
|