Declare and use associative arrays - PolarDB

An associative array is a set of key-value pairs that works like a hash table. Unlike variable-length arrays (VARRAYs) and nested tables, which use sequential integer subscripts, associative arrays use string or integer keys and do not require sequential indexing.

Key characteristics

An associative array starts empty when declared. Assign a value to a key before reading it.
Keys can be positive integers or character strings. Elements are sorted by the sort order of the key type.
Accessing a key that has not been assigned raises an error.
Declare an associative array type in the DECLARE section of a package or stored procedure, then declare variables of that type.

Syntax

TYPE type_name IS TABLE OF value_type [ NULL | NOT NULL ] INDEX BY key_type;

Parameter	Description
`type_name`	Identifier for the array type.
`value_type`	Data type of each element. Supports scalar types (such as `INT` or `VARCHAR2`), record types, and object types.
`NOT NULL`	Optional. Prevents null values from being stored.
`key_type`	Index type. Supports character types (such as `VARCHAR2`) and numeric types (such as `INTEGER`).

Collection methods

Method	Type	Description
`FIRST`	Function	Returns the first key in the array.
`LAST`	Function	Returns the last key in the array.
`COUNT`	Function	Returns the number of elements.
`NEXT(key)`	Function	Returns the key that follows the given key.
`EXISTS(key)`	Function	Returns `TRUE` if the given key exists, `FALSE` otherwise.
`DELETE(key)`	Procedure	Removes the element at the given key.

Examples

Basic usage

Declare an associative array type locally in the DECLARE block, populate it inside a procedure, and print the result:

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10); -- Local type declaration
    PROCEDURE set_array(aarray IN OUT aarray_type) IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i;  -- Assign value at key i
        END LOOP;
    END;
    aarray aarray_type;  -- Array variable
BEGIN
    set_array(aarray);
    RAISE NOTICE '%', aarray;  -- Print array contents
END;

Output:

NOTICE:  {1,2,3,4,5}
DO

Access an unassigned key

An associative array is empty when declared. Accessing a key that has not been assigned raises an error:

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    RAISE NOTICE '%', aarray('b');
END;

Output:

ERROR:  associative arrays element does not exist
CONTEXT:  PL/SQL function inline_code_block line 5 at RAISE

Declare and use an associative array in a package

Declare the array type globally in a package to share it across procedures and functions. Use the OUT parameter to populate the array from a stored procedure:

CREATE OR REPLACE PACKAGE test_package IS
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);  -- Global type
    PROCEDURE set_array(aarray IN OUT aarray_type);
END;

CREATE OR REPLACE PACKAGE BODY test_package IS
    PROCEDURE set_array(aarray IN OUT aarray_type) IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i;  -- Assign value at key i
        END LOOP;
    END;
END;

DECLARE
    aarray test_package.aarray_type;  -- Variable of the package-level type
BEGIN
    test_package.set_array(aarray);
    RAISE NOTICE '%', aarray;  -- Print array contents
END;

Output:

NOTICE:  {1,2,3,4,5}
DO

Use an associative array as a function return value

The following example demonstrates pass-by-value semantics: modifying aarray1 after calling get_array does not affect aarray2. The IN OUT parameter mode allows the function to both read and modify the array.

CREATE OR REPLACE PACKAGE test_package IS
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);  -- Global type
    FUNCTION get_array(aarray IN OUT aarray_type) RETURN aarray_type;
END;

CREATE OR REPLACE PACKAGE BODY test_package IS
    FUNCTION get_array(aarray IN OUT aarray_type) RETURN aarray_type IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i + 100;
        END LOOP;
        RETURN aarray;
    END;
END;

DECLARE
    aarray1 test_package.aarray_type;
    aarray2 test_package.aarray_type;
BEGIN
    aarray2 := test_package.get_array(aarray1);
    RAISE NOTICE 'aarray1 is %', aarray1;  -- aarray1 modified via IN OUT
    RAISE NOTICE 'aarray2 is %', aarray2;  -- aarray2 received the return value

    -- Modify aarray1 independently
    aarray1(1) := 1;
    aarray1(2) := 2;
    aarray1(3) := 3;
    aarray1(4) := 4;
    aarray1(5) := 5;
    RAISE NOTICE 'aarray1 is %', aarray1;  -- aarray1 changed
    RAISE NOTICE 'aarray2 is %', aarray2;  -- aarray2 unchanged
END;

Output:

NOTICE:  aarray1 is {101,102,103,104,105}
NOTICE:  aarray2 is {101,102,103,104,105}
NOTICE:  aarray1 is {1,2,3,4,5}
NOTICE:  aarray2 is {101,102,103,104,105}
DO

You can also declare the type in the DECLARE block to get the same result:

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);  -- Local type declaration
    FUNCTION get_array(aarray IN OUT aarray_type) RETURN aarray_type IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i + 100;
        END LOOP;
        RETURN aarray;
    END;
    aarray1 aarray_type;
    aarray2 aarray_type;
BEGIN
    aarray2 := get_array(aarray1);
    RAISE NOTICE 'aarray1 is %', aarray1;
    RAISE NOTICE 'aarray2 is %', aarray2;
    aarray1(1) := 1;
    aarray1(2) := 2;
    aarray1(3) := 3;
    aarray1(4) := 4;
    aarray1(5) := 5;
    RAISE NOTICE 'aarray1 is %', aarray1;
    RAISE NOTICE 'aarray2 is %', aarray2;
END;

Use an associative array as a function parameter

The parameter mode determines whether a function or procedure can modify the array:

IN: The array is read-only inside the function or procedure. Any assignment raises an error.
OUT: The array can be modified inside the function or procedure.
IN OUT: The array is readable and writable.

IN parameter — assignment raises an error:

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    PROCEDURE set_array(aarray IN aarray_type) IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i;  -- Not allowed: IN parameter is constant
        END LOOP;
    END;
    aarray aarray_type;
BEGIN
    set_array(aarray);
END;

Output:

ERROR:  variable "aarray" is declared CONSTANT

OUT parameter — assignment succeeds:

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    PROCEDURE set_array(aarray OUT aarray_type) IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i;
        END LOOP;
    END;
    aarray aarray_type;
BEGIN
    set_array(aarray);
    RAISE NOTICE '%', aarray;
END;

Output:

NOTICE:  {1,2,3,4,5}
DO

Three-dimensional associative arrays

Associative arrays support up to three dimensions. Declare nested types to build each dimension:

DECLARE
    TYPE aarray_type1 IS TABLE OF INT INDEX BY VARCHAR(10);
    TYPE aarray_type2 IS TABLE OF aarray_type1 INDEX BY VARCHAR(10);
    TYPE aarray_type3 IS TABLE OF aarray_type2 INDEX BY VARCHAR(10);
    aarray aarray_type3;
BEGIN
    -- Populate the innermost dimension
    aarray('a')('b')('c') := 1;
    aarray('a')('b')('d') := 2;
    aarray('a')('b')('e') := 3;
    aarray('a')('b')('f') := 4;
    RAISE NOTICE '%', aarray('a')('b');

    -- Delete one element and check the remaining contents
    aarray('a')('b').delete('c');
    RAISE NOTICE '%', aarray('a')('b');

    -- Use collection methods on the inner array
    RAISE NOTICE '%', aarray('a')('b').first;    -- First key
    RAISE NOTICE '%', aarray('a')('b').last;     -- Last key
    RAISE NOTICE '%', aarray('a')('b').count;    -- Element count
    RAISE NOTICE '%', aarray('a')('b').next('d');  -- Key after 'd'
    RAISE NOTICE '%', aarray('a')('b').exists('d');  -- Check if 'd' exists
END;

Output:

NOTICE:  {1,2,3,4}
NOTICE:  {2,3,4}
NOTICE:  d
NOTICE:  f
NOTICE:  3
NOTICE:  e
NOTICE:  t