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.
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.
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'