Oracle PL/SQL - PL SQL Data Type Associative Arrays

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Associative Arrays

Introduction

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 or PLS_INTEGER.

Indexes are stored in sort order, not creation order.

For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

An associative array is empty but not null until you populate it.

The following code defines a type of associative array indexed by string.

Demo

SQL>
SQL> DECLARE-- w w  w.  j ava2s . c  o m
  2    TYPE StringToIntegerMap IS TABLE OF NUMBER  -- Associative array type
  3    INDEX BY VARCHAR2(64);              --  indexed by string
  4    cityMap  StringToIntegerMap;        -- Associative array variable
  5    i  VARCHAR2(64);  -- Scalar variable
  6
  7  BEGIN
  8    -- Add elements (key-value pairs) to associative array:
  9
 10    cityMap('A') := 1;
 11    cityMap('B') := 2;
 12    cityMap('C') := 3;
 13
 14    -- Change value associated with key 'A':
 15
 16    cityMap('A') := 10;
 17
 18    -- Print associative array:
 19
 20    i := cityMap.FIRST;  -- Get first element of array
 21
 22    WHILE i IS NOT NULL LOOP
 23      DBMS_Output.PUT_LINE('value of ' || i || ' is ' || cityMap(i));
 24      i := cityMap.NEXT(i);  -- Get next element of array
 25    END LOOP;
 26  END;
 27  /
value of A is 10
value of B is 2
value of C is 3

PL/SQL procedure successfully completed.

SQL>

Related Topics