SQL Reference

WHILE Statement

The WHILE statement repeats the execution of a statement or group of statements while a specified condition is true.

Syntax

>>-+---------+--WHILE--search-condition--DO--------------------->
   '-:label--'
 
      .-------------------------------.
      V                               |
>--------SQL-procedure-statement--;---+--END WHILE-------------->
 
>-----+--------+-----------------------------------------------><
      '-label--'
 

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 statement or statements to execute within the loop.

Examples

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