Exploring the PAIRS OF iterator

21c introduced several new iterator syntax. I’ll explore each of them in a series of articles beginning with the (in my opinion) most interesting of them, the PAIRS OF iterator.

If you’ve used PL/SQL for a while you’ve probably created simple FOR loops iterating over some set of values, for example: 1 to 10.

BEGIN
    FOR i IN 1 .. 5
    LOOP
        DBMS_OUTPUT.put_line(i);
    END LOOP;
END;

And if you’ve used collections, you’ve probably written a loop with a construction something like this, iterating over the indices of the collection to get the corresponding values.

DECLARE
    TYPE number_tab IS TABLE OF NUMBER;

    t   number_tab := number_tab(2, 4, 6, 8, 10);
BEGIN
    FOR i IN t.FIRST .. t.LAST
    LOOP
        DBMS_OUTPUT.put_line(i || '=>' || t(i));
    END LOOP;
END;

1=>2
2=>4
3=>6
4=>8
5=>10

The PAIRS OF syntax of 21c allows you to do reference both the index and its associated value together, giving each of them their own iterator value. Using this iterator I could then rewrite the block above like this:

DECLARE
    TYPE number_tab IS TABLE OF NUMBER;

    t   number_tab := number_tab(2, 4, 6, 8, 10);
BEGIN
    FOR idx, val IN PAIRS OF t
    LOOP
        DBMS_OUTPUT.put_line(idx || '=>' || val);
    END LOOP;
END;

1=>2
2=>4
3=>6
4=>8
5=>10

The examples above use a nested table but it also works with varrays and associated arrays. In the example below I build a collection of numbers with their squares (i.e. a number multiplied by itself.)

DECLARE
    t   DBMS_SQL.number_table;
BEGIN
    FOR i IN 1 .. 5
    LOOP
        t(i) := i ** 2;
    END LOOP;

    FOR idx, val IN PAIRS OF t
    LOOP
        DBMS_OUTPUT.put_line(idx || '=>' || val);
    END LOOP;
END;

1=>1
2=>4
3=>9
4=>16
5=>25

The examples above all have numeric values but they don’t need to be. These examples show DATE and VARCHAR2 values as well.

DECLARE
    t   DBMS_SQL.date_table;
BEGIN
    FOR i IN 1 .. 10
    LOOP
        t(i) := SYSDATE + i;
    END LOOP;

    FOR idx, val IN PAIRS OF t
    LOOP
        DBMS_OUTPUT.put_line(idx || '=>' || val);
    END LOOP;
END;

1=>06-NOV-24
2=>07-NOV-24
3=>08-NOV-24
4=>09-NOV-24
5=>10-NOV-24
6=>11-NOV-24
7=>12-NOV-24
8=>13-NOV-24
9=>14-NOV-24
10=>15-NOV-24

DECLARE
    t   DBMS_SQL.varchar2_table;
BEGIN
    FOR i IN 1 .. 10
    LOOP
        t(i) := TO_CHAR(TO_DATE(i, 'j'), 'jsp');
    END LOOP;

    FOR idx, val IN PAIRS OF t
    LOOP
        DBMS_OUTPUT.put_line(idx || '=>' || val);
    END LOOP;
END;

1=>one
2=>two
3=>three
4=>four
5=>five
6=>six
7=>seven
8=>eight
9=>nine
10=>ten

Even better, the indexes need not be numeric either. Associative Arrays can have text-based indexes and PAIRS OF will support those as well. Here, I generate a list of tablespaces with the number of bytes allocated to each. The collection is indexed by the tablespace names.

DECLARE
    TYPE tablespaces_tab IS TABLE OF NUMBER
        INDEX BY VARCHAR2(30);

    t   tablespaces_tab;
BEGIN
    FOR x IN (
                   SELECT tablespace_name, SUM(bytes) bytes
                     FROM dba_data_files
                 GROUP BY tablespace_name
             )
    LOOP
        t(x.tablespace_name) := x.bytes;
    END LOOP;

    FOR idx, val IN PAIRS OF t
    LOOP
        DBMS_OUTPUT.put_line(idx || '=>' || val);
    END LOOP;
END;

APEX_TS=>629145600
SYSAUX=>1006632960
SYSTEM=>639631360
UNDOTBS1=>262144000
USERS=>39397359616

Iterating through Key-Value pairs is a common coding pattern and I really like this syntax to help keep my code clean as I process them without a lot of de-referencing syntax cluttering up the intent.

I hope you find them useful as well. Questions and comments, as always are welcome.