Summary: in this tutorial, you will learn how to use PL/SQL FOR loop to execute a sequence of statements specified number of 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:
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 greater detail:
- PL/SQL automatically creates a local variable
INTEGERdata type implicitly in the
FORloop so you don’t have to declare it. The scope of the
loop_countervariable is within the loop itself so you cannot reference it outside the loop. After each iteration, PL/SQL increases
loop_counterby 1 and checks if the
loop_counteris still in the range of
higher_boundto execute the next iteration. If the
loop_counteris not in the range, the loop is terminated.
lower_bound..higher_boundis the range of integers that
FORloop iterates over. This range is known as iteration scheme. The range operator is specified by a double dot (
..) between the
FORloop evaluates the range when the loop first entered and never re-evaluated. The
lower_boundhas to be less than or equal to the
higher_bound. If the
lower_boundis equal to the
higher_bound, the sequence of statements is executed once. If the
lower_boundis larger than the
higher_bound, the sequence within the loop will never execute. The
higher_boundcan be literals, variables, or expressions that evaluate to numbers. Otherwise, PL/SQL raises a
- By default, the loop iterates in the upward fashion from the
higher_bound. However, if you want to force the loop to iterate in a downward way from the
lower_bound, you can use the
REVERSEkeyword after the
- You must have at least one executable statement between
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:
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 a list of integers in descending order.
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 statement to execute a statement specified number of times.