The REPEAT statement executes a statement or group of statements until a search condition is true.
No privileges are required to invoke the REPEAT 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 REPEAT statement.
>>-+--------+--REPEAT--| SQL-routine-statement |----------------> '-label:-' >--UNTIL--search-condition--END REPEAT--+-------+-------------->< '-label-' SQL-routine-statement .----------------------------. V | |--+---SQL-procedure-statement--;-+----+------------------------| | .-------------------------------. | | V | | '---| SQL-function-statement |--;-+-'
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