11 januari 2024

For instance, if you wanted to display a list of even numbers you had to write something like this:

for i in 1 .. 10 loop
 if mod( i, 2 ) = 0 then
   dbms_output.put_line( to_char( i ) );
 end if;
end loop;

In Oracle Database 23c (actually already since Oracle Database 21c) you can create loops with a stepped iterator:

for i in 2 .. 10 by 2 loop
 dbms_output.put_line( to_char( i ) );
end loop;

Be aware that you may have to change the lowest number to get the correct results.  You don’t have to use an integer as the ‘step’ value. This can also be a fractional value:

for i in 2 .. 5 by .5 loop
 dbms_output.put_line( to_char( i ) );
end loop;

But if you run your code like this, you will still see only the values 2, 3, 4, and 5. That is because the iterator is still an integer, which means it cannot hold fractional values. Another new thing in Oracle Database 23c is that the iterator datatype can be modified. To create the correct loop, which will display 2, 2.5, 3, 3.5, 4, 4.5, and 5 we need to change the iterator to a floating point type, like number( 2, 1 ).

for i number( 2, 1 ) in 2 .. 5 by .5 loop
 dbms_output.put_line( to_char( i ) );
end loop;

That is another new thing in Oracle 21c. You have more control over the iterator. Up until Oracle Database 21c the iterator was always an integer and you had no control over it. In Oracle Database 21c you can not only define a different datatype (number, instead of integer), you can also define the iterator to be mutable inside the loop. If you try to run code like this, it will result in an error:

for i number( 3, 2 ) in 2 .. 5 by .5 loop
 dbms_output.put_line( to_char( i ) );
 if i = 3
   then i := 3.25;
 end if;
end loop;

ORA-06550: line 5, column 12:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 5, column 12:
PL/SQL: Statement ignored

But Oracle Database 21c allows you to define the iterator to be mutable, therefor you have full control over the value of the iterator.

begin
 for i mutable number( 3, 2 ) in 2 .. 5 by .5 loop
   dbms_output.put_line( to_char( i ) );
   if i = 3
     then i := 3.25;
   end if;
 end loop;
end;
/
2
2.5
3
3.75
4.25
4.75

PL/SQL procedure successfully completed

Be very careful when doing this, because it is really easy to create endless loops. Believe me, I know, I did just that when writing the examples.

Collections
When looping through the contents of a collection, you had two choices. If your collection is densely filled, you can iterate from the first item to the last item, visiting every item in between (except for Associative Arrays indexed by a Varchar2) like this:

declare
 type driver_tt is table of f1data.drivers%rowtype
                index by pls_integer;
 drivers driver_tt;
begin
 select d.*
 bulk   collect
 into   drivers
 from   f1data.drivers d
 where  d.nationality = 'Dutch'
 order  by d.forename;
 
 for driver in drivers.first .. drivers.last loop
   dbms_output.put_line(  drivers(driver).forename
                       || ' ' 
                       || drivers(driver).surname 
                       );
 end loop;
end;
/
Ben Pon
Boy Lunger
Carel Godin de Beaufort
Christijan Albers
Dries van der Lof
Ernie de Vos
Giedo van der Garde
Gijs van Lennep
Huub Rothengatter
Jan Lammers
Jan Flinterman
Jos Verstappen
Max Verstappen
Michael Bleekemolen
Nyck de Vries
Rob Slotemaker
Robert Doornbos
Roelof Wunderink

PL/SQL procedure successfully completed

Let’s choose to remove Max Verstappen from the retrieved list (we would never do this, but for the sake of argument) then the collection is not dense anymore, so if you would try to iterate over the collection from first to last, you would run into a no data found error:

declare
 type driver_tt is table of f1data.drivers%rowtype
                index by pls_integer;
 drivers driver_tt;
begin
 select d.*
 bulk   collect
 into   drivers
 from   f1data.drivers d
 where  d.nationality = 'Dutch'
 order  by d.forename;

 drivers.delete( 13 ); -- Remove Max Verstappen
 for driver in drivers.first .. drivers.last loop
   dbms_output.put_line(  drivers(driver).forename
                       || ' ' 
                       || drivers(driver).surname 
                       );
 end loop;
end;
/

Ben Pon
Boy Lunger
Carel Godin de Beaufort
Christijan Albers
Dries van der Lof
Ernie de Vos
Giedo van der Garde
Gijs van Lennep
Huub Rothengatter
Jan Lammers
Jan Flinterman
Jos Verstappen
<< removed the code for brevity >>
ORA-01403: no data found
ORA-06512: at line 14
You may know how to use a sparse collection in a forall statement. You can use the indices of and the values of construction. A similar construction is now available in the loops in PL/SQL.
declare
 type driver_tt is table of f1data.drivers%rowtype
                index by pls_integer;
 drivers driver_tt;
begin
 select d.*
 bulk   collect
 into   drivers
 from   f1data.drivers d
 where  d.nationality = 'Dutch'
 order  by d.forename;

 drivers.delete( 13 ); -- Remove Max Verstappen
 for driver in indices of drivers loop
   dbms_output.put_line(  drivers(driver).forename
                       || ' ' 
                       || drivers(driver).surname 
                       );
 end loop;
end;
/

declare
 type driver_tt is table of f1data.drivers%rowtype
                index by pls_integer;
 drivers driver_tt;
begin
 select d.*
 bulk   collect
 into   drivers
 from   f1data.drivers d
 where  d.nationality = 'Dutch'
 order  by d.forename;

 drivers.delete( 13 ); -- Remove Max Verstappen
 for driver in values of drivers loop
   dbms_output.put_line(  driver.forename
                       || ' ' 
                       || driver.surname 
                       );
 end loop;
end;
/

Both scripts result in the same output, but note, in first one (indices of) the iterator is a numeric value, which is the pointer into the collection, whereas in the second on (values of) the iterator is the complete record from the collection. Therefore, you have to treat them a bit differently.

You can read more on the new PL/SQL iterator constructs in Modern Oracle Database Programming, Chapter 7.

The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/iterators.sql

Also read my previous blogs about Oracle Database 23:

• Introduction blog series Oracle Database 23
• Developer Role
• Group by Alias
• If [Not] Exists 
• Table values constructor
• The Boolean data type

And my next blog:

• Annotations

Auteur: Patrick Barel

Terug naar overzicht

Deel

Facebook Twitter LinkedIn E-mail Whatsapp