The FOR loop. Oracle Database offers both
a numeric and a cursor FOR loop. With the
numeric FOR loop, you specify the start and
end integer values, and PL/SQL does the rest
of the work for you, iterating through each
integer value between the start and the end
and then terminating the loop:
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
IS
BEGIN
FOR l_current_year
IN start_year_in .. end_year_in
LOOP
display_total_sales
(l_current_year);
END LOOP;
END display_multiple_years;
The cursor FOR loop has the same basic
structure, but with it you supply an explicit
cursor or SELEC T statement in place of the
low/high integer range:
Code Listing 3: A WHILE loop
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
IS
l_current_year PLS_INTEGER := start_year_in;
BEGIN
WHILE (l_current_year <= end_year_in)
LOOP
display_total_sales (l_current_year);
l_current_year := l_current_year + 1;
END LOOP;
END display_multiple_years;
Code Listing 4: A FOR loop with a conditional exit
PROCEDURE display_multiple_years (
BEGIN
FOR l_current_year IN start_year_in .. end_year_in
LOOP
display_total_sales (l_current_year);
EXIT WHEN total_sales (l_current_year) = 0;
END LOOP;
END display_multiple_years;
Code Listing 5: A WHILE loop with one exit
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
IS
BEGIN
FOR l_current_year IN (
SELECT FROM sales_data
WHERE year
BETWEEN start_year_in
AND end_year_in)
LOOP
display_total_sales
END display_multiple_years;
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
BEGIN
WHILE ( l_current_year <= end_year_in
AND total_sales (l_current_year) > 0)
LOOP
display_total_sales (l_current_year);
l_current_year := l_current_year + 1;
END LOOP;
END display_multiple_years;
In both the numeric and the cursor FOR
loop, Oracle Database implicitly declares
the iterator (in the examples above, it is l_
current_year) for you, as either an integer or
a record. You do not have to (and should not)
declare a variable with that same name, as in
,end_year_in IN PLS_INTEGER
)
IS
l_current_year
INTEGER; /* NOT NEEDED */
BEGIN
FOR l_current_year
IN start_year_in
.. end_year_in
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
In fact, if you do declare such a variable, it
will not be used by the FOR loop. It is, more
specifically, a different integer variable than
that declared implicitly by Oracle Database
and used within the body of the loop.
