The
WHILE statement repeats the execution of a statement or group of statements
while a specified condition is true.
Invocation
This statement
can be embedded in an:
- SQL procedure definition
- Compound SQL (compiled) statement
- Compound SQL (inlined) statement
The compound statements can be embedded in an SQL procedure definition,
SQL function definition, or SQL trigger definition. It is not an executable
statement and cannot be dynamically prepared.
Authorization
No privileges are required
to invoke the WHILE statement. However, the authorization ID of the
statement must hold the necessary privileges to invoke the SQL statements
and search condition that are embedded in the WHILE statement.
Syntax
>>-+--------+--WHILE--search-condition--DO--| SQL-routine-statement |--END WHILE--+-------+-><
'-label:-' '-label-'
SQL-routine-statement
.----------------------------.
V |
|--+---SQL-procedure-statement--;-+----+------------------------|
| .-------------------------------. |
| V | |
'---| SQL-function-statement |--;-+-'
Description
- label
- Specifies the label for the WHILE statement. If the beginning
label is specified, it can be specified in LEAVE and ITERATE statements.
If the ending label is specified, it must be the same as the beginning
label.
- search-condition
- Specifies a condition that is evaluated before each execution
of the loop. If the condition is true, the SQL-procedure-statements
in the loop are processed.
- SQL-procedure-statement
- Specifies the SQL statements to execute within the loop. SQL-procedure-statement is
only applicable when in the context of an SQL procedure or compound SQL (compiled) statement. See SQL-procedure-statement in "Compound
SQL (compiled)" statement.
- SQL-function-statement
- Specifies the SQL statements to execute within the loop. SQL-function-statement is
only applicable in an SQL function or a compound
SQL (inlined) statement which can be embedded in an SQL trigger, SQL
function or SQL method. See SQL-function-statement in "FOR".
Example
This
example uses a WHILE statement to iterate through FETCH and SET statements.
While the value of SQL variable
v_counter is
less than half of number of employees in the department identified
by the IN parameter
deptNumber, the WHILE
statement continues to perform the FETCH and SET statements. When
the condition is no longer true, the flow of control leaves the WHILE
statement and closes the cursor.
CREATE PROCEDURE DEPT_MEDIAN
(IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INTEGER DEFAULT 1;
DECLARE v_counter INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
WHERE DEPT = deptNumber
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM staff
WHERE DEPT = deptNumber;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
END