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.
Posts
0 comments:
Post a Comment