Declaring Associative Arrays

To declare an associative array, you declare an associative array type, and then declare a variable of that type. The simplest syntax is:

TYPE array_type IS TABLE OF element_type INDEX BY key_type;

array_name  array_type;

An efficient way to declare an associative array is with a cursor, using the following procedure. The procedure uses each necessary statement in its simplest form, but provides references to its complete syntax.

To use a cursor to declare an associative array:

1. In the declarative part:

   1. Declare the cursor:

      CURSOR cursor_name IS query;
      

      For complete explicit cursor declaration syntax, see Oracle Database PL/SQL Language Reference.

   2. Declare the associative array type:

      TYPE array_type IS TABLE OF cursor_name%ROWTYPE
        INDEX BY { PLS_INTEGER | VARCHAR2 length }
      

      For complete associative array type declaration syntax, see Oracle Database PL/SQL Language Reference.

   3. Declare an associative array variable of that type:

      array_name  array_type;
      

      For complete variable declaration syntax, see Oracle Database PL/SQL Language Reference.

The following example uses the preceding procedure to create two associative arrays, employees_jobs and jobs_, and then declares a third associative array, job_titles_type, without using a cursor. The first two arrays are indexed by integer; the third is indexed by string.

DECLARE
  -- Declare cursor:

  CURSOR employees_jobs_cursor IS
    SELECT FIRST_NAME, LAST_NAME, JOB_ID
    FROM EMPLOYEES
    ORDER BY JOB_ID, LAST_NAME, FIRST_NAME;

  -- Declare associative array type:

  TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE
    INDEX BY PLS_INTEGER;

  -- Declare associative array:

  employees_jobs  employees_jobs_type;

  -- Use same procedure to declare another associative array:

  CURSOR jobs_cursor IS
    SELECT JOB_ID, JOB_TITLE
    FROM JOBS;

  TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE
    INDEX BY PLS_INTEGER;

  jobs_  jobs_type;

-- Declare associative array without using cursor:

  TYPE job_titles_type IS TABLE OF JOBS.JOB_TITLE%TYPE
    INDEX BY JOBS.JOB_ID%TYPE;  -- jobs.job_id%type is varchar2(10)

  job_titles  job_titles_type;

BEGIN
  NULL;
END;
/