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-compatible 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 are the details:
If the key's data type is
PLS_INTEGER, the associative array is indexed by integer and is dense (elements are contiguous without gaps). 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 don't need to consider gaps between elements. However, when traversing a sparse associative array, you should be aware of the 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, but they cannot be operated on with DML statements because they are only for temporary storage.
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 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 -- 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 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
Fill an associative array
The most effective way to fill a dense associative array is to use the SELECT [FETCH] BULK COLLECT INTO clause.
Note
If a dense associative array needs to be filled 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 combination with the BULK COLLECT INTO and LIMIT clauses to replace the SELECT statement.
You cannot use the SELECT statement to fill a sparse associative array. You can use the assignment statement in a FOR LOOP statement.
The following example shows how to use the SELECT statement to fill an associative array indexed by an integer and how to use the FOR LOOP statement to fill a sparse associative array indexed by a string.
Example: Fill 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; -- The index type of the associative array is an integer.
num_list NUM_TABLE; -- 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
In a dense associative array (indexed by integers), elements are contiguous. 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 bound 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
In a sparse associative array (indexed by strings), there may be 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 execution 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