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
- CONTINUE
- EXIT / EXIT WHEN
- GOTO
CONTINUE
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.
EXIT
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.
GOTO
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.
Basic loop
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.
Syntax :
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.
Syntax :
LOOP sequence_of_statements; EXIT; END LOOP;
LOOP sequence_of_statements; EXIT WHEN condition; END LOOP;
Example :
SET SERVEROUTPUT ON; DECLARE counter NUMBER := 0; BEGIN LOOP counter := counter + 1; DBMS_OUTPUT.PUT_LINE('Counter : ' || counter); IF counter = 5 THEN EXIT; END IF; END LOOP; END; /
Counter : 1 Counter : 2 Counter : 3 Counter : 4 Counter : 5 PL/SQL procedure successfully completed.
SET SERVEROUTPUT ON; DECLARE counter NUMBER := 0; BEGIN LOOP counter := counter + 1; DBMS_OUTPUT.PUT_LINE('Counter : ' || counter); EXIT WHEN counter=3; END LOOP; END; /
Counter : 1 Counter : 2 Counter : 3 PL/SQL procedure successfully completed.
FOR 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.
Syntax :
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound LOOP sequence_of_statements; END LOOP;
Example :
DECLARE n_counter NUMBER := 5; BEGIN FOR i IN 1 .. n_counter LOOP DBMS_OUTPUT.PUT_LINE('Iteration :' || i); END LOOP; END; /
Iteration :1 Iteration :2 Iteration :3 Iteration :4 Iteration :5 PL/SQL procedure successfully completed.
DECLARE n_counter NUMBER := 5; BEGIN FOR i IN 1 .. n_counter LOOP IF i=3 THEN CONTINUE; END IF; DBMS_OUTPUT.PUT_LINE('Iteration :' || i); END LOOP; END; /
Iteration :1 Iteration :2 Iteration :4 Iteration :5 PL/SQL procedure successfully completed.
In 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 :
DECLARE n_counter NUMBER := 5; BEGIN FOR i IN 1 .. n_counter LOOP CONTINUE WHEN i=3; DBMS_OUTPUT.PUT_LINE('Iteration :' || i); END LOOP; END; /
Reverse FOR LOOP Statements
DECLARE n_counter NUMBER := 5; BEGIN FOR i IN REVERSE 1 .. n_counter LOOP CONTINUE WHEN i=3; DBMS_OUTPUT.PUT_LINE('Iteration :' || i); END LOOP; END; /
Iteration :5 Iteration :4 Iteration :2 Iteration :1 PL/SQL procedure successfully completed.
FOR LOOP with GOTO Statement.
DECLARE n_counter NUMBER := 5; BEGIN FOR i IN 1 .. n_counter LOOP DBMS_OUTPUT.PUT_LINE('Iteration :' || i); IF i=3 THEN GOTO counter_reached; END IF; END LOOP; <<counter_reached>> DBMS_OUTPUT.PUT_LINE('Counter reached to 3'); END; /
In above example, when the condition is true, GOTO statement transfer the control out side the loop to the label “counter_reached”.
GOTO Statement Goes to Labeled NULL Statement
DECLARE done BOOLEAN; BEGIN FOR i IN 1..10 LOOP IF done THEN GOTO end_loop; END IF; <<end_loop>> NULL; END LOOP; END; /
GOTO Statement Cannot Transfer Control into IF Statement
DECLARE valid BOOLEAN := TRUE; BEGIN GOTO update_row; IF valid THEN <<update_row>> NULL; END IF; END; /
Error report - ORA-06550: line 4, column 3: PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'UPDATE_ROW' ORA-06550: line 6, column 12: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:n%s" *Cause: Usually a PL/SQL compilation error. *Action:
NULL Statement
The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).
WHILE loop
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.
Syntax :
WHILE condition LOOP sequence_of_statements; END LOOP;
Example :
DECLARE n_counter NUMBER := 5; n_factorial NUMBER := 1; n_number NUMBER; BEGIN n_number := n_counter; WHILE n_counter > 0 LOOP n_factorial := n_factorial * n_counter; n_counter := n_counter - 1; END LOOP; DBMS_OUTPUT.PUT_LINE('factorial of ' || n_number || ' is ' || n_factorial); END; /
factorial of 5 is 120 PL/SQL procedure successfully completed.
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.
Examples :
Implicit Cursor FOR LOOP Statement
BEGIN FOR item IN ( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name ) LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Explicit Cursor FOR LOOP Statement
DECLARE CURSOR c IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name; BEGIN FOR item IN c LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Passing Parameters to Explicit Cursor FOR LOOP Statement
DECLARE CURSOR c (job VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE job_id = job AND salary > max_wage; BEGIN FOR person IN c('ST_CLERK', 3000) LOOP DBMS_OUTPUT.PUT_LINE ( 'Name = ' || person.last_name || ', salary = ' || person.salary || ', Job Id = ' || person.job_id ); END LOOP; END; /
We will see more about cursors in detail in next chapters.
Nested Loops
The loop statements can also be nested. The outer and inner loop can be of different types. In nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.
Syntax :
LOOP -- outer statements; LOOP -- inner statements; END LOOP; statements; END LOOP;
Example :
Labelling 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.
Syntax :
<<outer_loop>> LOOP statements; <<inner_loop>> LOOP statements; END LOOP; statements; END LOOP';