BEGIN... END statement

Description

Groups SQL statements together.

Syntax

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

Parameters

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 company_name, CAST(
				sum(sales_order_items.quantity *
				product.unit_price) AS INTEGER) VALUE
		FROM customer
				LEFT OUTER JOIN sales_order
				LEFT OUTER JOIN sales_order_items
				LEFT OUTER JOIN product
		GROUP BY company_name ;
	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.

None.

Standards

Permissions

None

See also