Enhancements to the FOR Loop in Oracle 21c

You are probably already familiar with the FOR loop format:

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
   {...statements...}
END LOOP;

According to this syntax, the loop counter (loop_counter) sequentially (with a step size of 1) iterates from the lowest_number to the highest_number.

For example, executing the following code displays the numbers from 4 to 8:

SQL> set serveroutput on
begin
  for i in 4 .. 8 loop
    dbms_output.put_line(i);
  end loop;
end;
/
4
5
6
7
8
PL/SQL procedure successfully completed.

If we wanted the output above to also include the numbers from 50 to 55, what changes would be necessary?
Prior to Oracle 21c, based on the existing FOR loop syntax, we would have to use two separate FOR loops:

begin
  for i in 4 .. 8 loop
    dbms_output.put_line(i);
  end loop;
  for i in 50 .. 55 loop
    dbms_output.put_line(i);
  end loop;  
end;
/
4
5
6
7
8
50
51
52
53
54
55
PL/SQL procedure successfully completed.

With the enhancements introduced in Oracle 21c, solving such problems has become simpler.
For example, the following code produces the same result as the above example:

begin
     for i in 4..8,50..55 loop
       dbms_output.put_line(i);
     end loop;
   end;
 /

Let’s explore more of the new FOR loop enhancements in Oracle 21c through additional examples.

Example (WHEN clause)
In the following code, using Oracle 21c’s new feature, we display all multiples of 4 before the number 30:

BEGIN
  FOR i IN 1 .. 30 WHEN MOD(i,4) = 0 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;
/
4
8
12
16
20
24
28

Example (WHILE clause)
A number is assigned to a variable, and the loop continues as long as the counter is less than half of that number:

declare
n number:=10;
BEGIN
  FOR i IN 1 .. n WHILE i<n/2 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;
/
1
2
3
4

Example (Step control)
Displaying every other number between 1 and 10:

begin
  for i in 1 .. 10 by 2 loop
    dbms_output.put_line(i);
  end loop;
end;
/
1
3
5
7
9

Example (Decimal counter values)
The counter value can be defined as a decimal number:

BEGIN
  FOR i number(10,5) IN 3.5..4.4 by 0.4 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;
/
3.5
3.9
4.3

Example (REPEAT clause)
In Oracle 21c, you can define only the starting point for the counter:

BEGIN
  FOR i IN 5 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;
/
5

Although this may not seem very useful on its own, with the REPEAT clause, it becomes more practical.
For example, the following code displays the powers of 4 up to 1500:

begin
  for i in 4, repeat i*4 while i < 1500 loop
    dbms_output.put_line(i);
  end loop;
end;
/
4
16
64
256
1024