An associative array is a collection of key-value pairs. Each key is unique and serves as the index for the corresponding 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 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. The following table describes these data types:
If the key is of the
PLS_INTEGERdata type, the associative array is indexed by integer and is dense (elements are contiguous). All elements from the first to the last are defined and have values (which can beNULL).If the key is of the
VARCHAR2data type, 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. However, when traversing a sparse associative array, you need to be aware of potential 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 corresponding to the key to the new value.
In particular, associative arrays can be used for temporary data storage. They do not require as much space or network resources as tables. However, since associative arrays are for temporary storage, you cannot perform DML operations 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 is visible during the entire 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 -- Declare an associative array type.
INDEX BY VARCHAR2(64); -- Declare the index type as a string.
salary TYPE_SALARY; -- Declare an associative array variable.
n VARCHAR2(64); -- Declare a scalar variable.
BEGIN
-- Add key-value pairs 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 need to populate a dense associative array with a large amount of data and the result set returned by the SELECT statement exceeds the memory size, you can use a cursor and the FETCH statement in conjunction 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; -- Declare the index type of the associative array as an integer.
num_list NUM_TABLE; -- Declare 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 integer) are contiguous, and each element between the first and last elements has a defined 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 string) may have gaps between elements. To traverse a sparse associative array, you need to use the WHILE LOOP statement in combination with the FIRST and NEXT methods of the collection. Here's 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 declaration:
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 code 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