PolarDB:Associative arrays

Syntax

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

Examples

See the following example of an associative array:

DECLARE
  TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10); -- Declare the local type of the associative array.
  aarray aarray_type; -- Create a variable. The initial value is an empty value.
BEGIN
  aarray('a') := 1; -- Assign a value.
  RAISE NOTICE '%', aarray('a'); -- Obtain a value.
END;

Sample result:

NOTICE:  1
DO

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. The initial value of an associative array is an empty value 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 that indicates associative arrays element does not exist is returned. 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