Associative arrays (PL/SQL)

A PL/SQL associative array is a collection type that associates a unique key with a value.

An associative array has the following characteristics:

An associative array type must be defined before array variables of that array type can be declared. Data manipulation occurs in the array variable.
The array does not need to be initialized; simply assign values to array elements.
There is no defined limit on the number of elements in the array; it grows dynamically as elements are added.
The array can be sparse; there can be gaps in the assignment of values to keys.
An attempt to reference an array element that has not been assigned a value results in an exception.

Use the TYPE IS TABLE OF statement to define an associative array type.

Syntax

Description

TYPE assoctype

Specifies an identifer for the array type.

datatype

Specifies a supported data type, such as VARCHAR2, NUMBER, RECORD, VARRAY, or associative array type. The %TYPE attribute and the %ROWTYPE attribute are also supported.

INDEX BY

Specifies that the associative array is to be indexed by one of the data types introduced by this clause.

BINARY INTEGER: Integer numeric data.
PLS_INTEGER: Integer numeric data.
VARCHAR2 (n[BYTE|CHAR]): A variable-length character string of maximum length n code units, which may range from 1 to 32 672 BYTE or from 1 to 8 168 CHAR. The %TYPE attribute is also supported if the object to which the %TYPE attribute is being applied is of the BINARY_INTEGER, PLS_INTEGER, or VARCHAR2 data type.

To declare a variable with an associative array type, specify array-name assoctype, where array-name represents an identifier that is assigned to the associative array, and assoctype represents the identifier for a previously declared array type.

To reference a particular element of the array, specify array-name(n), where array-name represents the identifier for a previously declared array, and n represents a value of INDEX BY data type of assoctype. If the array is defined from a record type, the reference becomes array-name(n).field, where field is defined within the record type from which the array type is defined. To reference the entire record, omit field.

Examples

The following example reads the first ten employee names from the EMP table, stores them in an array, and then displays the contents of the array.

SET SERVEROUTPUT ON
/

CREATE OR REPLACE PACKAGE pkg_test_type1
IS
    TYPE emp_arr_typ IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
END pkg_test_type1
/

DECLARE
    emp_arr         pkg_test_type1.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
/

This code generates the following sample output:

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

The example can be modified to use a record type in the array definition.

SET SERVEROUTPUT ON
/

CREATE OR REPLACE PACKAGE pkg_test_type2
IS
    TYPE emp_rec_typ IS RECORD (
        empno       INTEGER,
        ename       VARCHAR2(10)
    );
END pkg_test_type2
/

CREATE OR REPLACE PACKAGE pkg_test_type3
IS
    TYPE emp_arr_typ IS TABLE OF pkg_test_type2.emp_rec_typ INDEX BY BINARY_INTEGER;
END pkg_test_type3
/

DECLARE
    emp_arr         pkg_test_type3.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 modified code generates the following sample output:

EMPNO    ENAME
-----    -------
1001     SMITH
1002     ALLEN
1003     WARD
1004     JONES
1005     MARTIN
1006     BLAKE
1007     CLARK
1008     SCOTT
1009     KING
1010     TURNER

This example can be further modified to use the emp%ROWTYPE attribute to define emp_arr_typ, instead of using the emp_rec_typ record type.

SET SERVEROUTPUT ON
/

CREATE OR REPLACE PACKAGE pkg_test_type4
IS
    TYPE emp_arr_typ IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
END pkg_test_type4
/

DECLARE
    emp_arr         pkg_test_type4.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
/

In this case, the sample output is identical to that of the previous example.

Finally, instead of assigning each field of the record individually, a record-level assignment can be made from r_emp to emp_arr:

SET SERVEROUTPUT ON
/

CREATE OR REPLACE PACKAGE pkg_test_type5
IS
    TYPE emp_rec_typ IS RECORD (
        empno       INTEGER,
        ename       VARCHAR2(10)
    );
END pkg_test_type5
/

CREATE OR REPLACE PACKAGE pkg_test_type6
IS
    TYPE emp_arr_typ IS TABLE OF pkg_test_type5.emp_rec_typ INDEX BY BINARY_INTEGER;
END pkg_test_type6
/

DECLARE
    emp_arr         pkg_test_type6.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
/