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

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 SQLSTATE [ VALUE ] 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 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.

For a complete description of compound statements and exception handling, see Chapter 8, “Using Procedures and Batches” in the Sybase IQ System Administration Guide.


Side effects

None.

Standards

Permissions

None

See also

DECLARE LOCAL TEMPORARY TABLE statement

DECLARE CURSOR statement [ESQL] [SP]

LEAVE statement

RESIGNAL statement

SIGNAL statement