“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 
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.
Learning is not a spectator sport 
Leave a reply to vivek Cancel reply