An associative array is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).

The data type of index can be either a string type (VARCHAR or VARCHAR2) or INTEGER. Indexes are stored in sort order, not creation order.

The operation of exporting a union array will replace the data type of the index with varchar2 to varchar.The operation of importing a union array will replace the data type of the index from varchar2 to varchar.

PERFORM is not supported for manipulating associative array and dbms_output.put_line is not supported for printing the elements of the associative array.

For example, define a type of associative array indexed by integer, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values (in sort order, not creation order).

DECLARE
  -- Associative array indexed by integer:
  TYPE population IS TABLE OF varchar  -- Associative array type
    INDEX BY integer;                  -- indexed by integer

  city_population  population;        -- Associative array variable
  i                integer;
BEGIN
  -- Add elements (key-value pairs) to associative array:
  city_population('2')  := 'Smallville';
  city_population('5') := 'Midland';
  city_population('3')  := 'Megalopolis';

  FOR i IN 1..6 LOOP
      IF city_population.EXISTS(i) THEN
          dbms_output.put_line ('city_population(' || i || ')' || ' exists');
      ELSE
          dbms_output.put_line ('city_population(' || i || ')' || ' does not exist');
      END IF;
  END LOOP;

END;
/

For example, defines a type of associative array indexed by string, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values.

DECLARE
  -- Associative array indexed by varchar2(64):
  
  TYPE population IS TABLE OF NUMERIC  -- Associative array type
    INDEX BY VARCHAR2(64);            --  indexed by varchar2(64)
  
  city_population  population;        -- Associative array variable

BEGIN
  -- Add elements (key-value pairs) to associative array:
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;

  raise info 'the count elements is %', city_population;

  city_population('Midland')     := 50000;

  raise info 'the count elements is %', city_population;

END;
/

dbms_output.put_line is not supported for printing the elements of the associative array, so use raise to print.

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.

When you retrieve a nested table value from the database into a pl/sql nested table variable, pl/sql gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.

Example: Nested Table of Local Type

DECLARE
TYPE Roster IS TABLE OF VARCHAR(15);  -- nested table type

-- nested table variable initialized with constructor: 
names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial Values:');
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('------------------');
    names2(5) := 'P Perez';  -- add one element
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;
END;
/

Example: Nested Table of Standalone Type

CREATE TYPE names2 IS TABLE OF VARCHAR(15);

DECLARE
  nt names2 ;
BEGIN
  nt(1) = 'D Caruso';
  nt(2) = 'J Hamil';
  nt(3) = 'R Singh';
  DBMS_OUTPUT.PUT_LINE(nt(2));
END;
/

Example: Nested Table of Composite Type

create table t(a int,b text);
insert into t values(1,'1');
insert into t values(2,'2');
select dbms_output.serveroutput('t');

create or replace procedure p1 is 
  TYPE array_table IS TABLE OF t%rowtype;
  a_table array_table := array_table();
BEGIN

  SELECT t.* BULK COLLECT INTO a_table FROM t;
  
  FOR i IN a_table.first..a_table.last LOOP
    a_table(i).a := 123;
    a_table(i).b := 'test';
    dbms_output.put_line('a_table(i).a = '||a_table(i).a||', a_table(i).b = '||a_table(i).b);
  END LOOP;
END;
/

Example: Using Nested Table Element as Insert Source

create table t(a int,b float,c number);
insert into t values(1,10,100);
insert into t values(2,20,200);

create or replace procedure p1 is 
  TYPE array_table IS TABLE OF t%rowtype;
  a_table array_table := array_table();
BEGIN
  SELECT t.* BULK COLLECT INTO a_table FROM t;
  execute immediate 'truncate table t';

  FOR i IN a_table.first..a_table.last LOOP
    a_table(i).a := a_table(i).a * 100;
    a_table(i).b := a_table(i).b * 100;
    a_table(i).c := a_table(i).c * 100;
    INSERT INTO t VALUES a_table(i);
  END LOOP;
END;
/

Variable-sized array is an array whose element count can vary from zero (empty) to the declared maximum size, and overruns are also supported here.

To access the elements of a mutable array, use the syntax variable_name(index). When storing and retrieving mutable arrays in the database, their indexes and element order will remain stable.

As you add or delete elements, the amount of memory occupied by the mutable array variable can dynamically increase or decrease.

Currently, only local varrays are supported, not global varrays .

Example: varrays of local type

DECLARE
    TYPE Roster IS VARRAY(10) OF VARCHAR(15);  -- varray type

    -- varray variable initialized with constructor:
    names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial Values:');
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
        DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('------------------');
    names2.extend;
    names2(5) := 'P Perez';  -- add one element
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
        DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;
END;
/

A collection method is a pl/sql subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain.

Table 41.1 shows collection methods.

The basic syntax of a collection method invocation is:

collection_name.method