Iterative control

With the LOOP, WHILE, FOR, and EXIT statements, you can control the flow of execution of your NZPLSQL program iteratively.

LOOP statement

The LOOP statement defines an unconditional loop that repeats until terminated by an EXIT statement or a RETURN statement (which terminates the procedure and the loop). It has the following syntax:

[<<label>>]
LOOP
    statements
END LOOP;

The optional label can be used by EXIT statements of nested loops to specify which level of nesting is terminated.

EXIT statement

The EXIT statement terminates a loop. It has the following syntax:

EXIT [ label ] [ WHEN expression ];

If you do not specify a label, the innermost loop is terminated and the statement that follows END LOOP runs next. If you specify a label, it must be the label of the current or an upper level of nested loop or blocks. Then the named loop or block is terminated and control continues with the statement after the corresponding END of the loop or blocks.

Examples:

LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;

LOOP
-- some computations
EXIT WHEN count > 0;
END LOOP;

BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT; 
END IF;
END;

WHILE statement

With the WHILE statement, you can loop through a sequence of statements if the evaluation of the condition expression is true.

[<<label>>]
WHILE expression LOOP
statements
END LOOP;

For example:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;

WHILE NOT boolean_expression LOOP
-- some computations here
END LOOP;

FOR statement

By using the FOR statement, you can create a loop that iterates over a range of integer values.

[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP;

The variable name is automatically created as type integer and exists only inside the loop. The two expressions for the lower and upper bound of the range are evaluated only when entering the loop. The iteration step is always 1.

Some examples of FOR loops:

FOR i IN 1 .. 10 LOOP
-- some expressions here
RAISE NOTICE 'i is %',i;
END LOOP;

FOR i IN REVERSE 10 .. 1 LOOP
-- some expressions here
END LOOP;