Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL - Implement FOR .. OF loops from Ada 2012

Jon TheriaultJan 24 2017 — edited Jan 24 2017

To create a block in PL/SQL to loop through a possibly sparse table you'd need to write the following:

declare

     index_l     pls_integer;

     value_l     varchar2 ( 200 );

     -- mytable_l is a table declared outside of the block

begin

     if mytable_l is not null then

          index_l := mytable_l.first();

     end if;

     while index_l is not null loop

          value_l := mytable_l ( index_l );

          do_something ( value_l );

          index_l := mytable_l.next ( index_l );

     end loop;

end;

There's about twelve lines of code for a basic loop.  This is actually a lot of code for what is a relatively common task and is the most common complaint I hear from people new to PL/SQL - especially those who just came out of college with the FOR ... EACH loop from the Java/.net world.  For existing developers I'd argue with each line of code it becomes harder to read and more likely for a developer to fat finger something (or forget to add the call to next, etc).  Ada 2012 introduced the FOR OF iteration which I'd imagine would be functionally identical but look something like this in PL/SQL.

if mytable_l is not null

     for value_l of mytable_l loop

          do_something ( value_l );

     end loop;

end if;

We've more than halved the amount of code and didn't need to create a block just to keep value_l a local variable within the loop (PL/SQL would ensure that).

As the Ada 2012 Rationale explains their example code:

Not only is this just five lines of text rather than nine or eleven, the key point is that the possibility of making various errors of detail is completely removed.

The mechanisms by which this magic abstraction is achieved are somewhat laborious and it is anticipated that users will take a cookbook approach (show us how to do it, but please don't explain why – after all, this is the approach taken with boiling an egg, we can do it without deep knowledge of the theory of coagulation of protein material).

Comments

Post Details

Added on Jan 24 2017
9 comments
468 views