The WHILE statement repeats the execution of a statement while a specified condition is true.
>>-+--------+--WHILE--search-condition--DO----------------------> '-label:-' .-----------------------------. V | >----SQL-procedure-statement-- ; +--END WHILE--+-------+------->< '-label-'
Considerations for the diagnostics area: At the beginning of the first iteration of the WHILE statement, and with every subsequent iteration, the diagnostics area is cleared.
Considerations for SQLSTATE and SQLCODE SQL variables: With each iteration of the WHILE statement, when the first SQL-procedure-statement is executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating the search condition of that WHILE statement. If the loop is terminated with a GOTO, ITERATE, or LEAVE statement, the SQLSTATE and SQLCODE values reflect the successful completion of that statement. Otherwise, after the END WHILE of the WHILE statement completes, the SQLSTATE and SQLCODE reflect the result of evaluating the search condition of that WHILE statement.
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 DECIMAL(7,2)) LANGUAGE SQL BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; DECLARE v_counter INTEGER DEFAULT 0; DECLARE c1 CURSOR FOR SELECT salary 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