PolarDB:Associative array constructors

Example 1: Initialize an associative array in a PL/SQL block

This example shows how to define a local associative array type in a DECLARE block and assign an initial value to it using a constructor in the execution section.

DECLARE
    -- Declare a local associative array type with a VARCHAR key and an INT value.
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    
    -- Declare a variable of this type.
    aarray aarray_type;
BEGIN
    -- Initialize the array using a constructor.
    aarray := aarray_type('first' => 10, 'second' => 20, 'third' => 30);
    
    -- Print the array content and the number of elements.
    RAISE NOTICE 'Array content: %', aarray;
    RAISE NOTICE 'Number of elements: %', aarray.COUNT;
END;
/

Expected outputs:

NOTICE: Array content: (first=>10,second=>20,third=>30)
NOTICE: Number of elements: 3

Example 2: Use a constructor in a package

This example shows how to define a global associative array type in a package and how to use a constructor to initialize an array as an OUT parameter.

-- 1. Create the package specification.
CREATE OR REPLACE PACKAGE test_package AS
    -- Declare a global associative array type.
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    
    -- Declare a procedure that uses a constructor to initialize an array.
    PROCEDURE init_array(aarray OUT aarray_type);
END;
/

-- 2. Create the package body.
CREATE OR REPLACE PACKAGE BODY test_package AS
    PROCEDURE init_array(aarray OUT aarray_type) IS
    BEGIN
        aarray := aarray_type('one' => 1, 'two' => 2, 'three' => 3);
    END;
END;
/

-- 3. Call the procedure in the package.
DECLARE
    my_array test_package.aarray_type;
BEGIN
    test_package.init_array(my_array);
    RAISE NOTICE 'Array content: %', my_array;
    RAISE NOTICE 'Value of key "two": %', my_array('two');
END;
/

Expected outputs:

NOTICE: Array content: (one=>1,three=>3,two=>2)
NOTICE: Value of key "two": 2

Example 3: Use a constructor as a function return value

This example shows how a function can directly return an associative array created by a constructor.

DECLARE
    -- Declare the associative array type.
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    
    -- Declare a function that returns an associative array.
    FUNCTION create_array RETURN aarray_type IS
    BEGIN
        -- Directly use the constructor as the return value.
        RETURN aarray_type('alpha' => 100, 'beta' => 200, 'gamma' => 300);
    END;
    
    result_array aarray_type;
BEGIN
    result_array := create_array();
    RAISE NOTICE 'Returned array: %', result_array;
    RAISE NOTICE 'Value of key "gamma": %', result_array('gamma');
END;
/

Expected outputs:

NOTICE: Returned array: (alpha=>100,beta=>200,gamma=>300)
NOTICE: Value of key "gamma": 300

Example 4: Handle special cases

Associative array constructors have specific rules for handling special cases.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    aarray := aarray_type(); -- Use an empty constructor.
    RAISE NOTICE 'Number of elements in the empty array: %', aarray.COUNT;

    -- Add elements later.
    aarray('new_key') := 42;
    RAISE NOTICE 'After adding an element: %', aarray;
END;
/

NOTICE: Number of elements in the empty array: 0
NOTICE: After adding an element: (new_key=>42)

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    aarray := aarray_type('key1' => 10, 'key2' => 20, 'key1' => 30);
    RAISE NOTICE 'Array content: %', aarray;
    RAISE NOTICE 'Final value of key1: %', aarray('key1');
END;
/

NOTICE: Array content: (key1=>30,key2=>20)
NOTICE: Final value of key1: 30

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    aarray := aarray_type('zebra' => 1, 'apple' => 2, 'banana' => 3);
    -- Although 'zebra' is first, the keys are stored in alphabetical order.
    RAISE NOTICE 'Array content: %', aarray;
    RAISE NOTICE 'First key: %', aarray.FIRST;
    RAISE NOTICE 'Last key: %', aarray.LAST;
END;
/

NOTICE: Array content: (apple=>2,banana=>3,zebra=>1)
NOTICE: First key: apple
NOTICE: Last key: zebra

Example 5: Initialize a multidimensional associative array

Constructors also support nested usage to initialize multi-dimensional associative arrays.

DECLARE
    -- Define inner and outer associative array types.
    TYPE aarray_type1 IS TABLE OF INT INDEX BY VARCHAR(10);
    TYPE aarray_type2 IS TABLE OF aarray_type1 INDEX BY VARCHAR(10);
    
    aarray aarray_type2;
BEGIN
    -- Use a nested constructor to initialize the two-dimensional array.
    aarray := aarray_type2(
        'row1' => aarray_type1('col1' => 1, 'col2' => 2),
        'row2' => aarray_type1('col1' => 3, 'col2' => 4)
    );
    
    RAISE NOTICE 'Complete array: %', aarray;
    RAISE NOTICE 'Value of element [row2][col1]: %', aarray('row2')('col1');
END;
/

Expected outputs:

NOTICE: Complete array: (row1=>"(col1=>1,col2=>2)",row2=>"(col1=>3,col2=>4)")
NOTICE: Value of element [row2][col1]: 3