User's Guide
   PART 1. Working with Databases
     CHAPTER 10. Using Procedures, Triggers, and Batches       

Control statements

There are a number of control statements for logical flow and decision making in the body of the procedure or trigger, or in a batch. The following is a list of control statements available.

Control statement	Syntax
Compound statements	BEGIN [ ATOMIC ] statement-list END
Conditional execution: IF	IF condition THEN statement-list ELSEIF condition THEN statement-list ELSE statement-list END IF
Conditional execution: CASE	CASE expression WHEN value THEN statement-list WHEN value THEN statement-list ELSE statement-list END CASE
Repetition: WHILE, LOOP	WHILE condition LOOP statement-list END LOOP
Repetition: FOR cursor loop	FOR statement-list END FOR
Break: LEAVE	LEAVE label
CALL	CALL procname( arg, ... )

For complete descriptions of each, see the entries in SQL Statements

  Using compound statements

A compound statement starts with the keyword BEGIN and ends with the keyword END. The body of a procedure or trigger is a compound statement. Compound statements can also be used in batches. Compound statements can be nested, and combined with other control statements to define execution flow in procedures and triggers or in batches.

A compound statement allows a set of SQL statements to be grouped together and treated as a unit. SQL statements within a compound statement should be delimited with semicolons.

A command delimiter is required after the first two SELECT statements. It is optional after the final statement in a statement list.

  Declarations in compound statements

Local declarations in a compound statement immediately follow the BEGIN keyword. These local declarations exist only within the compound statement. The following may be declared within a compound statement:

• Variables

• Cursors

• Temporary tables

• Exceptions (error identifiers)

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 called from the compound statement.

The following user-defined function illustrates local declarations of variables.

The customer table includes some Canadian customers sprinkled among those from the USA, but there is no country column. The user-defined function nationality uses the fact that the US zip code is numeric while the Canadian postal code begins with a letter to distinguish Canadian and US customers.

CREATE FUNCTION nationality( cust_id INT )
RETURNS CHAR( 20 )
BEGIN
   DECLARE natl CHAR(20);
   IF cust_id IN ( SELECT id FROM customer
               WHERE LEFT(zip,1) > '9') THEN
         SET natl = 'CDN';
   ELSE
      SET natl = 'USA';
   END IF;
   RETURN ( natl );
END

This example declares a variable natl to hold the nationality string, uses a SET statement to set a value for the variable, and returns the value of the natl string to the calling environment.

The following query lists all Canadian customers in the customer table:

SELECT *
FROM customer
WHERE nationality(id) = 'CDN'

Declarations of cursors and exceptions are discussed in later sections.

  Atomic compound statements

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 are undone. The UPDATE statement is atomic.

All noncompound SQL statements are atomic. A compound statement can be made atomic by adding the keyword ATOMIC after the BEGIN keyword.

BEGIN ATOMIC
   UPDATE employee
   SET manager_ID = 501
   WHERE emp_ID = 467;
   UPDATE employee
   SET birth_date = 'bad_data';
END

In this example, the two update statements are part of an atomic compound statement. They must either succeed or fail as one. The first update statement would succeed. The second one causes a data conversion error since the value being assigned to the birth_date column cannot be converted to a date.

The atomic compound statement fails and the effect of both UPDATE statements is undone. Even if the currently executing transaction is eventually committed, neither statement in the atomic compound statement takes effect.

COMMIT and ROLLBACK and some ROLLBACK TO SAVEPOINT statements are not permitted within an atomic compound statement (see Transactions and savepoints in procedures and triggers).

There is a case where some, but not all, of the statements within an atomic compound statement are executed. This is when an error occurs, and is handled by an exception handler within the compound statement.

     For more information, see Using exception handlers in procedures and triggers.