Using associative arrays v16

Suggest edits

An associative array is a type of collection that associates a unique key with a value. The key doesn't have to be numeric. It can be character data as well.

Associative array overview

An associative array has the following characteristics:

You must define an associative array type after which you can declare array variables of that array type. Data manipulation occurs using the array variable.
When an array variable is declared, the associative array is created, but it is empty. Start assigning values to key values.
The key can be any negative integer, positive integer, or zero if you specify INDEX BY BINARY_INTEGER or PLS_INTEGER.
The key can be character data if you specify INDEX BY VARCHAR2.
There's no predefined 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 hasn't been assigned a value results in an exception.

Defining an associative array

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>) };

Where:

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.

Declaring a variable

To make use of the array, you must declare a variable with that array type. The following is the syntax for declaring an array variable:

<array> <assoctype>

Where:

array is an identifier assigned to the associative array.

assoctype is the identifier of a previously defined array type.

Referencing an element of the array

Reference an element of the array 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, then [.field ] must reference an individual field in the record type or attribute in the object type from which the array type is defined. Alternatively, you can reference the entire record by omitting [.field ].

Examples

This example reads the first 10 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;

This example produces the following output:

Output

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

This example uses 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 is the output from this anonymous block:

Output

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER

This example uses the emp%ROWTYPE attribute to define emp_arr_typ instead of using the emp_rec_typ record type:

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 using a record type in the array definition.

Instead of assigning each field of the record individually, you can make a record-level assignment 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;

This example uses the key of an associative array as character data:

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

← Prev

About collection types

↑ Up

Working with collection types

Working with nested tables