Loop Statements

You can use the LOOP, EXIT, CONTINUE, WHILE, and FOR statements to make the SPL program repeat a sequence of commands.

LOOP

LOOP

statements

END LOOP;

The LOOP statement defines an unconditional loop that loops indefinitely and terminates when an EXIT or RETURN command is encountered.

EXIT

EXIT [ WHEN expression ];

The purpose of this statement is to end the innermost loop and then execute the statement that follows END LOOP. If the WHEN clause is present, then the loop can be exited only if the specified condition is true, otherwise the flow will go to the statement following EXIT. You can use the EXIT statement to exit from all types of loops early, and there is no restriction on the use of this statement in unconditional loops.

The following is a simple example of a loop that repeats 10 times and then exits the loop using the EXIT statement.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_1 ()
IS
    v_counter NUMBER(2);
BEGIN
    v_counter := 1;
    LOOP
        EXIT WHEN v_counter > 10;
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;
/
\set PLSQL_MODE off

The following is the output of the program.

postgres=# select LOOP_PRO_1();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_1
------------

(1 row)