User's Guide

Part III. Using SQL Anywhere

Chapter 20. Using Procedures, Triggers, and Batches

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 "Watcom-SQL Language Reference".

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.

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 within 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".