Oracle 5.0, Reference Manual

Page: 1160 / 3232

Flow Control Statements
1140
DECLARE xid INT;
DECLARE done TINYINT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT xname, id FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH FROM cur1 INTO newname, xid;
IF done THEN LEAVE read_loop; END IF;
SELECT newname;
END LOOP;
CLOSE cur1;
END;
See also Section E.1, “Restrictions on Stored Programs” .
13.6.5. Flow Control Statements
MySQL supports the
IF
,
CASE
,
ITERATE
,
LEAVE LOOP
,
WHILE
, and
REPEAT
constructs for flow
control within stored programs. It also supports
RETURN
within stored functions.
Many of these constructs contain other statements, as indicated by the grammar specifications in the
following sections. Such constructs may be nested. For example, an
IF
statement might contain a
WHILE
loop, which itself contains a
CASE
statement.
MySQL does not support
FOR
loops.
13.6.5.1.
CASE
Syntax
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list ] ...
[ELSE statement_list ]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list ] ...
[ELSE statement_list ]
END CASE
The
CASE
statement for stored programs implements a complex conditional construct.
Note
There is also a
CASE
[883] expression, which differs from the
CASE
statement
described here. See Section 12.4, “Control Flow Functions” . The
CASE
statement cannot have an
ELSE NULL
clause, and it is terminated with
END
CASE
instead of
END
.
For the first syntax,
case_value
is an expression. This value is compared to the
when_value
expression in each
WHEN
clause until one of them is equal. When an equal
when_value
is found, the
corresponding
THEN
clause
statement_list
executes. If no
when_value
is equal, the
ELSE
clause
statement_list
executes, if there is one.
This syntax cannot be used to test for equality with
NULL
because
NULL = NULL
is false. See
Section 3.3.4.6, “Working with
NULL
Values” .
For the second syntax, each
WHEN
clause
search_condition
expression is evaluated until
one is true, at which point its corresponding
THEN
clause
statement_list
executes. If no
search_condition
is equal, the
ELSE
clause
statement_list
executes, if there is one.
If no
when_value
or
search_condition
matches the value tested and the
CASE
statement contains
no
ELSE
clause, a
Case not found for CASE statement
error results.