An associative array is a type of collection that associates a unique key with a value. The key does not have to be numeric but can be character data as well.
An associative array has the following characteristics:
- An associative array type must be defined after which array variables can be declared of that array type. Data manipulation occurs by using the array variable.
- When an array variable is declared, the associative array is created but is empty - just start assigning values to key values.
- The key can be any negative integer, positive integer, or zero if INDEX BY BINARY_INTEGER or PLS_INTEGER is specified.
- The key can be character data if INDEX BY VARCHAR2 is specified.
- The number of elements in the array has no pre-defined limit - it grows dynamically as elements are added.
- The array can be sparse - gaps may exist in the assignment of values to keys.
- An attempt to reference an array element that has not been assigned a value will result in an exception.
The TYPE IS TABLE OF ... INDEX BY statement is used to define an associative array type:
TYPE assoctype IS TABLE OF { datatype | rectype | objtype }
INDEX BY { BINARY_INTEGER | PLS_INTEGER | VARCHAR2(n) };
assoctype is an identifier assigned to the array type. datatype is a scalar data type such as VARCHAR2 or NUMBER. rectype is a previously defined record type. objtype is a previously defined object type. n is the maximum length of a character key.
To use the array, a variable must be declared with that array type. The syntax for declaring an array variable is as follows:
array assoctype
array is an identifier assigned to the associative array. assoctype is the identifier of a previously defined array type.
An element of the array is referenced by using the following syntax:
array(n)[.field ]
array is the identifier of a previously declared array. n is the key value, type-compatible with the data type given in the INDEX BY clause. If the array type of array is defined from a record type or object type, [.field ] must reference an individual field within the record type or attribute within the object type from which the array type is defined. Alternatively, the entire record can be referenced by omitting [.field ].
The following example reads the first ten employee names from the emp table, stores them in an array, and then displays the results from the array:
DECLARE
TYPE emp_arr_typ IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
emp_arr emp_arr_typ;
CURSOR emp_cur IS SELECT ename FROM emp WHERE ROWNUM <= 10;
i INTEGER := 0;
BEGIN
FOR r_emp IN emp_cur LOOP
i := i + 1;
emp_arr(i) := r_emp.ename;
END LOOP;
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(emp_arr(j));
END LOOP;
END;
The above example produces the following output:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
The previous example is now modified to use a record type in the array definition:
DECLARE
TYPE emp_rec_typ IS RECORD (
empno NUMBER(4),
ename VARCHAR2(10)
);
TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER;
emp_arr emp_arr_typ;
CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
i INTEGER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
FOR r_emp IN emp_cur LOOP
i := i + 1;
emp_arr(i).empno := r_emp.empno;
emp_arr(i).ename := r_emp.ename;
END LOOP;
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || ' ' ||
emp_arr(j).ename);
END LOOP;
END;
The following output is generated from this anonymous block:
EMPNO ENAME
----- -------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
The emp%ROWTYPE attribute could be used to define emp_arr_typ instead of using the emp_rec_typ record type as shown in the following example:
DECLARE
TYPE emp_arr_typ IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_arr emp_arr_typ;
CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
i INTEGER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
FOR r_emp IN emp_cur LOOP
i := i + 1;
emp_arr(i).empno := r_emp.empno;
emp_arr(i).ename := r_emp.ename;
END LOOP;
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || ' ' ||
emp_arr(j).ename);
END LOOP;
END;
The results are the same as in the prior example.
Instead of assigning each field of the record individually, a record level assignment can be made from r_emp to emp_arr.
DECLARE
TYPE emp_rec_typ IS RECORD (
empno NUMBER(4),
ename VARCHAR2(10)
);
TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER;
emp_arr emp_arr_typ;
CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
i INTEGER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
FOR r_emp IN emp_cur LOOP
i := i + 1;
emp_arr(i) := r_emp;
END LOOP;
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || ' ' ||
emp_arr(j).ename);
END LOOP;
END;
The key of an associative array can be character data as shown in the following example:
DECLARE
TYPE job_arr_typ IS TABLE OF NUMBER INDEX BY VARCHAR2(9);
job_arr job_arr_typ;
BEGIN
job_arr('ANALYST') := 100;
job_arr('CLERK') := 200;
job_arr('MANAGER') := 300;
job_arr('SALESMAN') := 400;
job_arr('PRESIDENT') := 500;
DBMS_OUTPUT.PUT_LINE('ANALYST : ' || job_arr('ANALYST'));
DBMS_OUTPUT.PUT_LINE('CLERK : ' || job_arr('CLERK'));
DBMS_OUTPUT.PUT_LINE('MANAGER : ' || job_arr('MANAGER'));
DBMS_OUTPUT.PUT_LINE('SALESMAN : ' || job_arr('SALESMAN'));
DBMS_OUTPUT.PUT_LINE('PRESIDENT: ' || job_arr('PRESIDENT'));
END;
ANALYST : 100
CLERK : 200
MANAGER : 300
SALESMAN : 400
PRESIDENT: 500