An associative array is a group of key-value pairs. Each key is unique and stores the subscript of the corresponding data element.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
You can use the variable_name(index) syntax to access each element in a collection variable by using its unique index, without the need to know the location of the array where these elements belong or to traverse the entire array.
The data type of keys can be PLS_INTEGER or VARCHAR2. Take note of the following considerations:
If the data type of keys is
PLS_INTEGER, the associative array is indexed by integer and is dense without gaps between elements. Every element between the first and last elements has a definition and a value, which can beNULL.If the data type of keys is
VARCHAR2, the associative array is indexed by string and is sparse with gaps between elements.Note
The gaps between elements are of concern during the traversal of a sparse associative array but not during the traversal of a dense associative array.
Assign values to elements in an associative array
You can use the following expression to assign values to elements in an associative array:
array_name(key) := value
If the key in the expression does not exist in the array, the assignment statement will add a key-value pair to the array. If the key exists in the array, the assignment statement will update its value.
In particular, an associative array can be used to store data temporarily without occupying space or network resources like tables. However, the associative array is used only for temporary storage and therefore does not support DML operations.
If you define an associative array variable in a package and assign a value to the variable in the package body, the associative array remains visible during database sessions. Otherwise, it is visible only within the subprogram where it resides.
Declare an associative array
To declare an associative array, you need to first declare an associative array type and then declare a variable of this type. The syntax of a simple declaration statement is as follows:
TYPE array_type IS TABLE OF element_type INDEX BY key_type;
array_name array_type;
Example: Declare an associative array
obclient> DECLARE
TYPE TYPE_SALARY IS TABLE OF NUMBER -- The type of the associative array.
INDEX BY VARCHAR2(64); -- The index type is string.
salary TYPE_SALARY; -- The associative array variable.
n VARCHAR2(64); -- The scalar variable.
BEGIN
-- Add key-value pairs to the associative array.
salary('Tom') := 2000;
salary('Mike') := 7500;
salary('Steve') := 10000;
-- Output the associative array.
n :=salary.FIRST; -- Obtain the first element of the associative array.
WHILE n IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE('Salary of ' || n || ' is ' || salary(n));
n := salary.Next(n); -- Obtain the next element of the associative array.
END LOOP;
END;
/
Query OK, 0 rows affected
Salary of Mike is 7500
Salary of Steve is 10000
Salary of Tom is 2000
Pad an associative array
The most efficient way to pad a dense associative array is to use the SELECT [FETCH] BULK COLLECT INTO clause.
Note
If a large amount of data needs to be padded to a dense associative array, and the result set returned by the SELECT statement will exceed the memory size, you can use a cursor and the FETCH statement in conjunction with the BULK COLLECT INTO and LIMIT clauses to replace the SELECT statement.
You cannot use the SELECT statement to pad a sparse associative array. Instead, you can use a value assignment statement in the FOR LOOP loop statement.
The following example uses the SELECT statement to pad an associative array indexed by integer, and then uses the FOR LOOP statement to pad a sparse associative array indexed by string.
Example: Pad an associative array
obclient> CREATE TABLE emp(
emp_id NUMBER NOT NULL);
Query OK, 0 rows affected
obclient>INSERT INTO emp VALUES(101),(102),(103),(103),(102);
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
obclient>DECLARE
TYPE num_table IS TABLE OF emp.emp_id%TYPE
INDEX BY BINARY_INTEGER; -- Set the index type of the associative array to integer.
num_list NUM_TABLE; -- Define an associative array variable.
BEGIN
SELECT emp_id
BULK COLLECT INTO num_list from emp;
DBMS_OUTPUT.PUT_LINE('Bulk Collected:');
FOR i IN num_list.FIRST..num_list.LAST LOOP
IF i <= 3 THEN
DBMS_OUTPUT.PUT_LINE('Number ['||num_list(i)||']');
END IF;
END LOOP;
END;
/
Query OK, 0 rows affected
Bulk Collected:
Number [101]
Number [102]
Number [103]
Traverse a dense associative array
In a dense associative array indexed by integer, no gaps exist between elements, and every element between the first and last elements has a definition and a value, which can be NULL. You can use the FOR LOOP statement to traverse the dense associative array.
The following example uses the FOR LOOP statement to output the value of each element in the associative array. The upper bound tab_wares.COUNT in the FOR LOOP statement calls the COUNT collection method to return the number of elements in the array.
Example: Traverse a dense associative array
obclient> CREATE TABLE ware (
w_id INT NOT NULL,
w_name VARCHAR(10),
w_city VARCHAR(20),
w_state VARCHAR(20),
PRIMARY KEY(w_id)
);
Query OK, 0 rows affected
obclient>INSERT INTO ware VALUES(1,'KING','BeiJing','China');
Query OK, 1 row affected
obclient>INSERT INTO ware VALUES(2,'ALLEN','ShangHai','China');
Query OK, 1 row affected
obclient>INSERT INTO ware VALUES(3,'CLARK','ShangHai','China');
Query OK, 1 row affected
obclient>SET SERVEROUTPUT ON;
Query OK, 0 rows affected
obclient>DECLARE
-- Declare an associative array without a cursor.
TYPE type_ware IS TABLE OF ware%ROWTYPE INDEX BY PLS_INTEGER ;
tab_wares TYPE_WARE;
TYPE type_ware_city IS TABLE OF ware.w_city%TYPE INDEX BY VARCHAR(20);
tab_ware_cities TYPE_WARE_CITY;
TYPE type_ware_name IS TABLE OF ware.w_name%TYPE INDEX BY VARCHAR(20);
ware_names TYPE_WARE_NAME;
BEGIN
SELECT w_id,w_name,w_city,w_state BULK COLLECT INTO tab_wares FROM ware;
FOR i IN 1..tab_wares.COUNT LOOP
dbms_output.put_line(
rpad( tab_wares(i).w_id, 23)
|| rpad( tab_wares(i).w_name, 23)
|| rpad(tab_wares(i).w_city, 23 )
);
END LOOP;
END;
/
Query OK, 0 rows affected
1 KING BeiJing
2 ALLEN ShangHai
3 CLARK ShangHai
Traverse a sparse associative array
Spaces may exist between elements in a sparse associative array, which is indexed by string. You need to use the WHILE LOOP statement together with the collection methods FIRST and NEXT to traverse a sparse associative array. Here is an example:
DECLARE
-- Declare an associative array without a cursor.
TYPE type_ware IS TABLE OF ware%ROWTYPE INDEX BY PLS_INTEGER ;
tab_wares TYPE_WARE;
TYPE type_ware_city IS TABLE OF ware.w_city%TYPE INDEX BY VARCHAR(20);
tab_ware_cities TYPE_WARE_CITY;
TYPE type_ware_name IS TABLE OF ware.w_name%TYPE INDEX BY VARCHAR(20);
-- Add the following content to the declarative part:
i_name ware.w_name%TYPE;
BEGIN
SELECT w_id,w_name,w_city,w_state
BULK COLLECT INTO tab_wares FROM ware;
FOR i IN 1..tab_wares.COUNT LOOP
dbms_output.put_line(
rpad( tab_wares(i).w_id, 23)
|| rpad( tab_wares(i).w_name, 23)
|| rpad(tab_wares(i).w_city, 23 )
);
END LOOP;
-- Add the following content to the executable part:
i_name := tab_ware_cities.FIRST ;
WHILE i_name IS NOT NULL LOOP
dbms_output.put_line(rpad(i_name,12) || tab_ware_cities(i_name) );
i_name := tab_ware_cities.NEXT(i_name);
END LOOP;
END;
/
Query OK, 0 rows affected
1 KING BeiJing
2 ALLEN ShangHai
3 CLARK ShangHai