An associative array is a collection of key-value pairs. Each key is unique and serves as the index for the corresponding element in the array.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
You can use the variable_name(index) syntax to access each element of a collection variable by its unique index without knowing its position in the array or traversing the entire array.
The data type of the key can be PLS_INTEGER or VARCHAR2. Here's an explanation:
If the key's data type is
PLS_INTEGER, the associative array is indexed by integer and is dense (elements are contiguous). In this case, every element from the first to the last has a defined value (which can beNULL).If the key's data type is
VARCHAR2, the associative array is indexed by string and is sparse (elements may have gaps).Note
When traversing a dense associative array, you do not need to consider gaps between elements. When traversing a sparse associative array, you should be aware of the gaps.
Assign values to elements of an associative array
You can use the following assignment expression to assign a value to an element of an associative array:
array_name(key) := value
If the key does not exist in the array, the assignment statement adds a key-value pair to the array. Otherwise, the assignment statement updates the value of the key to the new value.
In particular, associative arrays can be used for temporary data storage. They do not require the same amount of space or network resources as tables, but since they are only for temporary storage, you cannot perform DML statements on them.
If you define an associative array variable in a package and assign a value to it in the package body, the associative array remains visible during the database session. Otherwise, it is only visible within the scope of the subprogram.
Declare an associative array
To declare an associative array, you need to first declare an associative array type and then declare a variable of that type. The simplest syntax 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 type of the index is a string.
salary TYPE_SALARY; -- The variable of the associative array.
n VARCHAR2(64); -- The scalar variable.
BEGIN
-- Add a key-value pair to the associative array.
salary('Tom') := 2000;
salary('Mike') := 7500;
salary('Steve') := 10000;
-- Print 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
Populate an associative array
The most efficient way to populate a dense associative array is to use the SELECT [FETCH] BULK COLLECT INTO clause.
Note
If you want to populate a dense associative array with a large amount of data and the result set returned by the SELECT statement exceeds the memory limit, you can use a cursor and the FETCH statement in combination with the BULK COLLECT INTO and LIMIT clauses instead of the SELECT statement.
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; -- Define the index type of the associative array as an integer.
num_list NUM_TABLE; -- Define the variable of the associative array.
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
The elements of a dense associative array (indexed by integers) are contiguous, and each element between the first and last elements is defined and has a value (which can be NULL). You can use the FOR LOOP statement to traverse a dense associative array. The following example uses the FOR LOOP statement to print the values of each element in the associative array. The upper limit of the FOR LOOP statement, tab_wares.COUNT, calls the COUNT method of the collection 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
A sparse associative array (indexed by strings) may have gaps between elements. To traverse a sparse associative array, you must use the WHILE LOOP statement in combination with the FIRST and NEXT methods of the collection. 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 line in the declaration section:
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 lines in the execution section:
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
Query OK, 0 rows affected
1 KING BeiJing 2 ALLEN ShangHai 3 CLARK ShangHai