The compound statement is used in a routine or trigger to create an environment within which variables, cursors, exception condition names and exception handlers can be declared.
A compound statement can be defined as atomic by specifying
ATOMICnext to the
When a compound statement is defined as atomic, an `atomic execution context' becomes active while it, or any sub-query contained in a statement within it, is executing.
While an atomic execution context is active:
- It is not possible to explicitly terminate a transaction.
- If an SQL statement fails to execute successfully,
RESIGNALis effectively executed and any changes already successfully made can be potentially committed, depending on the error handling in effect.
The procedural SQL statements in a compound statement are executed in sequence whenever the compound statement is executed.
The compound statement may be used wherever a single procedural SQL statement is permitted. Thus, it provides a mechanism for executing a sequence of statements in places where the syntax rules permit only a single statement to be specified.
For a list of declarative statements, see Declarative Statements.
For a list of procedural SQL statements, see Procedural SQL Statements.
Compound statements can be nested and the optional
labelvalue can be used to qualify the names of objects declared within the compound statement.
labelvalue can also be used in conjunction with the
LEAVEstatement to control the execution flow by exiting from the compound statement.
ATOMICis specified, the
COMMITstatements must not be used in the compound statement.
A compound statement which contains a declaration of an
UNDOexception handler must be
A compound statement without an
NOT ATOMICspecification is assumed to be
The value of label must be the same at both ends of the compound statement.
If label is specified at the end of the compound statement it must also be specified at the beginning.
LEAVEstatement is to be used to exit the compound statement, the label at the beginning must be specified.
ExampleCREATE PROCEDURE exproc (INOUT Y INTEGER) CONTAINS SQL S0: BEGIN ... S1: BEGIN IF Y < 0 THEN SET Y = 0; LEAVE S0; END IF; ... END S1; ... END S0;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40