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.

Numeric For Loops in Oracle

Leave a Reply

Your email address will not be published. Required fields are marked *

27 − nineteen =