Summary: In this tutorial, you will learn how to use PL/SQL FOR loop to execute a sequence of statements a fixed number times.
Introducing to PL/SQL FOR Loop
PL/SQL FOR loop is an iterative statement that allows you to execute a sequence of statements a fixed number of times. Unlike the PL/SQL WHILE loop, the number of iterations of the PL/SQL FOR loop is known before the loop starts. The following illustrates the PL/SQL FOR loop statement syntax:
[codesyntax lang="plsql" title="PL/SQL FOR Lopp Syntax" bookmarkname="PL/SQL FOR Loop Syntax"]
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound LOOP sequence_of_statements; END LOOP;
Let’s examine the PL/SQL FOR loop syntax in details:
- loop_counter. The PL/SQL automatically creates a local variable loop_counter with data type INTEGER implicitly in the FOR loop so you don’t have to declare it. The scope of the loop_counter variable is within the loop itself so you cannot reference it outside the loop. After each iteration, PL/SQL increases loop_counter by 1 and checks if the loop_counter is still in the range of lower_bound and higher_bound to execute the next iteration. If the loop_counter is not in the range, the loop terminates.
- The lower_bound..higher_bound is the range of integers that FOR loop iterates over. This range is known as iteration scheme. The range operator is specified by a double dot (..) between lower_bound and higher_bound. The FOR loop evaluates the range when the loop first entered and never re-evaluated. The lower_bound has to be less than or equal to the higher_bound. If the lower_bound is equal to the higher_bound, the sequence of statements is executed once. If the lower_bound is larger than the higher_bound, the sequence within the loop will never execute. The lower_bound and higher_bound can be literals, variables, or expressions that evaluate to numbers. Otherwise, PL/SQL raises VALUE_ERROR exception.
- By default, the loop iterates in the upward fashion from the lower_bound to the higher_bound. However, if you want to force the loop to iterates in a downward way from the higher_bound to the lower_bound, you can use the REVERSE keyword after the IN keyword.
- You must have at least one executable statement between LOOP and END LOOP keywords.
Examples of PL/SQL FOR LOOP
In the first example, we print integers from 1 to 10 by using PL/SQL FOR loop as the code below:
[codesyntax lang="plsql" title="PL/SQL FOR Loop Example" bookmarkname="PL/SQL FOR Loop Example" highlight_lines="5,7"]
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_times NUMBER := 10; BEGIN FOR n_i IN 1..n_times LOOP DBMS_OUTPUT.PUT_LINE(n_i); END LOOP; END; /
In the second example, we use the RESERVE keyword to print integers but in descending order.
[codesyntax lang="plsql" title="PL/SQL FOR Loop Example 2" bookmarkname="PL/SQL FOR Loop Example 2" highlight_lines="5,7"]
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_times NUMBER := 10; BEGIN FOR n_i IN REVERSE 1..n_times LOOP DBMS_OUTPUT.PUT_LINE(n_i); END LOOP; END; /
In this tutorial, you’ve learned how to use PL/SQL FOR loop to iterates over a specified range of integer which is known as iteration scheme.