BEGIN … END statement

Description

Groups SQL statements together.

Syntax

statement-label : ]
… BEGIN [ [ NOT ] ATOMIC ]
… [ local-declaration ; … ]
… statement-list
… [ EXCEPTIONexception-case … ] ]
… ENDstatement-label ]

Parameters

local-declaration:

variable-declaration | cursor-declaration | exception-declaration | temporary-table-declaration }

variable-declaration:

DECLARE variable-name data-type

exception-declaration:

DECLARE exception-name EXCEPTION FOR SQLSTATEVALUE ] string

exception-case:

WHEN exception-name [ , … ] THEN statement-list | WHEN OTHERS THEN statement-list

Examples

Example 1

The body of a procedure is a compound statement:

CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
  DECLARE err_notfound EXCEPTION FOR
    SQLSTATE '02000' ;
  DECLARE curThisCust CURSOR FOR
    SELECT CompanyName, CAST(
        sum(SalesOrderItems.Quantity *
        Products.UnitPrice) AS INTEGER) VALUE
    FROM Customers
        LEFT OUTER JOIN Salesorders
        LEFT OUTER JOIN SalesOrderItems
        LEFT OUTER JOIN Products
    GROUP BY CompanyName ;
  DECLARE ThisValue INT ;
  DECLARE ThisCompany CHAR(35) ;
  SET TopValue = 0 ;
  OPEN curThisCust ;


  CustomerLoop:
  LOOP
    FETCH NEXT curThisCust
      INTO ThisCompany, ThisValue ;
    IF SQLSTATE = err_notfound THEN
      LEAVE CustomerLoop ;
    END IF ;
    IF ThisValue > TopValue THEN
      SET TopValue = ThisValue ;
      SET TopCompany = ThisCompany ;
    END IF ;
  END LOOP CustomerLoop ;


CLOSE curThisCust ;
END

Usage

The body of a procedure or trigger is a compound statement. Compound statements can also be used in control statements within a procedure or trigger.

A compound statement allows one or more SQL statements to be grouped together and treated as a unit. A compound statement starts with BEGIN and ends with END. Immediately following BEGIN, a compound statement can have local declarations that exist only within the compound statement. A compound statement can have a local declaration for a variable, a cursor, a temporary table, or an exception. Local declarations can be referenced by any statement in that compound statement, or in any compound statement nested within it. Local declarations are not visible to other procedures that are called from within a compound statement.

If the ending statement-label is specified, it must match the beginning statement-label. The LEAVE statement can be used to resume execution at the first statement after the compound statement. The compound statement that is the body of a procedure has an implicit label that is the same as the name of the procedure or trigger.

ATOMIC clause An atomic statement is a statement executed completely or not at all. For example, an UPDATE statement that updates thousands of rows might encounter an error after updating many rows. If the statement does not complete, all changes revert back to their original state. Similarly, if you specify that the BEGIN statement is atomic, the statement is executed either in its entirety or not at all.

For a complete description of compound statements and exception handling, see Chapter 1, “Using Procedures and Batches” in the System Administration Guide: Volume 2.


Side effects

None.

Standards

Permissions

None

See also

DECLARE LOCAL TEMPORARY TABLE statement

DECLARE CURSOR statement [ESQL] [SP]

LEAVE statement

RESIGNAL statement

SIGNAL statement