COMPOUND STATEMENT

The compound statement (BEGIN/END) is used either in a routine or trigger, or as a separate statement, to create an environment within which variables, cursors, exception condition names and exception handlers can be declared.

 

Usage

Embedded, Interactive, Module, Procedural.

Description

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.

Compound statements can be nested and the optional label value can be used to qualify the names of objects declared within the compound statement. The label value can also be used in conjunction with the LEAVE statement to control the execution flow by exiting from the compound statement.

A compound statement can be defined as atomic by specifying ATOMIC next to the BEGIN keyword.

When a compound statement is defined as atomic, an ‘atomic execution context’ becomes active while it, or any invoked routine, is executing. While an atomic execution context is active it is not possible to explicitly terminate a transaction, i.e. the statements START, COMMIT or ROLLBACK are not allowed. Within an atomic compound statement it is possible to declare an undo handler for exception handling. If an undo handler is activated due to an exception, the handler will undo any insert, delete or update operations done within the atomic execution context. If there is no appropriate undo handler found the exception handling will be the same as in a non-atomic context, only the operations performed by the statement causing the exception will be undone.

Restrictions

If ATOMIC is specified, the ROLLBACK and COMMIT statements must not be used in the compound statement.

A compound statement which contains a declaration of an UNDO exception handler must be ATOMIC.

Notes

A compound statement without an ATOMIC or NOT ATOMIC specification is assumed to be NOT ATOMIC.

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.

If the LEAVE statement is to be used to exit the compound statement, the label at the beginning must be specified.

Example

CREATE PROCEDURE exproc(IN P_SIRE VARCHAR(30)) MODIFIES SQL DATA

S0: BEGIN

    ...

    S1: BEGIN

        DECLARE EOF BOOLEAN DEFAULT FALSE;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = TRUE;

        DECLARE HORSES CURSOR FOR

            SELECT *

            FROM HORSES

            WHERE SIRE = P_SIRE;

        DECLARE HORSE AS (HORSES);

        L1: LOOP

            FETCH FROM HORSES INTO HORSE;

            IF  EOF THEN

                LEAVLE L1;

            END IF;

--

-- atomic compound to ensure that both or none of the DML operations are done

--

            BEGIN ATOMIC

                DECLARE UNDO HANDLER FOR SQLEXCEPTION BEGIN END;

                UPDATE HORSE_PEOPLE ...;

                UPDATE HORSE_EVENTS ...;

            END;

        END LOOP;

    END S1;

END S0;

Standard Compliance