PL/SQL datastructure like Hashmap

Question

Is there a way in Oracle pl/sql that I can create a datastructure like java Hashmap which can have a record type as value and String as index.

For example;

 type emp_rec_type is record (emp_id number,emp_salary number);
 emp_rec emp_rec_type;

 type emp_assoc_array is table of emp_rec_type indexed by varchar2(30);
 emp_map emp_assoc_array;

I would like to be able to do the below

emp_rec.emp_id := 1;
   emp_rec.salary := 1000; 
   emp_map('Rohan') := emp_rec;

Can this be achieved? I cannot use nested tables since I can only index it by integer. With associative arrays, I cannot use object type as attributes.

Also would like to do this without having to create an object or schema datatype. Can you please suggest something?

Answer 1

In PL/SQL you can define associative arrays indexed by VARCHAR2 (since 10g I think):

SQL> DECLARE
  2     TYPE map_varchar IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
  3     l map_varchar;
  4  BEGIN
  5     l('A') := 'alpha';
  6     l('B') := 'beta';
  7     dbms_output.put_line(l('A'));
  8  END;
  9  /

alpha

Internally, I think the structure is more like a binary-tree map than an hashmap.

Of course you can store PL/SQL records or SQL objects:

SQL> DECLARE
  2     TYPE emp_rec_type IS RECORD (emp_id NUMBER,emp_salary NUMBER);
  3     TYPE map_varchar IS TABLE OF emp_rec_type INDEX BY VARCHAR2(30);
  4     l_emp emp_rec_type;
  5     l_map map_varchar;
  6  BEGIN
  7     l_emp.emp_id := 1;
  8     l_emp.emp_salary := 1000;
  9     l_map('A') := l_emp;
 10     -- you can also affect record values directly
 11     l_map('B').emp_id := 2;
 12     l_map('B').emp_salary := 2000;
 13     dbms_output.put_line(l_map('A').emp_salary);
 14  END;
 15  /

1000

PL/SQL procedure successfully completed