The REPEAT statement executes a statement or group of statements until a search condition is true.
.-----------------------------. V | >>-+--------+--REPEAT----SQL-procedure-statement-- ; +----------> '-label:-' >--UNTIL--search-condition--END REPEAT--+-------+-------------->< '-label-'
Considerations for the diagnostics area: At the beginning of the first iteration of the REPEAT statement, and with every subsequent iteration, the diagnostics area is cleared.
Considerations for SQLSTATE and SQLCODE SQL variables: With each iteration of the REPEAT statement, the SQLSTATE and SQLCODE SQL variables are cleared prior to executing the first SQL-procedure-statement within the REPEAT statement. At the beginning of the first iteration of the REPEAT statement, the SQLSTATE and SQLCODE values reflect the last values that were set prior to the REPEAT statement. At the beginning of iterations 2 through n of the REPEAT statement, the SQLSTATE and SQLCODE values reflect the result of evaluating the search condition in the UNTIL clause of that REPEAT 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 REPEAT of the REPEAT statement completes, the SQLSTATE and SQLCODE values reflect the result of evaluating the search condition in the UNTIL clause of that REPEAT statement.
A REPEAT statement fetches rows from a table until the not_found condition handler is invoked.
CREATE PROCEDURE REPEAT_STMT (OUT COUNTER INTEGER) LANGUAGE SQL BEGIN DECLARE v_counter INTEGER DEFAULT 0; DECLARE v_firstnme VARCHAR(12); DECLARE v_midinit CHAR(1); DECLARE v_lastname VARCHAR(15); DECLARE at_end SMALLINT DEFAULT 0; DECLARE not_found CONDITION FOR SQLSTATE '02000'; DECLARE c1 CURSOR FOR SELECT firstnme, midinit, lastname FROM employee; DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; OPEN c1; fetch_loop: REPEAT FETCH c1 INTO v_firstnme, v_midinit, v_lastname; SET v_counter = v_counter + 1; UNTIL at_end > 0 END REPEAT fetch_loop; SET counter = v_counter; CLOSE c1; END