Monday, March 15, 2021

FOR LOOP Iteration Enhancements in Oracle Database 21c

Oracle Database Tutorial and Material, Oracle Database Exam Prep, Oracle Database Preparation, Oracle Database Certification

The FOR LOOP iteration control has been enhanced in Oracle database 21c.

◉ Pre-21c

Prior to Oracle database 21c the iteration control of a FOR LOOP was really basic. By default the loop variable, called "i" in these examples, increments by 1 from the lower boundary to the upper boundary. In the example below we see the loop steps up from 1 to 5.

set serveroutput on

begin

  for i in 1 .. 5 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

2

3

4

5

PL/SQL procedure successfully completed.

SQL>

Adding the REVERSE keyword makes the loop variable decrement by 1 from the upper boundary to the lower boundary. In the example below we see the loop steps down from 5 to 1.

begin

  for i in reverse 1 .. 5 loop

    dbms_output.put_line(i);

  end loop;

end;

/

5

4

3

2

1

PL/SQL procedure successfully completed.

SQL>

All the other sections in this article relate to functionality in Oracle 21c onward.

◉ Multiple Iterations

We can now string together multiple iteration boundaries as a comma separated list into a single loop definition. In the example below we step forward from 1 to 3, backward from 9 to 7, then forward from 20 to 22.

begin

  for i in 1 .. 3, reverse 7 .. 9, 20 .. 22 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

2

3

9

8

7

20

21

22

PL/SQL procedure successfully completed.

SQL>

◉ Stepped Range Iteration

We can now alter the increment or decrement of loop variable using a step. The following example steps forward from 1 to 5 in increments of 2, then steps back from 5 to 1 in decrements of 2.

begin

  for i in 1 .. 5 by 2, reverse 1 .. 5 by 2 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

3

5

5

3

1

PL/SQL procedure successfully completed.

SQL>

◉ Fractional Stepped Range Iteration

By default, fractional boundaries get rounded to keep the loop variable as an integer. This is true of prior releases also.

begin

  for i in 1.2 .. 2.2 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

2

PL/SQL procedure successfully completed.

SQL>

Oracle database 21c allows us to define fractional loop variables by explicitly typing the iterand, but the step is still an increment or decrement or 1 by default.

begin

  for i number(5,1) in 1.2 .. 2.2 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1.2

2.2

PL/SQL procedure successfully completed.

SQL>

We can also increment or decrement by fractional values using the stepped range iteration syntax.

begin

  for i number(5,1) in 1.2 .. 2.2 by 0.2 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1.2

1.4

1.6

1.8

2

2.2

PL/SQL procedure successfully completed.

SQL>

◉ Single Expression Iteration

In its most basic form a single expression iteration is pretty useless, as it gives a single iteration.

begin

  for i in 2 loop

    dbms_output.put_line(i);

  end loop;

end;

/

2

PL/SQL procedure successfully completed.

SQL>

Where it becomes interesting is when we add the REPEAT clause. The upper limit on the iteration is defined by the WHILE clause. In the following example each iteration increases the loop variable by multiplying the previous value by 5.

begin

  for i in 1, repeat i*5 while i < 1000 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

5

25

125

625

PL/SQL procedure successfully completed.

SQL>

◉ Collection Iteration Control

The VALUES OF, INDICES OF and PAIRS OF iteration controls derive their index values from collections, allowing us to easily deal with sparse collections. If you have used VALUES OF or INDICES OF with FORALL bulk operations, you will understand what they do.

These examples use PLS_INTEGER as the index, but these collection iteration controls also work with VARCHAR2 indexes.

     ◉ VALUES OF

In the following examples we create an associative array (index-by table) and create three elements with sparse indexes. The VALUES OF iteration control allows us to move through the collection easily, regardless of it being a sparse collection. The two examples do the same thing, but the second example uses a qualified expression to initialize the collections. 

declare

  type tab_t is table of pls_integer index by pls_integer;

  l_tab tab_t;

begin

  l_tab(1)   := 20;

  l_tab(10)  := 30;

  l_tab(100) := 40;

  for i in values of l_tab loop

    dbms_output.put_line(i);

  end loop;

end;

/

20

30

40

PL/SQL procedure successfully completed.

SQL>

declare

  type tab_t is table of pls_integer index by pls_integer;

  l_tab tab_t := tab_t(1 => 20, 10 => 30, 100 => 40);

begin

  for i in values of l_tab loop

    dbms_output.put_line(i);

  end loop;

end;

/

20

30

40

PL/SQL procedure successfully completed.

SQL>

In previous releases we would have to do something like this to cope with a sparse collection.

declare

  type tab_t is table of pls_integer index by pls_integer;

  l_tab tab_t := tab_t(1 => 20, 10 => 30, 100 => 40);

  l_idx pls_integer;

begin

  l_idx := l_tab.first;

  while l_idx is not null loop

    dbms_output.put_line(l_tab(l_idx));

    l_idx := l_tab.next(l_idx);

  end loop;

end;

/

20

30

40

PL/SQL procedure successfully completed.

SQL>

     ◉ INDICES OF

The INDICES OF iteration control outputs the indices of a collection, not the values. If we repeat the example from the previous section, but replace VALUES OF by INDICES OF, we see the index values are displayed.

declare

  type tab_t is table of pls_integer index by pls_integer;

  l_tab tab_t := tab_t(1 => 20, 10 => 30, 100 => 40);

begin

  for i in indices of l_tab loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

10

100

PL/SQL procedure successfully completed.

SQL>

This allows us to use an index collection to pull out the values of interest from another collection. In the following example we have a main collection with five entries, and an index collection containing the indexes we are interested in. Notice the values are set to NULL, as we aren't interested in the values. We loop through the index collection, and display the values from the main collection for those indexes.

declare

  type tab_t is table of varchar2(32767) index by pls_integer;

  l_main_tab  tab_t := tab_t(1 => 'ONE', 2 => 'TWO', 3 => 'THREE', 4 => 'FOUR', 5 => 'FIVE');

  l_idx_tab   tab_t := tab_t(1 => null, 3 => null, 5 => null);

begin

  for i in indices of l_idx_tab loop

    dbms_output.put_line(l_main_tab(i));

  end loop;

end;

/

ONE

THREE

FIVE

PL/SQL procedure successfully completed.

SQL>

     ◉ PAIRS OF

The PAIRS OF iteration control gives us access to both the index and the value. We can think of this as an INDICES OF and VALUES OF combined into one. The following example uses PAIRS OF to display the index "i" and value "v" for each entry in the collection.

declare

  type tab_t is table of varchar2(32767) index by pls_integer;

  l_tab  tab_t := tab_t(1 => 'ONE', 2 => 'TWO', 3 => 'THREE', 4 => 'FOUR', 5 => 'FIVE');

begin

  for i,v in pairs of l_tab loop

    dbms_output.put_line('index=' || i || '  value=' || v);

  end loop;

end;

/

index=1  value=ONE

index=2  value=TWO

index=3  value=THREE

index=4  value=FOUR

index=5  value=FIVE

PL/SQL procedure successfully completed.

SQL>

◉ Skipping and Stopping

The WHEN clause can be used to skip iteration variable values that do not match the condition. In the following example we use the range 1 to 10, but will only accept values that are divisible by 3.

begin

  for i in 1 .. 10 when mod(i,3)=0 loop

    dbms_output.put_line(i);

  end loop;

end;

/

3

6

9

PL/SQL procedure successfully completed.

SQL>

The WHILE clause stops the loop as soon as the condition is not met. In the following example we use a range from 1 to 10, but stop the loop if the iteration variable value is not less than or equal to 5. Clearly that's a silly example, but it allows us to see how the WHILE clause works.

begin

  for i in 1 .. 10 while i <= 5 loop

    dbms_output.put_line(i);

  end loop;

end;

/

1

2

3

4

5

PL/SQL procedure successfully completed.

SQL>

◉ FOR In Qualified Expressions

Oracle database 21c makes it even easier to populate collections using qualified expressions. The iterator syntax uses the FOR keyword, and uses a similar syntax to a conventional FOR LOOP.

Related Posts

0 comments:

Post a Comment