BEGIN statement

Specifies a compound statement.

Syntax

[ statement-label : ]
BEGIN [ [ NOT ] ATOMIC ]
   [ local-declaration; ... ]
   statement-list
   [ EXCEPTION [ exception-case ... ] ]
END [ statement-label ]

local-declaration :
variable-declaration
| cursor-declaration
| exception-declaration
| temporary-table-declaration

exception-case :
WHEN exception-name [, ... ] THEN statement-list
| WHEN OTHERS THEN statement-list

variable-declaration and exception-declaration : see the DECLARE statement

cursor-declaration : see the DECLARE CURSOR statement

temporary-table-declaration : see the DECLARE LOCAL TEMPORARY TABLE statement

Parameters

statement-label
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 or trigger has an implicit label that is the same as the name of the procedure or trigger.
ATOMIC clause
An atomic statement is a statement that is 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.
local-declaration
Immediately following the BEGIN, a compound statement can have local declarations for objects that only exist 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 of the compound statement are visible to the exception handlers for the statement. Local declarations are not visible to other procedures that are called from within a compound statement.

Remarks

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 the keyword BEGIN and ends with the keyword END.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
BEGIN, which identifies a compound statement, comprises part of optional ANSI/ISO SQL Language Feature P002.

Example

The body of a procedure or trigger 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 GROUPO.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;

The example below declares the following variables:

v1 as an INT with the initial setting of 5.
v2 and v3 as CHAR(10), both with an initial value of abc.

BEGIN
   DECLARE v1 INT = 5
   DECLARE v2, v3 CHAR(10) = 'abc'
			// ...
END