MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PL/SQL Mimic Iterator

without comments

There’s no formal iterator in PL/SQL but you do have the ability of navigating a list or array with Oracle’s Collection API. For example, the following navigates a sparsely indexed collection from the lowest to the highest index value while skipping a missing index value:

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

The next one, navigates a sparsely indexed collection from the highest to the lowest index value while skipping a missing index value:

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;
END;
/

However, the next example is the most valuable because it applies to a PL/SQL associative array indexed by string values. You should note that the string indexes are organized in ascending order and assigned in the execution section of the program. This differs from the earlier examples where the values are assigned by constructors in the declaration section.

There’s no need to delete an element from the associative array because the string-based indexes are already sparsely constructed. A densely populated character index sequence is possible but not very useful, which is probably why there aren’t any examples of it.

Moreover, the following example is how you navigate a dictionary, which is known as an associative array in Oracle parlance (special words to describe PL/SQL structures). Unfortunately, associative arrays lack any utilities like Python’s key() method for dictionaries.

DECLARE
  /* Create a local associative array type. */
  TYPE list IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
 
  /* Define a variable of the associative array type. */
  lv_list  LIST; --  := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  VARCHAR2(5);
BEGIN
  /* Assign values to an associative array (PL/SQL structure). */
  lv_list('One') := 'Moe';
  lv_list('Two') := 'Shemp';
  lv_list('Three') := 'Larry';
  lv_list('Four') := 'Curly';
 
  /* Mimic iterator. */
  CURRENT := lv_list.FIRST;
  dbms_output.put_line('Debug '||CURRENT);
  WHILE NOT (CURRENT < lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

As always, I hope this example helps somebody solve a real world problem.

Written by maclochlainn

May 14th, 2021 at 4:50 pm

Posted in Linux,Microsoft Windows 10,Oracle,Oracle 12c,Oracle 18c,pl/sql

Tagged with ,

«
»