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.