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 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 or trigger has an implicit label that is the same as the name of the procedure or trigger.
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.
BEGIN, which identifies a compound statement, comprises part of optional SQL language feature P002 in SQL/2008. The form
of exception declaration supported by SQL Anywhere, namely the DECLARE EXCEPTION statement, is a vendor extension; in SQL/2008,
exceptions are specified using a handler declaration using the keywords DECLARE HANDLER.
BEGIN ... END blocks are supported by Adaptive Server Enterprise to define compound statements.
The body of a procedure or trigger is a compound statement.
CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
DECLARE err_notfound EXCEPTION FOR
DECLARE curThisCust CURSOR FOR
SELECT CompanyName, CAST(
sum( SalesOrderItems.Quantity *
Products.UnitPrice ) AS INTEGER) VALUE
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;
FETCH NEXT curThisCust
INTO ThisCompany, ThisValue;
IF SQLSTATE = err_notfound THEN
IF ThisValue > TopValue THEN
SET TopValue = ThisValue;
SET TopCompany = ThisCompany;
END LOOP CustomerLoop;
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.
DECLARE v1 INT = 5
DECLARE v2, v3 CHAR(10) = 'abc'