Numeric for loops are one of the workhorses of the PL/SQL programming language. They allow you to iterate through a piece of code an arbitrary number of times. For example, to loop through all the years of this decade:
For decade_loop in 1991..2000 loop
.. your code goes here
end loop;
Remember that you don’t need to declare the decade_loop control variable; Oracle does it for you.
Did you also know that you can loop in reverse?
For decade_loop in reverse 1991..2000 loop
.. your code goes here
end loop;
Note that the order of the control values stays the same!
That’s fun stuff, but let’s take it a step further. First let’s create a function that simply returns the current year as a numeric variable:
create or replace function current_year
return number is
begin
return(to_number(to_char(sysdate,’YYYY’)));
end;
/
Now let’s modify our loop to start with a variable and end with this function:
declare
x number := 1994;
begin
for decade_loop in reverse x..current_year loop
x := x + 1;
dbms_output.put_line(decade_loop);
end loop;
end;
/
The output from this code is:
year = 1999 x= 1995
year = 1998 x= 1996
year = 1997 x= 1997
year = 1996 x= 1998
year = 1995 x= 1999
year = 1994 x= 2000
This shows that you can use variables or functions (even SQL functions) as your loop boundaries. But check out how many
times the loop executed. Even though x was increasing, the loop continued until the initial value of x was reached. This is because Oracle evaluates the expression once at the start of the loop, not for each iteration.