Co-authored by Charles Wetherell from the
PL/SQL development team
Populating and looping through sparse arrays in
PL/SQL can be tricky. Suppose, for example, that you want to:
The naive code you might write could look like Listing 1; a lot of code for a simple task.
declare evens dbms_sql.number_table; inx pls_integer; begin for i in 1 .. 10 loop if mod (i, 2) = 0 then evens (i) := i; end if; end loop; inx := evens.first; while inx is not null loop dbms_output.put_line ('Val = ' || evens( inx )); inx := evens.next ( inx ); end loop; end;
Extended iterators, introduced in Oracle Database 21c, make this task much easier. The new iterator options give significantly more control over loops. Even better, qualified expressions with iteration controls make populating arrays a breeze. This post explores how all these new features work.
Before we begin let's recap the terminology for parts of loops. In the loop statement
for i in 1 .. 10 loop
i is called the iteration variable or iterand. Iterands are sometimes called index variables because they commonly index a collection. The clause
1 .. 10 is called an iteration control. Most of the fun happens because of new iteration control features added to the old vanilla standby
1 .. 10.
Let’s start by using custom increments to iterate over only even values. Iteration variables are implicitly of type
pls_integer with an increment of one. Before Oracle Database 21c, you could not change this. Now you can override the default. To loop over only the even values, for example, add
by 2 after the value range.
begin for even in 2 .. 10 by 2 loop dbms_output.put_line ( even ); end loop; end;
You can specify fractional increments! To be useful, the index variable should be a non-integer numeric type. Another new feature is that the iterand can be given an explicit type and that type can be anything you choose. The
number type works well for decimal computations. The output is what would be expected.
begin for val number in 0 .. 1 by 0.2 loop dbms_output.put_line ( 'Val = ' || to_char (val , '0.9') ); end loop; end; -- The Output Val = 0.0 Val = 0.2 Val = 0.4 Val = 0.6 Val = 0.8 Val = 1.0
The loop can go in the other direction as well. Just change the loop statement from
for val number in 0 .. 1 by 0.2 loop
for val number in reverse 0 .. 1 by 0.2 loop
and the iterand
val will step 1.0, 0.8, 0.6, . . . , 0.0. But take care that loop bounds and the step are still in the same order as for the original loop.
Some readers may try to understand by doing. So you may be running the examples as you go. Maybe instead of cutting and pasting, you are retyping the code. If you do that and make the tiny mistake of leaving out the data type
number on the iteration variable, the code looks like Listing 4. What will happen when it runs?
begin for val in 0 .. 1 by 0.2 loop -- Where’s the number ? dbms_output.put_line ( 'Val = ' || to_char (val , '0.9') ); end loop ; end;
The example raises a
VALUE_ERROR exception! Why? Because the elements of the iteration control are all converted to the iteration variable type – that type is
pls_integer by default. But 0.2 converts to zero and there is a rule for the step in the by clause: the value must be strictly positive. Zero is not strictly positive and that triggers the exception.
Don’t feel bad. That particular rule about by clauses was (re-)discovered while writing this post.
The iteration control works well if you want to cover all the values evenly spaced in some range. But what if you want to bypass or skip over some of those values? This block covers the integers from 1 to 5 but skips over 3. The
when clause allows the loop body to be executed when its predicate is true; otherwise, the whole body is skipped and processing for the next iterand value begins.
begin for val in 1 .. 5 when val <> 3 loop dbms_output.put_line ( 'Val = ' || val ); end loop ; end; -- The output Val = 1 Val = 2 Val = 4 Val = 5
Combined with a stepped increment, skipped values can solve FizzBuzz style challenges, such as
Display all the numbers in a range that have some common factors.
For example, you can output all the integers up to 30 that are multiples of both 3 and 5 by
begin for fizzbuzz35 in 0 .. 30 by 5 when mod ( fizzbuzz35 , 3) = 0 loop dbms_output.put_line ( 'Buzz ! ' || fizzbuzz35 ); end loop; end; -- The output Buzz ! 0 Buzz ! 15 Buzz ! 30
To summarize the new stepping iteration control,
But what if you want to use an expression, rather than a literal, to drive the loop? For example, what if you want to display all the powers of 2 between 1 and 100?
You could iterate through all the values in the range and use the
when clause to check each value. But there are only seven powers in the range. Clearly, looping through every value to return a few is inefficient. Instead, use a repeated expression to control the loop. Even better, use two expressions in combination.
The stepping iteration control used up to now defines a sequence of values. The lower and upper bounds of the sequence and the step are set when the iteration begins and the elements of the sequence form an arithmetic progression. The powers 2 are not an arithmetic sequence. You can use a simple expression to compute the sequence.
begin for power in 1, power * 2 loop dbms_output.put_line ( 'Val = ' || power ); end loop; end; -- The output Val = 1 Val = 2
The loop in Listing 7 has some interesting new features.
There are two distinct iteration controls separated by a comma. The rule is that there can be multiple controls and each one runs after the previous one finishes.
The iterand – in this example,
power – can be used in a loop control. The iterand is initialized to null before the first loop control.
This loop only prints two values: 1 and 2. What happened to the rest of the powers of 2? The keyword
repeat before an expression sticks on that control and evaluates it over and over again. To keep the control from running forever – or, in this case, from raising a
VALUE_ERROR exception – add a
while stopping condition. Listing 8 has the finished loop.
begin for power in 1, repeat power * 2 while power < 100 loop dbms_output.put_line ( 'Val = ' || power ); end loop; end; -- The output Val = 1 Val = 2 Val = 4 Val = 8 Val = 16 Val = 32 Val = 64
while clause stops any iteration control and moves on to the next control whenever its predicate is not true – in other words, when the predicate is false or null.
These are the new loop tools you have just seen.
By default, non-cursor iterands are immutable. If you try to assign a value to them within the loop, you will see a compiler error message. But the
mutable keyword now allows you to modify the iterand inside the loop. Listing 9 uses a mutated iterand rather than a repeated iteration control expression to list the powers of 2.
begin for power mutable in 1 .. 100 loop dbms_output.put_line ( 'Val = ' || power ); power := power * 2 - 1; end loop ; end;
There are two interesting details in this loop.
poweris doubled and then one is subtracted from it. The step loop control will automatically add the one back at the next cycle of the loop. See what the output is if one is not subtracted.
poweris more than 100. The control does not care whether the iterand grew too big because of the control itself or because of your new code modifying the iterand inside the loop: it stops in either case!
Take care when using mutable iterands. It is easy to end up with an infinite loop. Combining mutability with stepped or expression control can lead to unexpected outcomes if you're not careful. Remember the point about subtracting one in the powers of 2 example.
You should avoid mutable iterands unless they are the only way you can make a clean solution for some problem.
Even then, take care to comment so that you remember what you did. Most
PL/SQL programmers will automatically assume that the iterand is immutable and may not notice that sneaky little word
As Listing 1 showed, earlier releases of Oracle Database required a while loop to iterate neatly through sparsely populated arrays. This is a lot of code for a basic task. Extended iterators improve these iterations by using the collection itself to drive the loop. This has these options:
Indices of The
indices of control walks through the indices of a collection from first to last.
Values of The
values of controls walks through the element values of a collection from first to last.
Pairs of The
pairs of control walks through a collection and provides both the index and the value of each element. This control also introduces a second iterand into the loop definition.
declare arr dbms_sql.number_table; begin for inx in 1 .. 3 loop arr ( inx *2 ) := -inx * 3; end loop; for ind in indices of arr loop dbms_output.put_line ( 'Index = ' || ind ); end loop; for val in values of arr loop dbms_output.put_line ( 'Value = ' || val ); end loop; end; -- The Output Index = 2 Index = 4 Index = 6 Value = -3 Value = -6 Value = -9
The step control fills the array – sparsely – and the two loop with array controls report the array without any of the while loop mess. The
pairs of control makes output even easier by combining both of the previous output loops. It also insures that the index and the value cannot be out of sync. Listing 11 shows how it works.
declare arr dbms_sql.number_table; begin for inx in 1 .. 3 loop arr ( inx * 2 ) := -inx * 3; end loop ; for inx, val in pairs of arr loop dbms_output.put_line ( 'Arr(' || inx || ') = ' || val ); end loop; end; -- The Output Arr (2) = -3 Arr (4) = -6 Arr (6) = -9
PL/SQL for-cursor loop (show in Listing 12) has been in the language almost from the beginning. The new iteration controls do not require a for-cursor. Instead, you can use the
values of control for a
SQL statement, cursor object, cursor variable, or
SQL– essentially, this last is for an
execute immediate statement. The for-cursor loop from Listing 12 is slightly simpler when written with a loop control as shown in Listing 13 and it has the same output.
begin for rws in ( select level c1 from dual connect by level <= 3 ) loop dbms_output.put_line ( rws.c1 ); end loop ; end; -- The Output 1 2 3
begin for rws in values of ( select level c1 from dual connect by level <= 3 ) loop dbms_output.put_line ( rws.c1 ); end loop; end;
declare stmt varchar2 (100) := 'select level c1 from dual connect by level <= 3'; begin for rws integer in values of ( execute immediate stmt ) loop dbms_output.put_line ( rws ); end loop; end;
The same example can run using
execute immediate as seen in Listing 14. The code is trivial here, but if the
SQL statement had to be constructed during execution, this mechanism allows you to use it in a loop.
There is one little detail. The data type for the return value of an
execute immediate control is not obvious. In the example, it is
integer and so the iteration variable
rws needs to be given an explicit type. The
SQL statement control does not need this help because of an exception to the general rule that the iteration variable defaults to
pls_integer. Instead, it follows the rule for cursor-for loops and infers the iterand type as a record derived from the rows returned by the selection.
SQL loop controls are really just variations on the
values of controls seen before. They are far too powerful to explore completely here, but a few points may help you think of ways to use them.
SQL statements can be used with the
indices of and
pairs of controls as well.
whenclause can be used to skip loop cycles with a
SQLcontrol, just as with the other controls. The row will be retrieved from the data base but simply not used in the loop body because the body does not execute.
whileclause can be used to stop a
SQLcontrol, just as with other controls.
SQLcontrols can be combined with other controls – more to come.
execute immediateversion doesn’t have to be defined in advance as a string. It can be built using any necessary operations that will create a text value right inside the control itself.
SQLstatement – barring a perfectly specified
whenclause inside the statement itself – does not return its values in any particular order. So if a
SQLcontrol is executed twice, it may go through the values in a different order the second time. Be aware of the nondeterminism. Usually it doesn’t matter, but assuming a particular order for
SQLcontrol is hiding place for hard to diagnose bugs.
The new iterator controls have one more trick – the ability to use many different controls in a single loop. This chaining was used without much explanation in Listing 8 to compute the powers of 2. Listing 15 shows a loop that
declare arr dbms_sql.number_table ; begin arr (10) := 3; arr (20) := 5; arr (30) := 7; for i in reverse 1 .. 3, repeat i*2 while i <= 8, indices of arr loop dbms_output.put_line ( 'Val = ' || i ); end loop ; end; -- The Output Val = 3 Val = 2 Val = 1 Val = 2 Val = 4 Val = 8 Val = 10 Val = 20 Val = 30
One little point matters here. When one control finishes, the last iterand value that might have gone through the loop is used to start the next control. So the first step control ends with an iterand value of 1 and the second
repeat control sees that
i has that value when it starts and so produces a new value of 2 for its first cycle.
By itself, this is not a very convincing example. Another might help. For a positive number – say, A – the square root can be calculated quickly using Newton’s method.
Just start with a possible root approximation r of one half A and then repeatedly compute a new value of r as (r + A/r )/2. When r × r gets close enough to A, then that is the root. Listing 16 shows a simple square root function.
function root (A in number ) return number is begin for r number in A/2.0 when false , repeat ( r + A/r )/2.0 while abs ( r*r-A ) > A * 1.0E-6 when false , r loop return r; end loop; end;
root function puts the iteration control tricks of the last example to good use.
A/2.0initializes the root approximation to one half of the input
A. A more elaborate initialization could be written here. Consult your local friendly numerical analyst.
whenclause on the first control keeps it from entering the loop body. As soon as the loop body is executed, the function will return the current value of
r. It shouldn’t be done too soon.
whileclause keeps it running until the difference suggests that the root has at least 6 good decimal digits – probably good enough for practical purposes. Again, a math Ph.D. might be able to help out here if a better approximation is needed.
whenclause that keeps it from entering the loop body. This shows that both kinds of clause can apply to the same control.
rstanding alone. This is the necessary root value and because there is nothing to stop it, this value enters the loop body and is promptly returned as the value of the whole function. Job done!
In Oracle Database 18c,
PL/SQL added qualified expressions which act like constructors for most types – especially arrays and records. Now that the new iteration controls have come along, array qualified expressions gain much more elegance and power.
By the way, you will sometimes hear a qualified expression called an aggregate which is the ADA name for the inner value defining part. Aggregate is never used by
PL/SQL, but if you want to use it as shorthand, everybody will understand you.
Listing 17 shows a basic qualified expression defining an array of numbers. Each array element has an index (to the left of the =>) and a value. This particular array has elements 1 to 5 filled with the values 1 to 5.
declare arr dbms_sql.number_table; begin arr := dbms_sql.number_table ( 1 => 1, 2 => 2, 3 => 3, 4 => 4, 5 => 5 ); end;
When there are only a few values to put in the array or when the positions to put them in are “random”, the explicit listing works well. Listing 18 shows a simpler way to fill this same array. This example sets the array elements to the same value as the index.
declare arr dbms_sql.number_table; begin arr := dbms_sql.number_table ( for i in 1 .. 5 => i ); end;
What if you wanted the value to be the square of the index? You can write an arbitrary expression to the right of the =>; in this case, the right expression is
i*i to make the assignment look like this:
arr := dbms_sql.number_table ( for i in 1 .. 5 => i*i );
Or perhaps you want an array where the index value is a square and the element value is square root. Then you can write an arbitrary indexing expression using the keyword index like this:
arr := dbms_sql.number_table ( for i in 1 .. 5 index i*i => i );
This last version would create these array values.
arr(1) = 1; arr(4) = 2; arr(9) = 3; arr(16) = 4; arr(25) = 5
declare arr dbms_sql.number_table := dbms_sql.number_table ( for i in 0 .. 4 by 2, 18 .. 20, repeat i*2 while i < 100 => i ); begin for i, v in pairs of arr loop dbms_output.put_line ( 'arr(' || i || ') = ' || v ); end loop ; end; -- The Output arr (0) = 0 arr (2) = 2 arr (4) = 4 arr (18) = 18 arr (19) = 19 arr (20) = 20 arr (40) = 40 arr (80) = 80
Array qualified expressions can use the full power of iteration controls: custom increments, repeat expressions,
while clauses, whatever you like. Listing 19 shows several loop controls in one qualified expression. Because a qualified expression may be used wherever a value is needed, the code is shorter and clearer if the
arr variable is just initialized directly. The third loop control takes advantage of the fact that next iteration starts with the last value (20) of the iterand
i, a trick that was used before. The output loop takes uses the
pairs of control. Because all the values are integral, it is fine that the iterands
v are left with their default type of
You may well have faced the common problem of combining two arrays into one. There are two obvious ways to combine arrays: append the values of one array to the other or merge the arrays while finding a way to combine the individual elements when they happen to fall at the same index. Listing 20 shows how appending arrays could work.
declare arr1 dbms_sql.number_table := dbms_sql.number_table ( for i in 1 ..3 index i*2 => i ); arr2 dbms_sql.number_table := dbms_sql.number_table ( for i in 10 .. 12 => i ); appended dbms_sql.number_table := dbms_sql.number_table ( for v in values of arr1 , values of arr2 sequence => v ); begin for i, v in pairs of appended loop dbms_output.put_line ( 'Appended (' || i || ') = ' || v ); end loop ; end; -- The Output Appended (1) = 1 Appended (2) = 2 Appended (3) = 3 Appended (4) = 10 Appended (5) = 11 Appended (6) = 12
This example uses some tricks from before and one new feature.
appendeduses the keyword
sequenceto tell the qualified expression that indices are to be assigned in order starting with the first input array.
Merging two arrays is more complicated. What should be done if both arrays have an element at some index? The merge mechanism has to make a choice. Listing 21 chooses to let the second element overwrite the first one.
declare arr1 dbms_sql.number_table := dbms_sql.number_table ( for i in 1 ..3 => i ); arr2 dbms_sql.number_table := dbms_sql.number_table ( for i in 3 .. 6 => 2*i ); merged dbms_sql.number_table := dbms_sql.number_table ( for i, v in pairs of arr1, pairs of arr2 index i => v ); begin for i, v in pairs of merged loop dbms_output.put_line ( 'Merged (' || i || ') = ' || v ); end loop ; end; -- The Output Merged (1) = 1 Merged (2) = 2 Merged (3) = 6 Merged (4) = 8 Merged (5) = 10 Merged (6) = 12
The tricky part is in the construction of the merged array. The first
pairs of control simply copies the elements of
arr1 into the merged output array. The second
pairs of control does the same thing for the second input array
arr2. The index keyword allows elements of the second array to “land on top of” already copied elements from the first array. In the output, the value of
merged(3) is 6 from the second array, not 3 from the first. This is exactly what was needed. A clever and elegant solution provided by qualified expressions and iteration controls.
Qualified expressions hitched up with loop controls can do this another way as shown in Listing 23. The output is the same.
declare arr dbms_sql.number_table ; begin select level c1 bulk collect into arr from dual connect by level <= 3; for v number in values of arr loop dbms_output.put_line (v); end loop ; end;
declare cursor cur is select level c1 from dual connect by level <= 3; type cur_arr is table of cur%rowtype index by pls_integer; a cur_arr ; begin a := cur_arr ( for rws in cur index rws.c1 => rws ); for v in values of a loop dbms_output.put_line ( v.c1 ); end loop ; end;
The new way doesn’t seem much better than bulk
SQL at a glance. But there is a critical difference. Bulk collection produces densely packed arrays with the index starting at one.
With qualified expressions, you can use the selected values’ fields to construct an index.
In particular, you can use a
varchar2 value as an index for your array. This example seen in Listing 24 produces an array indexed by the letters A to C.
declare cursor cur is select chr( level + 64) c1 , level c2 from dual connect by level <= 3; type cur_arr is table of integer index by varchar2(1); a cur_arr ; begin a := cur_arr ( for rws in cur index rws.c1 => rws.c2 ); for i varchar2(1), v in pairs of a loop dbms_output.put_line ( 'a(' || i || ') = ' || v ); end loop ; end; -- The Output a(A) = 1 a(B) = 2 a(C) = 3
This example could be even shorter – and perhaps less readable. Listing 25 shows how.
These two examples wrap together many techniques.
%rowtype) for the arrays you build.
SQLiteration controls return (anonymous) record values for each row, you don’t have to save those. Instead, you can use the values from the rows returned to build new index and element values of your own.
SQLiteration control could have used in the examples as well.
declare type cur_arr is table of integer index by varchar2 (1); begin for i varchar2 (1), v in pairs of cur_arr ( for rws in ( select chr( level + 64 ) c1 , level c2 from dual connect by level <= 3 ) index rws.c1 => rws.c2 ) loop dbms_output.put_line ( 'a(' || i || ') = ' || v ); end loop ; end;
Some of the examples shown here seem slightly far-fetched. Perhaps a real-life example or two would be helpful. Here are some fragments from a working program that manipulates text. Listing 26 provides the setup and the first example. The usual apparatus of complete begin and end and runnable code will be eliminated so the examples are not so overwhelming.
There are some things to notice about the example.
-- Some type declarations . type C_Freq is table of pls_integer index by pls_integer ; subtype H_Code is varchar2 (30); type T_Node is record ( Is_Leaf boolean , Int_Chr pls_integer , Weight pls_integer , Code H_Code , Left pls_integer , Right pls_integer ); type HTrie is table of T_Node index by pls_integer ; -- One variable declaration . Trie HTrie ; -- The problem is to create an HTrie called Trie from a C_Freq -- array called Freqs . Trie := HTrie ( for c, f in pairs of Freqs when f > 0 sequence => T_Node ( Is_Leaf => TRUE , Int_Chr => c, Weight => f, Code => null , Left => 0, Right => 0 ) );
Freqsis an actual argument of the surrounding function so it does not need a declaration here.
Freqsmay have a value of zero. If so, no
T_Noderecord should be created for them. The
whenclause handles this neatly without a bulky
HTrievalue is tightly packed because of the sequence keyword.
forloop, an if statement, and probably several assignment statements to set the fields of each
T_Noderecord. Much neater!
HTrie value produced is manipulated further and more nodes are added in a complicated algorithm that runs until there is nothing left to do. Classical expositions of this algorithm typically use while loops and complicated tests to end them. With the iteration controls, the loop is easy to write:
for items in repeat Back - Front + 1 + Tail - Added + 1
while items > 1 loop
The inside of the loop can change any of the variables mentioned in the loop control. But the control itself just says “keep going so long as there are at least two elements left to process”. Again, how neat.
There is one other small point that can easily be missed. Obviously the new loop controls and qualified expressions have more bells and whistles than older for loops. There are more things that might go wrong. In
PL/SQL, when something does go awry, usually an exception is raised. There was an example in Listing 4. What happens when an exception does occur?
PL/SQL already has a rule for this. The construct under execution is abandoned and an exception handler is sought. Before, loop control exceptions could only occur because the initial stepping setup was wrong somehow; the loop would not have started execution yet. A cursor-for loop might raise an exception from the
SQL, but again that would usually happen before any loop execution.
With the new controls, some cycles of the loop may already have run before the exception occurs. For example, what if a when clause has a divide by zero error only after 10 or 20 steps have already been taken? In these cases, the existing loop cycles are over and done with. Whatever work they did stands. But when the exception fires, the entire loop stops and control heads off to the handler. This really is no different than if something in the body of the loop had raised an exception.
So long as you keep in mind that the more complicated controls do provide you with more opportunities not only for power but for errors, you will think clearly and get rid of those bugs before they ever have a chance to bite.
To wrap up, modern iteration controls and qualified expressions together do not provide any new computational power to
PL/SQL. Anything you could program with these new tools, you could have programmed before.
Iteration controls and qualified expressions make programs shorter and neater. They eliminate “working” and “temporary” variables. They control loops directly, in the loop itself, rather than in some complication logic buried inside the loop. They allow the use of “natural” loop index values. They provide the power of
SQL to drive loops and to build data structures. And, on top of everything else, they are fun to write.
Enjoy your new tool kit as
PL/SQL helps your applications march into the future.
Thanks to Charles Wetherell from the
PL/SQL development team for reviewing this post and adding his comments
We'd love to hear what you think about these new features and real-world use cases you have for them. Let us know in the comments!