Associated array (index-by table / PL-SQL table)

·         Unbounded set of key-value pairs. The subscript of the associated array can be integer or string. Subscripts are stored in sort order not creation order. For string subscript sort order is determined by NLS_SORT and NLS_COMP initialization parameters.

·         Associated array created empty but not null.

·         Does not need space and network operation

·         Stored data of the associated array cannot be manipulated by DML statement.

DECLARE

  -- Associative array index by string

  TYPE population  IS TABLE OF NUMBER INDEX BY VARCHAR2(25);

  -- Associative array variable 

  metro_population population;

 

  l_metro VARCHAR2(25);

 

BEGIN

   metro_population('Delhi')   := 18916890;

   metro_population('Mumbai')  := 21900967;

   metro_population('Chennai') := 7413779;

   metro_population('Kolkata') := 15644040;

  

   l_metro := metro_population.FIRST;

   WHILE l_metro IS NOT NULL

   LOOP

     DBMS_OUTPUT.PUT_LINE ('Population of '||

     l_metro||' is  '||  metro_population(l_metro));

     l_metro := metro_population.NEXT(l_metro);

   END LOOP;

END;

/

Population of Chennai is  7413779

Population of Delhi is  18916890

Population of Kolkata is  15644040

Population of Mumbai is  21900967