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.
Syntax¶
LOOP
<statement>;
[ <statement>; ... ]
END LOOP [ <label> ] ;
Where:
statement
A statement can be any of the following:
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 aBREAK
orRETURN
is executed. TheBREAK
orRETURN
command is almost always inside a conditional expression (e.g.IF
orCASE
).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 |
+----+