Forum Stats

  • 3,826,721 Users
  • 2,260,699 Discussions
  • 7,897,063 Comments

Discussions

Allow for iterating associative array key/value pairs

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited Apr 27, 2020 4:50PM in Database Ideas - Ideas

Looping over associative arrays can be a bit of a hassle in PL/SQL sometimes, especially if they're indexed by a varchar2 type.:

SET SERVEROUTPUT ON

DECLARE

  TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);

  l_arr arr_t;

  l_idx VARCHAR2(30);

BEGIN

  l_arr('a') := 'x';

  l_arr('b') := 'y';

 

  l_idx := l_arr.FIRST;

  WHILE l_idx IS NOT NULL LOOP

    dbms_output.put_line(l_idx || '=' || l_arr(l_idx));

    l_idx := l_arr.NEXT(l_idx);

  END LOOP;

END;

/

The FOR loop can define implicit loop variables without explicit type references, both when looping over numbers:

FOR i IN 1 .. 10 LOOP

   dbms_output.put_line(i);

END LOOP;

Or over implicit cursors:

FOR rec IN (SELECT 1 i FROM dual) LOOP

  dbms_output.put_line(rec.i);

END LOOP;

Why not also create such an implicit FOR loop for associative arrays? E.g. using this hypothetical syntax:

SET SERVEROUTPUT ON

DECLARE

  TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);

  l_arr arr_t;

BEGIN

  l_arr('a') := 'x';

  l_arr('b') := 'y';

 

  FOR key, value IN l_arr LOOP

    dbms_output.put_line(key || '=' || value);

  END LOOP;

  -- Alternative syntax akin to FORALL's syntax as suggested by Jeff Kemp: https://community.oracle.com/ideas/21601#comment-1026797

  FOR key IN INDICES OF l_arr LOOP

    dbms_output.put_line(key || '=' || l_arr(key));

  END LOOP;

END;

/

Quite a few other languages have this feature, including PHP:

foreach ($arr as $key => $value) {

  echo $key;

}

Kotlin:

for ((key, value) in map) {

  ...

}

C#:

foreach(var item in myDictionary)

{

  foo(item.Key);

  bar(item.Value);

}

Java:

for (Entry<K, V> entry : map.entrySet()) {

   System.out.println(entry.getKey());

   System.out.println(entry.getValue());

}

Newer versions of ECMAScript:

Object.entries(obj).forEach(

  ([key, value]) => console.log(key, value)

);

UPDATE: This has been implemented in Oracle 20c.

Example 6-9 Index Iterator Choice Association in Qualified Expressions

This example creates a copy of vec with values incremented by N.

result := vec_t (FOR I,j IN PAIRS OF vec INDEX I => j+n); 

This example creates a vector of the first N even numbers.

result := vec_t (FOR i IN 2.n BY 2 INDEX i/2 => i); 
Lukas EderJW_Veldhuis-OracleErik van RoonPDVBVUser_N9EK3Franck PachotHolgerHAndrewSayerRafael PontePiotr WrzosekWilliam RobertsonMike KutzJeffrey Kemp3503851Sven W.blessed DBAOleh TyshchenkoFranck NGregVThorsten Kettnerulohmannmoc-moc-a-moc
22
22 votes

Delivered · Last Updated

Comments

  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    Nice idea. Perhaps the syntax could be similar to forall<code>, e.g.

        for i in indices of somecollection loop

    Upvoted. Agreed with William, the syntax can be kept simple and consistent with other syntax in PL/SQL, and we don't need a special "value" placeholder.

    SET SERVEROUTPUT ON

    DECLARE

      TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);

      l_arr arr_t;

    BEGIN

      l_arr('a') := 'x';

      l_arr('b') := 'y';

      FOR key IN INDICES OF l_arr LOOP

        dbms_output.put_line(key || '=' || l_arr(key));

      END LOOP;

    END;

    /

    Lukas Eder
  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    Upvoted. Agreed with William, the syntax can be kept simple and consistent with other syntax in PL/SQL, and we don't need a special "value" placeholder.

    SET SERVEROUTPUT ON

    DECLARE

      TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);

      l_arr arr_t;

    BEGIN

      l_arr('a') := 'x';

      l_arr('b') := 'y';

      FOR key IN INDICES OF l_arr LOOP

        dbms_output.put_line(key || '=' || l_arr(key));

      END LOOP;

    END;

    /

    Very cool, I wasn't aware of that syntax. That should definitely be reused in the FOR LOOP, for consistency reasons. I'll update my suggestion.

  • blessed DBA
    blessed DBA Member Posts: 218

    Nice idea. Perhaps the syntax could be similar to bulk collection<code>, e.g.

    SELECT employee_id

    BULK COLLECT INTO l_employee_ids

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Nice idea. Perhaps the syntax could be similar to bulk collection<code>, e.g.

    SELECT employee_id

    BULK COLLECT INTO l_employee_ids

    blessed DBA wrote:Nice idea. Perhaps the syntax could be similar to bulk collection<code>, e.g.SELECT employee_idBULK COLLECT INTO l_employee_ids

    I’m not following. What would the loop look like then?

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    Alternative syntax akin to FORALL’s syntax as suggested by Jeff Kemp

    You’re welcome 

  • The lack of this feature makes it dangerous to loop through associative arrays.

    If you want to use CONTINUE, you need to remember to duplicate your "loop increment" logic, or you'll generate an infinite loop:

    SET SERVEROUTPUT ON

    DECLARE

      TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);

      l_arr arr_t;

      l_idx VARCHAR2(30);

    BEGIN

      l_arr('a') := 'x';

      l_arr('b') := 'y';

      l_idx := l_arr.FIRST;

      WHILE l_idx IS NOT NULL LOOP

        -- skip anything beginning with x

        CONTINUE WHEN l_arr(l_idx) LIKE 'x%'; -- looks reasonable, but ruh-roh! infinite loop!

        dbms_output.put_line(l_idx || '=' || l_arr(l_idx));

        l_idx := l_arr.NEXT(l_idx);

      END LOOP;

    END;

    /

    The correct loop would of course be:

      l_idx := l_arr.FIRST;

      WHILE l_idx IS NOT NULL LOOP

        -- skip anything beginning with x

        IF l_arr(l_idx) LIKE 'x%'

        THEN

            l_idx := l_arr.NEXT(l_idx);

            CONTINUE;

        END IF;

        dbms_output.put_line(l_idx || '=' || l_arr(l_idx));

        l_idx := l_arr.NEXT(l_idx);

      END LOOP;

    ... but then you are having to duplicate your "increment" code, which is also Not A Good Thing.

    If you want to avoid duplicating your code, you can (eg) raise and trap an exception inside your loop, or use a GOTO, but then you're not using CONTINUE, which is meant to be the tool for this job.

    There should be a way to set up a safe, implicit, once-over loop for an associative array, to protect us from ourselves. IN INDICES OF would be perfect.