DBA Hub

📋Steps in this guide1/11

FOR LOOP Iteration Enhancements in Oracle Database 21c

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

oracle 21cconfigurationintermediate
by OracleDba
14 views
1

Pre-21c

Prior to Oracle database 21c the iteration control of a 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. Adding the 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. All the other sections in this article relate to functionality in Oracle 21c onward.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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>

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>
2

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>
3

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>
4

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. 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. We can also increment or decrement by fractional values using the stepped range iteration syntax.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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>

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>

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>
5

Single Expression Iteration

In its most basic form a single expression iteration is pretty useless, as it gives a single iteration. Where it becomes interesting is when we add the clause. The upper limit on the iteration is defined by the clause. In the following example each iteration increases the loop variable by multiplying the previous value by 5.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
begin
  for i in 2 loop
    dbms_output.put_line(i);
  end loop;
end;
/
2

PL/SQL procedure successfully completed.

SQL>

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>
6

Collection Iteration Control

The , and iteration controls derive their index values from collections, allowing us to easily deal with sparse collections. If you have used or with bulk operations, you will understand what they do. These examples use as the index, but these collection iteration controls also work with indexes.
7

VALUES OF

In the following examples we create an associative array (index-by table) and create three elements with sparse indexes. The 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. You can read more about those here . In previous releases we would have to do something like this to cope with a sparse collection.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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>

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>
8

INDICES OF

The iteration control outputs the indices of a collection, not the values. If we repeat the example from the previous section, but replace by , we see the index values are displayed. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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>

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>
9

PAIRS OF

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>
10

Skipping and Stopping

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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>

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>
11

FOR In Qualified Expressions

Oracle database 21c makes it even easier to populate collections using qualified expressions. The iterator syntax uses the keyword, and uses a similar syntax to a conventional . You can read more about the enhancements to qualified expressions here . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!