X

PL/SQL and edition-based redefinition:
the perfect marriage.

  • January 21, 2021

Better loops and qualified expressions (array constructors) in PL/SQL

Chris Saxon
Developer Advocate

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:

  • Create an array with indices and values of the even numbers between 1 and 10
  • Output the contents of the array

The naive code you might write could look like Listing 1; a lot of code for a simple task.

Listing 1: Even numbers

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

the variable 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.

Custom loop increments

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.

Listing 2: Loop with step of 2

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.

Listing 3: Loop with step of 0.2

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

to

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?

Listing 4: Mistyped loop with step of 0.2

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.

Skip iteration values

Image by Sharky from Pixabay

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.

Listing 5: Loop with skipped values

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

  • Setting the step to 5.
  • Skipping the values that aren’t a multiple of 3.

Listing 6: FizzBuzz for 3 and 5

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,

  • It can have a by clause that provides an increment other than 1.
  • In general, all three parts – the low bound, the high bound, and the step – can be arbitrary expressions that will be evaluated once before the control starts running.
  • If the low bound is greater than the high bound, the loop stops before it even starts.
  • The step expression value must be strictly positive.
  • The loop can run down from the high bound to the low bound using reverse.
  • The iterand can be any numeric type.

Repeat expression loops

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.

Listing 7: Powers of 2 loop

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.

Listing 8: Powers of 2 loop up to 100

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

The 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.

  • There can be many loop controls for one loop.
  • Each control runs after the last one finishes.
  • A single expression can be a loop control and it gives just one value to the loop iterand.
  • An expression can give multiple values by prefixing it with a repeat keyword.
  • A loop control – any of them – can use the iterand’s current value in the control’s operation.
  • A while clause can terminate a loop control. It can be used on any loop control, not just a repeat expression.

Mutable iteration variables

Photo by Charlie Belvin Designs from StockSnap

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.

Listing 9: Powers of 2 with mutation

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.

  1. Inside the loop, the iterand power is 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.
  2. The loop runs to 100 but only prints 7 values. The loop control automatically stops when power is 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 mutable!

Array iterators

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.

Listing 10: Indices of and values of controls

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 indices of and values of controls may be familiar from forall. Listing 10 has an ordinary step control and the two new controls.

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.

Listing 11: Pairs of controls

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

Loops can use SQL

The 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 PL/SQL dynamic 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.

Listing 12: Cursor-for loop

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

Listing 13: Loop with SQL control

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;

Listing 14: Loop with execute immediate control

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.

The 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.

To recap:

  • The when clause can be used to skip loop cycles with a SQL control, 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.
  • The while clause can be used to stop a SQL control, just as with other controls.
  • The SQL controls can be combined with other controls – more to come.
  • The execute immediate version 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.
  • Finally, remember that a SQL statement – barring a perfectly specified when clause inside the statement itself – does not return its values in any particular order. So if a SQL control 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 SQL control is hiding place for hard to diagnose bugs.

Control chaining

Image by TanteTati from Pixabay

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

  • counts backwards from 3 to 1,
  • computes the first 3 powers of 2 (starting at 2), and
  • reports the indices of a sparse array.

Listing 15: Loop with three controls

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.

Listing 16: Square root loop

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;

The root function puts the iteration control tricks of the last example to good use.

  • The iterand r must be typed number.
  • The first expression control A/2.0 initializes the root approximation to one half of the input A. A more elaborate initialization could be written here. Consult your local friendly numerical analyst.
  • The when clause 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.
  • The second repeat control computes better and better approximations.
  • The while clause 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.
  • The second control also has a when clause that keeps it from entering the loop body. This shows that both kinds of clause can apply to the same control.
  • Finally, the last expression control just has little r standing 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!

Qualified expressions

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.

Listing 17: Simple array qualified expression

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.

Listing 18: Qualified expression with step control

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

Listing 19: Qualified expressions with several controls

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 i and v are left with their default type of pls_integer.

Combining arrays

Image by Michael Gaida from Pixabay

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.

Listing 20: Append one array to another

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.

  • The arrays are all computed as qualified expressions and initialize the variable declarations.
  • Array arr1 uses the index mechanism.
  • The output loop uses the pairs of control.
  • And the new thing: the initialization of variable appended uses the keyword sequence to tell the qualified expression that indices are to be assigned in order starting with the first input array.
  • The output shows that this worked because the values are in the elements indexed by 1 through 6.
  • Because no elements from the two original arrays were skipped, the number of elements in the result is the sum of the number of elements in the two input arrays.

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.

Listing 21: Merge one array with another

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.

Using SQL to populate arrays

PL/SQL has been able to populate an array with a bulk SQL statement for a long time. Listing 22 shows how. The output is the integers 1 to 3 as in Listing 12.

Qualified expressions hitched up with loop controls can do this another way as shown in Listing 23. The output is the same.

Listing 22: Bulk SQL fills array

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;

Listing 23: Cursor fills array

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.

Listing 24: Cursor creates varchar2 index values

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.

  • A cursor is used to generate a qualified expression value.
  • A select statement is used to generate the same qualified expression value.
  • You may need to create a new type (for example, using %rowtype) for the arrays you build.
  • Although the SQL iteration 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.
  • You can nest qualified expressions in one another or in loop controls to provide values. An important design goal of qualified expressions is to reduce the number of “incidental” variables that you need to declare and define. Often, as in Listing 25, a qualified expression might be able replace the variable declaration, its assignment, and its use.
  • The code in Listing 25 is quite dense. On the other hand, it is less cluttered than the extra type and variable declarations and assignments of Listing 24. Personal taste and programming style will help you understand which is better for your own use.
  • A dynamic SQL iteration control could have used in the examples as well.

Listing 25: Cursor shorter varchar2 index

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;

A little more before you go

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.

Listing 26: First text processing 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
    )
  );
  • Types and subtypes need to be created to manage the data.
  • Only one variable declaration is needed so there is somewhere to put the result. No auxiliary variables are necessary.
  • The record values are created by an inner qualified expression; again, no extra variables.
  • In its real use, the variable Freqs is an actual argument of the surrounding function so it does not need a declaration here.
  • Some of the elements of Freqs may have a value of zero. If so, no T_Node record should be created for them. The when clause handles this neatly without a bulky if statement.
  • The final HTrie value is tightly packed because of the sequence keyword.
  • The single assignment to Trie replaced a for loop, an if statement, and probably several assignment statements to set the fields of each T_Node record. Much neater!

The 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.

Conclusion

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.

But!

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!

Featured image by Paul Brennan from Pixabay

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.