A LOOP statement is an iterative control statement that allows you to executes a sequence of statements multiple times.
PL/SQL provides these loop statements.
- Basic loop
- FOR loop
- WHILE loop
- Cursor FOR loop
Loop Control Statements
To prevent an infinite loop, at least one statement must transfer control outside the loop. The statements that can transfer control outside the loop are
- EXIT / EXIT-WHEN
The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.
Restrictions on CONTINUE Statement
- A CONTINUE statement must be inside a LOOP statement.
- A CONTINUE statement cannot cross a subprogram or method boundary.
The EXIT statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the end of either the current loop or an enclosing labeled loop.
Restriction on EXIT Statement
- An EXIT statement must be inside a LOOP statement.
The GOTO statement transfers control to a labeled block or statement.
Restrictions on GOTO Statement
- A GOTO statement cannot transfer control into an IF statement, CASE statement, LOOP statement, or sub-block.
- A GOTO statement cannot transfer control from one IF statement clause to another, or from one CASE statement WHEN clause to another.
- A GOTO statement cannot transfer control out of a subprogram.
- A GOTO statement cannot transfer control into an exception handler.
- A GOTO statement cannot transfer control from an exception handler back into the current block.
With each iteration of the basic LOOP statement, its statements run and control returns to the top of the loop. The LOOP statement ends when a statement inside the loop transfers control outside the loop or raises an exception.
LOOP sequence_of_statements; END LOOP;
Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop. To prevent this, the EXIT condition should be given inside this execution block so that control exit from the loop.
LOOP sequence_of_statements; EXIT; END LOOP;
LOOP sequence_of_statements; EXIT WHEN condition; END LOOP;
PL/SQL FOR LOOP is an iterative statement that allows you to execute a sequence of statements a fixed number of times. The FOR LOOP statement ends when its index reaches a specified value, or when a statement inside the loop transfers control outside the loop or raises an exception.
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound LOOP sequence_of_statements; END LOOP;
In the above example, CONTINUE statement skipped the iteration when the condition is true and transfer the control to the next iteration of the current loop.
CONTINUE WHEN statement :
Reverse FOR LOOP Statements
FOR LOOP with GOTO Statement.
In above example, when the condition is true, GOTO statement transfers the control outside the loop to the label “counter_reached”.
GOTO Statement Goes to Labeled NULL Statement
GOTO Statement Cannot Transfer Control into IF Statement
The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).
The WHILE LOOP statement runs one or more statements while a condition is TRUE. The WHILE LOOP statement ends when the condition becomes FALSE or NULL, or when a statement inside the loop transfers control outside the loop or raises an exception.
WHILE condition LOOP sequence_of_statements; END LOOP;
We can include EXIT, EXIT WHEN, CONTINUE and GOTO statements in WHILE LOOP as well as per requirement.
Cursor FOR LOOP
The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor.
With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor.
Implicit Cursor FOR LOOP Statement
Explicit Cursor FOR LOOP Statement
Passing Parameters to Explicit Cursor FOR LOOP Statement
We will see more about cursors in detail in the next chapters.
The loop statements can also be nested. The outer and inner loop can be of different types. In the nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.
LOOP -- outer statements; LOOP -- inner statements; END LOOP; statements; END LOOP;
Labeling of Loops
In PL/SQL, the loops can be labeled. The label should be enclosed between “<<” and “>>”. The labeling of loops particularly in nested loop codes will give more readability.
The label can be given to GOTO statement to exit from that particular loop. Using label, the control can be made to directly exit the outer loop of the nested loops from anyplace inside the loops.
<< outer_loop >> LOOP statements; << inner_loop >> LOOP statements; END LOOP; statements; END LOOP';