REPEAT loop iterates until a specified condition is true. A
REPEAT loop tests the condition at
the end of the loop. This means that the body of a
REPEAT loop always executes at least once.
For more information on loops, see Working with Loops.
REPEAT <statement>; [ <statement>; ... ] UNTIL ( <condition> ) END REPEAT [ <label> ] ;
A statement can be any of the following:
An expression that evaluates to a BOOLEAN.
Put parentheses around the condition in the
REPEAT. For example:
REPEAT ( <condition> ).
conditionnever evaluates to TRUE, and the loop does not contain a BREAK command (or equivalent), then the loop will run and consume credits indefinitely.
conditionis NULL, then it is treated as FALSE.
This example uses a loop to calculate a power of 2. (This is an inefficient solution, but it does demonstrate looping.)
CREATE PROCEDURE power_of_2() RETURNS NUMBER(8, 0) LANGUAGE SQL AS $$ DECLARE counter NUMBER(8, 0); -- Loop counter. power_of_2 NUMBER(8, 0); -- Stores the most recent power of 2 that we calculated. BEGIN counter := 1; power_of_2 := 1; REPEAT power_of_2 := power_of_2 * 2; counter := counter + 1; UNTIL (counter > 8) END REPEAT; RETURN power_of_2; END; $$;
Here is the output of executing the stored procedure:
CALL power_of_2(); +------------+ | POWER_OF_2 | |------------| | 256 | +------------+