PL/SQL bulk collect into associative array with sparse key

Question

I want to execute a SQL query inside PL/SQL and populate the results into an associative array, where one of the columns in the SQL becomes the key in the associative array. For example, say I have a table Person with columns

PERSON_ID   INTEGER      PRIMARY KEY
PERSON_NAME VARCHAR2(50)

...and values like:

 PERSON_ID  |  PERSON_NAME
 ------------------------
 6          |  Alice
 15         |  Bob
 1234       |  Carol

I want to bulk collect this table into a TABLE OF VARCHAR2(50) INDEX BY INTEGER such that the key 6 in this associative array has the value Alice and so on. Can this be done in PL/SQL? If so, how?

Answer 1

No, you have to use either 2 collections (id, name) or one whose element type is a record.

Here's a sample of the latter:

  cursor getPersonsCursor is
    SELECT ID, Name
    FROM   Persons
    WHERE  ...;

  subtype TPerson is getPersonsCursor%rowtype;
  type TPersonList is table of TPerson;
  persons TPersonList;
begin

open getPersonsCursor;
fetch getPersonsCursor
  bulk collect into persons;
close getPersonsCursor;

if persons.Count > 0 then
  for i in persons.First .. persons.Last loop
    yourAssocArray(persons(i).ID) := persons(i).Name;
  end loop;
end if;

Answer 2

If we want to specify the value in an associative array's index then we have to use this syntax:

SQL> declare
  2      type n_array is table of varchar2(30)
  3           index by binary_integer;
  4      emp_names n_array;
  5  begin
  6      for r in ( select ename, empno from emp )
  7      loop
  8          emp_names(r.empno) := r.ename;
  9      end loop;
 10
 11      dbms_output.put_line('count='||emp_names.count()
 12                               ||'::last='||emp_names.last());
 13      dbms_output.put_line(emp_names(8085));
 14
 15  end;
 16  /
count=19::last=8085
TRICHLER

PL/SQL procedure successfully completed.

SQL>

We can populate associative arrays with bulk collect but only if the index is an integer, and we are happy to index by (an implicit) ROWNUM, i.e not a sparse key...

SQL> declare
  2      type n_array is table of varchar2(30)
  3           index by binary_integer;
  4      emp_names n_array;
  5  begin
  6      select ename
  7      bulk collect into emp_names
  8      from emp ;
  9
 10      dbms_output.put_line('count='||emp_names.count()
 11                               ||'::last='||emp_names.last());
 12      dbms_output.put_line(emp_names(19));
 13
 14  end;
 15  /
count=19::last=19
FEUERSTEIN

PL/SQL procedure successfully completed.

SQL>

To be fair, if you need to use BULK COLLECT you are probably dealing with more data than is appropriate for an associative array.

Edit

A cheap-ish performance test of the two approaches:

SQL> declare
  2      type n_array is table of varchar2(30)
  3           index by binary_integer;
  4      emp_names n_array;
  5      s_time pls_integer;
  6      e_time pls_integer;
  7  begin
  8      s_time := dbms_utility.get_time;
  9      select ename
 10      bulk collect into emp_names
 11      from big_emp
 12      where rownum <= 500;
 13      dbms_output.put_line('bulk collect elapsed time = '
 14                              ||to_char(dbms_utility.get_time - s_time));
 15      s_time := dbms_utility.get_time;
 16      for r in ( select ename, empno from big_emp
 17                 where rownum <= 500 )
 18      loop
 19          emp_names(r.empno) := r.ename;
 20      end loop;
 21      dbms_output.put_line('sparse array elapsed time = '
 22                              ||to_char(dbms_utility.get_time - s_time));
 23  end;
 24  /

bulk collect elapsed time = 0
sparse array elapsed time = 0

PL/SQL procedure successfully completed.

SQL>

Wall-clock performance tests are notoriously ropey. But for a few hundred records, any difference is unlikely to be worth worrying about, certainly in the context of the sort of place where we might want to use an assoociative array.

Edit 2

@Dan said:

t seems to me that wanting to query a decent sized number of rows into a data structure that can be used for constant-time lookup ought to be a pretty common need

It really depends on your definition of "a decent sized number". Are there really that many cases where we would want to populate an associative array with thousands of rows, with a string index? When we get to those sorts of numbers a normal database table might be just as useful, especially on 11g Enterprise Edition with resultset caching.