Oracle PL/SQL - Indexing Associative Arrays

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Associative Arrays

Introduction

Indexing associative arrays by VARCHAR2 can hold complex hash algorithms.

The following code generates a list of emp grouped by department and quarter when they were hired.

Demo

SQL>
SQL> drop table dept;

Table dropped.-- w  w w .j  a va  2s . com

SQL> create table dept(
  2    deptno number(2,0),
  3    dname  varchar2(14),
  4    loc    varchar2(13),
  5    constraint pk_dept primary key (deptno)
  6  );

Table created.

Elapsed: 00:00:00.02
SQL>
SQL> insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
SQL> insert into dept values(20, 'RESEARCH', 'DALLAS');
SQL> insert into dept values(30, 'SALES', 'CHICAGO');
SQL> insert into dept values(40, 'OPERATIONS', 'BOSTON');
SQL>
SQL> drop table emp;

Table dropped.
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL> declare
  2        type list_aa is table of VARCHAR2(2000) index by VARCHAR2(256);
  3        v_list_aa list_aa;
  4
  5        cursor c_dept is select deptNo from dept order by deptNo;
  6        cursor c_emp is select eName, deptNo,to_char(hireDate,'q') q_nr from emp;
  7        v_subscript_tx VARCHAR2(256);
  8  begin
  9      for r_dept in c_dept loop
 10         v_list_aa(r_dept.deptNo||'|1'):='Q1 Dept#'||r_dept.deptno||':';
 11         v_list_aa(r_dept.deptNo||'|2'):='Q2 Dept#'||r_dept.deptno||':';
 12         v_list_aa(r_dept.deptNo||'|3'):='Q3 Dept#'||r_dept.deptno||':';
 13         v_list_aa(r_dept.deptNo||'|4'):='Q4 Dept#'||r_dept.deptno||':';
 14      end loop;
 15
 16     for r_emp in c_emp loop
 17       v_list_aa(r_emp.deptNo||'|'||r_emp.q_nr):=
 18           v_list_aa(r_emp.deptNo||'|'||r_emp.q_nr)||' '||r_emp.eName;
 19     end loop;
 20
 21    v_subscript_tx:=v_list_aa.first;
 22     loop
 23        DBMS_OUTPUT.put_line(v_list_aa(v_subscript_tx));
 24        v_subscript_tx:=v_list_aa.next(v_subscript_tx);
 25        exit when v_subscript_tx is null;
 26     end loop;
 27  end;
 28  /
Q1 Dept#10:
Q2 Dept#10:
Q3 Dept#10:
Q4 Dept#10: KING
Q1 Dept#20:
Q2 Dept#20:
Q3 Dept#20:
Q4 Dept#20:
Q1 Dept#30:
Q2 Dept#30: BLAKE
Q3 Dept#30:
Q4 Dept#30:
Q1 Dept#40:
Q2 Dept#40:
Q3 Dept#40:
Q4 Dept#40:

PL/SQL procedure successfully completed.
SQL>

Related Topic