This section is a work in progress, information may change in the future.
Some parts in the output code are omitted for clarity reasons.
Description
An associative array (formerly called PL/SQL table or index-by table) 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 (VARCHAR2, VARCHAR, STRING, or LONG) 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.
()
Not to be confused with the .
For the translation, the type definition is replaced by an OBJECT and then its usages are changed accordingly across any operations.
In order to define an Associative Array type, the syntax is as follows:
type_definition := TYPE IS TABLE OF datatype INDEX BY indexing_datatype;
indexing_datatype := { PLS_INTEGER
| BINARY_INTEGER
| string_datatype
}
To declare a variable of this type:
variable_name collection_type;
Sample Source Patterns
Varchar-indexed Associative Array
Oracle
CREATE OR REPLACE PROCEDURE associative_array
IS
TYPE associate_array_typ IS TABLE OF INTEGER
INDEX BY VARCHAR2(50);
associate_array associate_array_typ := associate_array_typ();
associate_index VARCHAR2(50);
BEGIN
associate_array('abc') := 1;
associate_array('bca') := 2;
associate_array('def') := 3;
DBMS_OUTPUT.PUT_LINE(associate_array('abc'));
associate_array('abc') := 4;
--THROWS 'NO DATA FOUND'
--DBMS_OUTPUT.PUT_LINE(associate_array('no exists'));
DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
associate_index := associate_array.FIRST;
WHILE associate_index IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
associate_index := associate_array.NEXT(associate_index);
END LOOP;
END;
CALL associative_array();
DBMS OUTPUT
-----------
1
3
4
2
3
Snowflake
Please note the 'true' parameter in the OBJECT_INSERT. This is so that the element is updated if it is already present in the array.
CREATE OR REPLACE PROCEDURE PUBLIC.associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
associate_array OBJECT := OBJECT_CONSTRUCT();
associate_index VARCHAR(50);
BEGIN
associate_array := OBJECT_INSERT(associate_array, 'abc', 1, true);
associate_array := OBJECT_INSERT(associate_array, 'bca', 2, true);
associate_array := OBJECT_INSERT(associate_array, 'def', 3, true);
CALL DBMS_OUTPUT.PUT_LINE(:associate_array['abc']);
CALL DBMS_OUTPUT.PUT_LINE(:associate_array['not found']);
associate_array := OBJECT_INSERT(:associate_array, 'abc', 4, true);
CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
LOOP
associate_index := OBJECT_KEYS(:associate_array)[:i-1];
CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index]);
END LOOP;
END;
$$;
CALL PUBLIC.associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;
DBMS OUTPUT
-----------
1
3
4
2
3
Numeric-indexed Associative Array
Oracle
CREATE OR REPLACE PROCEDURE numeric_associative_array
IS
TYPE numeric_associative_array_typ IS TABLE OF INTEGER
INDEX BY PLS_INTEGER;
associate_array numeric_associativ
e_array_typ := numeric_associative_array_typ();
associate_index PLS_INTEGER;
BEGIN
associate_array(1) := -1;
associate_array(2) := -2;
associate_array(3) := -3;
DBMS_OUTPUT.PUT_LINE(associate_array(1));
associate_array(1) := -4;
DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
associate_index := associate_array.FIRST;
WHILE associate_index IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
associate_index := associate_array.NEXT(associate_index);
END LOOP;
END;
CALL numeric_associative_array();
DBMS OUTPUT
-----------
-1
3
-4
-2
-3
Snowflake
Please note that the numeric value is converted to varchar accordingly when the operation needs it. Additionally, note the 'true' parameter in the OBJECT_INSERT. This is so that the element is updated if it is already present in the array.
CREATE OR REPLACE PROCEDURE PUBLIC.numeric_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
associate_array OBJECT := OBJECT_CONSTRUCT();
associate_index NUMBER;
BEGIN
associate_array := OBJECT_INSERT(associate_array, '1', -1, true);
associate_array := OBJECT_INSERT(associate_array, '2', -2, true);
associate_array := OBJECT_INSERT(associate_array, '3', -3, true);
CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']);
associate_array := OBJECT_INSERT(:associate_array, '1', -4, true);
CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
LOOP
associate_index := OBJECT_KEYS(:associate_array)[:i-1];
CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index::VARCHAR]);
END LOOP;
END;
$$;
CALL PUBLIC.numeric_associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;
DBMS OUTPUT
-----------
-1
3
-4
-2
-3
Record-element Numeric-indexed Associative Array
In this case, the associative array is composed of a Record-structure, and this structure needs to be preserved. For this purpose, further operations on insertions were added.
Oracle
CREATE OR REPLACE PROCEDURE record_associative_array
IS
TYPE record_typ IS RECORD(col1 INTEGER);
TYPE record_associative_array_typ IS TABLE OF record_typ
INDEX BY PLS_INTEGER;
associate_array record_associati ve_array_typ := record_associative_array_typ();
associate_index PLS_INTEGER;
BEGIN
associate_array(1).col1 := -1;
associate_array(2).col1 := -2;
associate_array(3).col1 := -3;
DBMS_OUTPUT.PUT_LINE(associate_array(1).col1);
associate_array(4).col1 := -4;
DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
associate_index := associate_array.FIRST;
WHILE associate_index IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(associate_array(associate_index).col1);
associate_index := associate_array.NEXT(associate_index);
END LOOP;
END;
/
CALL record_associative_array();
DBMS OUTPUT
-----------
-1
3
-4
-2
-3
Snowflake
In this scenario, the insertion/update assumes an automatic creation of the record within the associative array and this needs to be taken into account when creating new records.
CREATE OR REPLACE PROCEDURE PUBLIC.record_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
associate_array OBJECT := OBJECT_CONSTRUCT();
associate_index NUMBER;
BEGIN
associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -1, true), true);
associate_array := OBJECT_INSERT(associate_array, '2', OBJECT_INSERT(NVL(associate_array['2'], OBJECT_CONSTRUCT()), 'col1', -2, true), true);
associate_array := OBJECT_INSERT(associate_array, '3', OBJECT_INSERT(NVL(associate_array['3'], OBJECT_CONSTRUCT()), 'col1', -3, true), true);
CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']:col1);
associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -4, true), true);
CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
LOOP
associate_index := OBJECT_KEYS(:associate_array)[:i-1];
CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index::VARCHAR]:col1);
END LOOP;
END;
$$;
CALL PUBLIC.record_associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;
DBMS OUTPUT
-----------
-1
3
-4
-2
-3
Known Issues
1. They are currently not being recognized
SnowConvert treats these collections as Nested Table Arrays. There is a work item to fix this.