PL/SQL in 21c gets amazing new loop iterators

Posted by

“It’s a dead language” people said.

“There’s never anything new” people said.

Well, let me serve those people a slice of humble pie Smile as we take a look at huge suite of enhancements to iteration handling in PL/SQL in 21c. In particular, there is one thing that I really find nice about these new changes. Most of them are improvements to functionality that you could already do, but had to do it in a cumbersome way. Thus these changes are not about dramatic new features, but (in my opinion) something equally if not more important, namely, allowing developers to achieve that functionality with less code, cleaner code and more maintainable code.

That’s a big win in my book. Here’s a look at some “before” (19c and below) and “after” (21c) code in PL/SQL to illustrate the new language facilities in PL/SQL.

Multiple loop ranges

How we used to do it …

We needed individual loops


SQL> set serverout on
SQL> begin
  2    for i in 1 ..10 loop
  3      dbms_output.put_line(i);
  4    end loop;
  5    for i in 100..110 loop
  6      dbms_output.put_line(i);
  7    end loop;
  8    for i in 200..210 loop
  9      dbms_output.put_line(i);
 10    end loop;
 11  end;
 12  /
1
2
3
4
5
6
7
8
9
10
100
101
102
103
104
105
106
107
108
109
110
200
201
202
203
204
205
206
207
208
209
210

PL/SQL procedure successfully completed.

How we can do it in 21c…


SQL> begin
  2    for i in 1 ..10, 100..110, 200..210 loop
  3      dbms_output.put_line(i);
  4    end loop;
  5  end;
  6  /
1
2
3
4
5
6
7
8
9
10
100
101
102
103
104
105
106
107
108
109
110
200
201
202
203
204
205
206
207
208
209
210

PL/SQL procedure successfully completed.

Arbitrary loop increment

How we used to do it …

What if we want to cycle through every 3rd iteration? We had to go back to high school modulo shenanigans.


SQL> begin
  2    for i in 1 .. 20 loop
  3       if mod(i,3)=1 then
  4         dbms_output.put_line(i);
  5       end if;
  6    end loop;
  7  end;
  8  /
1
4
7
10
13
16
19

PL/SQL procedure successfully completed.

How we can do it in 21c…


SQL> begin
  2    for i in 1 .. 20 by 3 loop
  3       dbms_output.put_line(i);
  4    end loop;
  5  end;
  6  /
1
4
7
10
13
16
19

PL/SQL procedure successfully completed.

And yes, it does not have to be an integer! Just make sure you tell the PL/SQL compiler that your loop variable is no longer an integer, otherwise (like standard Oracle) we’ll round it to the nearest whole number. So whilst this looks odd:


SQL> begin
  2    for i in 1 .. 10 by 0.5 loop
  3      dbms_output.put_line(i);
  4    end loop;
  5  end;
  6  /
1
2
3
4  --
5  -- no fractional parts?
6  --
7
8
9
10

PL/SQL procedure successfully completed.

but all you need to do is nominate the datatype of your iteration variable. That’s right, now you can choose the datatype of the iterator!


SQL> begin
  2    for i number(3,1) in 1 .. 10 by 0.5 loop
  3      dbms_output.put_line(i);
  4    end loop;
  5  end;
  6  /
1
1.5
2
2.5
3
3.5
4
4.5
5
5.5
6
6.5
7
7.5
8
8.5
9
9.5
10

PL/SQL procedure successfully completed.

Assign collection data

How we used to do it …

You had to assign each individual element if you were not fortunate enough to be sourcing the data from (say) a BULK COLLECT statement or an identical existing collection.


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4  begin
  5    for i in 1 .. 10 loop
  6      s1(i) := i*10;
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Or we could use the more compact but still quite verbose assignment syntax


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4  begin
  5    s1 := num_list(1=>10,2=>20,3=>30,4=>40,5=>50,
  6                   6=>60,7=>70,8=>80,9=>90,10=>100);
  7  end;
  8  /

PL/SQL procedure successfully completed.

How we can do it in 21c…

Now you can just assign them in the same way you would initialise a VARRAY or NESTED TABLE.


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4  begin
  5    s1 := num_list(10,20,30,40,50,60,70,80,90,100);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Copy collection subset

How we used to do it …

If we wanted to grab a subset of a collection, then typically we loop through the elements and select what we need. For example, to grab every 2nd element, our code would look like this:


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4    s2   num_list;
  5  begin
  6    for i in 1 .. 10 loop
  7      s1(i) := i*10;
  8    end loop;
  9
 10    for i in 2 .. 10 by 2 loop
 11      s2(i) := s1(i);
 12    end loop;
 13    for i in 1 .. 10 loop
 14      if s2.exists(i) then
 15        dbms_output.put_line(i||'='||s2(i));
 16      else
 17        dbms_output.put_line(i||' not exists');
 18      end if;
 19    end loop;
 20  end;
 21  /
1 not exists
2=20
3 not exists
4=40
5 not exists
6=60
7 not exists
8=80
9 not exists
10=100

PL/SQL procedure successfully completed.

How we can do it in 21c…


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4    s2   num_list;
  5  begin
  6    s1 := num_list(1,2,3,4,5,6,7,8,9,10);
  7    s2 := num_list(for i in 2 .. 10 by 2 => s1(i));
  8
  9    for i in 1 .. 10 loop
 10      if s2.exists(i) then
 11        dbms_output.put_line(i||'='||s2(i));
 12      else
 13        dbms_output.put_line(i||' not exists');
 14      end if;
 15    end loop;
 16  end;
 17  /
1 not exists
2=2
3 not exists
4=4
5 not exists
6=6
7 not exists
8=8
9 not exists
10=10

PL/SQL procedure successfully completed.

Did you notice? That is a loop iteration right there in the assignment statement for a collection!

Iterate through a sparse collection

How we used to do it …

If you wanted to make sure you avoided a “no data found” error, when a collection did not have contiguous indexes we have to ensure the index entry exists before we can use it


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4    s2   num_list;
  5    idx  int;
  6  begin
  7     
  8    s2 := num_list(2=>20,4=>40,6=>60,8=>80,10=>100);
  9
 10    idx := s2.first;
 11    loop
 12      if idx is not null then
 13        dbms_output.put_line(idx||'='||s2(idx));
 14      else
 15        exit;
 16      end if;
 17      idx := s2.next(idx);
 18    end loop;
 19  end;
 20  /
2=20
4=40
6=60
8=80
10=100

PL/SQL procedure successfully completed.

How we can do it in 21c…


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4    s2   num_list;
  5  begin
  6    s1 := num_list(10,20,30,40,50,60,70,80,90,1000);
  7    s2 := num_list(for i in 2 .. 10 by 2 => s1(i));
  8
  9    for idx in indices of s2 loop
 10        dbms_output.put_line(idx||'='||s2(idx));
 11    end loop;
 12  end;
 13  /
2=20
4=40
6=60
8=80
10=1000

PL/SQL procedure successfully completed.

But there’s more here as well. The INDICES clause gets us the index entries. But I don’t have to use that if I want the values of those entries because I can those directly now as well.


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4    s2   num_list;
  5  begin
  6    s1 := num_list(10,20,30,40,50,60,70,80,90,1000);
  7    s2 := num_list(for i in 2 .. 10 by 2 => s1(i));
  8
  9    for idx in values of s2 loop
 10        dbms_output.put_line(idx);
 11    end loop;
 12  end;
 13  /
20
40
60
80
1000

PL/SQL procedure successfully completed.

And I can even have both the indices and the values using the PAIRS clause


SQL> declare
  2    type num_list is table of int index by pls_integer;
  3    s1   num_list;
  4    s2   num_list;
  5  begin
  6    s1 := num_list(10,20,30,40,50,60,70,80,90,1000);
  7    s2 := num_list(for i in 2 .. 10 by 2 => s1(i));
  8
  9    for x,y in pairs of s2 loop
 10        dbms_output.put_line(x||','||y);
 11    end loop;
 12  end;
 13  /
2,20
4,40
6,60
8,80
10,1000

PL/SQL procedure successfully completed.

Manipulate the iteration variable – part 1

Like the MOD example earlier, if the iteration variable was not an ascending integer, we would need to perform additional work to manipulate it to satisfy our need. For example, what if I needed to loop through the powers of 2 in order to examine some bit masks?

How we used to do it …

I need another expression to get the job done


SQL> declare
  2    bits int := 739;
  3  begin
  4    for i in 0 .. 10 loop
  5      if bitand(bits, power(2,i)) > 0 then
  6        dbms_output.put_line(power(2,i));
  7      end if;
  8    end loop;
  9  end;
 10  /
1
2
32
64
128
512

PL/SQL procedure successfully completed.

How we can do it in 21c…

Now we have full control over our iterator


SQL> declare
  2    bits int := 739;
  3  begin
  4    for power2 in 1, repeat power2*2 while power2 <= 1024 loop
  5      if bitand(bits, power2) > 0 then
  6        dbms_output.put_line(power2);
  7      end if;
  8    end loop;
  9  end;
 10  /
1
2
32
64
128
512

PL/SQL procedure successfully completed.

Manipulate the iteration variable – part 2

And we are not limited to simple REPEAT commands etc whilst iterating. We have total control over the iteration variable, including being able to change it on the fly and utilise its value in the loop definition itself.

How we used to do it …


--
-- I don't know, but its a lot of code :-)
--

How we can do it in 21c…


SQL> begin
  2   for i in 1 .. 10,
  3            i+1 while i<5,
  4            6..15 by trunc(i/4),
  5            i .. i+10 when mod(i,3) = 0
  6   loop
  7     dbms_output.put_line(i);
  8   end loop;
  9  end;
 10  /
1
2
3
4
5
6
7
8
9
10
6
8
10
12
14
15
18
21
24

PL/SQL procedure successfully completed.

Loop through a ref cursor

How we used to do it …


SQL> variable rc refcursor
SQL> exec open :rc for select empno from emp;

PL/SQL procedure successfully completed.

SQL> declare
  2    type rec is record ( r1 number );
  3    type rec_list is table of rec index by pls_integer;
  4    r rec_list;
  5  begin
  6    loop
  7      fetch :rc bulk collect into r limit 10;
  8      for i in 1 .. r.count loop
  9        dbms_output.put_line(r(i).r1);
 10      end loop;
 11      exit when :rc%notfound;
 12    end loop;
 13  end;
 14  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

How we can do it in 21c…


SQL> variable rc refcursor
SQL> exec open :rc for select empno from emp;

PL/SQL procedure successfully completed.

SQL> begin
  2    for r number in values of :rc loop
  3      dbms_output.put_line(r);
  4    end loop;
  5  end;
  6  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

And even with more complex statements we don’t need to worry about the collection definitions etc


SQL> variable rc refcursor
SQL> exec open :rc for select empno, ename from emp;

PL/SQL procedure successfully completed.

SQL> declare
  2    type rec is record ( r1 number, r2 varchar2(30));
  3  begin
  4    for r rec in values of :rc loop
  5      dbms_output.put_line(r.r1||','||r.r2);
  6    end loop;
  7  end;
  8  /
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLER

PL/SQL procedure successfully completed.

Rest assured, this blog post is just scratching the surface of the improvements to iteration code in PL/SQL in 21c. Check out the docs for more examples and more keywords that let you do even more. Dynamic SQL within a FOR-LOOP? No problem!

So here’s my challenge to you. Take a fresh look at PL/SQL because it’s power to manipulate sets of data in a procedural language is unmatched.

Enjoy!

Office Hours January 21 Addenda

Does PAIRS work with INDEX BY VARCHAR2? Yes!


SQL> set serverout on
SQL> declare
  2    type str_list is table of int index by varchar2(20);
  3    s1   str_list;
  4    s2   str_list;
  5  begin
  6    s1 := str_list('a'=>10,'b'=>20,'c'=>30,'d'=>40,'e'=>50,
  7                   'f'=>60,'g'=>70,'h'=>80,'i'=>90,'j'=>100);
  8
  9    s2 := str_list(for i in indices of s1 => s1(i));
 10
 11    for x,y in pairs of s2 loop
 12        dbms_output.put_line(x||','||y);
 13    end loop;
 14  end;
 15  /
a,10
b,20
c,30
d,40
e,50
f,60
g,70
h,80
i,90
j,100

PL/SQL procedure successfully completed.

Can the iterand be repeated, ie, can values overlap? Yes!


SQL> set serverout on
SQL> begin
  2    for i in 1 .. 10, repeat i-1 while i>3
  3    loop
  4        dbms_output.put_line(i);
  5    end loop;
  6  end;
  7  /
1
2
3
4
5
6
7
8
9
10
9
8
7
6
5
4

PL/SQL procedure successfully completed.

3 comments

  1. Awesome features perfectly explained. I see a bit of Java streams, python list comprehension and a bit of lambdas being introduced into my beloved language. Thanks Connor.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.