WHILE

A WHILE loop iterates while a specified condition is true.

For more information on loops, see Working with Loops.

See also

BREAK, CONTINUE

Syntax

WHILE ( <condition> ) { DO | LOOP }
    <statement>;
    [ <statement>; ... ]
END { WHILE | LOOP } [ <label> ] ;

Where:

condition

An expression that evaluates to a BOOLEAN.

statement

A statement can be any of the following:

  • A single SQL statement (including CALL).

  • A control-flow statement (e.g. looping or branching statement).

  • A nested block.

label

An optional label. Such a label can be a jump target for a BREAK or CONTINUE statement. A label must follow the naming rules for Object Identifiers.

Usage Notes

  • Put parentheses around the condition in the WHILE. For example: WHILE ( <condition> ).

  • If the condition never evaluates to FALSE, and the loop does not contain a BREAK command (or equivalent), then the loop will run and consume credits indefinitely.

  • If the condition is NULL, then it is treated as FALSE.

  • A loop can contain multiple statements. You can use, but are not required to use, a BEGIN … END block to contain those statements.

  • The keyword DO should be paired with END WHILE and the keyword LOOP should be paired with END LOOP. For example:

    while (...) do
        ...
    end while;
    
    while (...) loop
        ...
    end loop;
    

Example

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;
    WHILE (counter <= 8) DO
        power_of_2 := power_of_2 * 2;
        counter := counter + 1;
    END WHILE;
    RETURN power_of_2;
END;
$$
;

Here is the output of executing the stored procedure:

CALL power_of_2();
+------------+
| POWER_OF_2 |
|------------|
|        256 |
+------------+
Back to top