LOOP

A LOOP loop does not specify a number of iterations or a terminating condition. The user must explicitly exit the loop by using BREAK or RETURN inside the loop.

For more information on loops, see Working with Loops.

See also

BREAK, CONTINUE, RETURN

Syntax

LOOP
    <statement>;
    [ <statement>; ... ]
END LOOP [ <label> ] ;

Where:

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

  • A LOOP repeats until a BREAK or RETURN is executed. The BREAK or RETURN command is almost always inside a conditional expression (e.g. IF or CASE).

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

Examples

This loop inserts predictable test data into a table:

CREATE TABLE dummy_data (ID INTEGER);

CREATE PROCEDURE break_out_of_loop()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
    DECLARE
        counter INTEGER;
    BEGIN
        counter := 0;
        LOOP
            counter := counter + 1;
            IF (counter > 5) THEN
                BREAK;
            END IF;
            INSERT INTO dummy_data (ID) VALUES (:counter);
        END LOOP;
        RETURN counter;
    END;
$$
;

Here is the output of executing the stored procedure:

CALL break_out_of_loop();
+-------------------+
| BREAK_OUT_OF_LOOP |
|-------------------|
|                 6 |
+-------------------+

Here is the content of the table after calling the stored procedure:

SELECT *
    FROM dummy_data
    ORDER BY ID;
+----+
| ID |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
⇧
Back to top