Oracle PL/SQL - Using Associative arrays

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

Introduction

An associative array is a collection of elements that use arbitrary numbers and strings for subscript values.

Syntax 

type AssocArray is table of ElementType index by binary_integer|pls_integer|VARCHAR2(size);

The subscript can be either Integer (BINARY_INTEGER and PLS_INTEGER ) or String (VARCHAR2 or any of its subtypes).

You cannot have two elements in the same collection with the same subscript.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w w  w .  ja va  2  s  .c  o m

Elapsed: 00:00:00.02
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(7369, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7499, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL>
SQL> declare
  2        type dept_rty is record(deptNo number, extra_tx VARCHAR2(2000));
  3        type dept_aa is table of dept_rty index by binary_integer;
  4        v_dept_aa dept_aa;
  5        cursor c_emp is
  6          select eName, deptNo
  7            from emp;
  8  begin
  9     v_dept_aa(10).deptNo:=10;
 10     v_dept_aa(20).deptNo:=20;
 11     v_dept_aa(30).deptNo:=30;
 12     for r_emp in c_emp loop
 13            v_dept_aa(r_emp.deptNo).extra_tx :=
 14                   v_dept_aa(r_emp.deptNo).extra_tx||
 15                        ' '||r_emp.eName;
 16     end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.
SQL>

Related Topic