Associative arrays - PolarDB - Alibaba Cloud Documentation Center

An associative array is a set of key-value pairs that can be used in a similar way as a hash table. The usage of variable-length arrays and nested tables is similar to that of arrays.

Syntax

You can declare an associative array in the DECLARE section of a package or stored procedure. See the following declaration syntax.

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

The key_type only supports character types (such as VARCHAR2) and numeric types (such as INTEGER).

Examples

Basic usage

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10); -- Declare the local type of the associative array.
    PROCEDURE set_array(aarray IN OUT aarray_type) IS
        i INT;
    BEGIN
        FOR i IN 1..5 LOOP
            aarray(i) := i;  -- Assign a value.
        END LOOP;
    END;
    aarray aarray_type;  -- Create a variable.
BEGIN
    set_array(aarray);
    RAISE NOTICE '%', aarray;  -- Obtain a value.
END;

Sample result:

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

Declare the type of index for an associative array

Compared with nested tables, the declaration syntax of associative arrays uses INDEX BY index_type to declare the type of index. The sorting method of an index is determined by the sorting order of the index type. The index type can be positive integers or strings. Associative arrays are initialized as empty by default. You can assign values to specific index positions. If you do not assign a value to an index position before you obtain a value from the index position, an error message will occur indicating associative arrays element does not exist. See the following example of an attempt to obtain an element that does not exist:

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

Sample result:

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

Declare and use an associative array in a package

Declare the associative array variable in the package and assign a value by using the OUT parameter of the stored procedure:

CREATE OR REPLACE PACKAGE test_package IS
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);  -- Declare the global type of the associative array.
    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 a value.
        END LOOP;
    END;
END;

DECLARE
    aarray test_package.aarray_type;  -- Create a variable.
BEGIN
    test_package.set_array(aarray);
    RAISE NOTICE '%', aarray;  -- Obtain a value.
END;

Sample result:

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

Use an associative array as the OUT parameter and return value of a function

CREATE OR REPLACE PACKAGE test_package IS
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);  -- Declare the global type of the associative array.
    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;  -- Obtain a value.
    RAISE NOTICE 'aarray2 is %', aarray2;  -- Obtain a value.
    aarray1(1) := 1;
    aarray1(2) := 2;
    aarray1(3) := 3;
    aarray1(4) := 4;
    aarray1(5) := 5;
    RAISE NOTICE 'aarray1 is %', aarray1;  -- Obtain a value.
    RAISE NOTICE 'aarray2 is %', aarray2;  -- Obtain a value.
END;

Sample result:

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 declare the associative array in the DECLARE section as shown in the following example. The expected output is the same.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);  -- Declare the global type of the associative array.
    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;  -- Obtain a value.
    RAISE NOTICE 'aarray2 is %', aarray2;  -- Obtain a value.
    aarray1(1) := 1;
    aarray1(2) := 2;
    aarray1(3) := 3;
    aarray1(4) := 4;
    aarray1(5) := 5;
    RAISE NOTICE 'aarray1 is %', aarray1;  -- Obtain a value.
    RAISE NOTICE 'aarray2 is %', aarray2;  -- Obtain a value.
END;

Use an associative array as the parameter of a function

When an associative array is used as the function parameter, the IN parameter cannot be modified inside the function or procedure. When an associative array is used as the OUT parameter, it can be modified inside the function or procedure.

An associative array is used as the IN parameter:

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;
        END LOOP;
    END;
    aarray aarray_type;
BEGIN
    set_array(aarray);
END;

Sample result:

ERROR:  variable "aarray" is declared CONSTANT

An associative array is used as the OUT parameter:

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;

Sample result:

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

Three-dimensional associative arrays

Associative arrays support up to three dimensions. The following example demonstrates how to declare, populate, and manipulate a three-dimensional associative array.

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
    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');

    aarray('a')('b').delete('c');
    RAISE NOTICE '%', aarray('a')('b');

    RAISE NOTICE '%', aarray('a')('b').first;
    RAISE NOTICE '%', aarray('a')('b').last;
    RAISE NOTICE '%', aarray('a')('b').count;
    RAISE NOTICE '%', aarray('a')('b').next('d');
    RAISE NOTICE '%', aarray('a')('b').exists('d');
END;

Sample result:

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