An associative array is a collection that associates a unique key with a value. The key does not have to be a number; it can also be character data. This article will briefly demonstrate the newly added associative array related functions in the Halo database.
1. Syntax Definition
If INDEX BY BINARY_INTEGER or PLS_INTEGER is specified, the key can be any negative integer, positive integer, or zero. If INDEX BY VARCHAR2 is specified, the key can be character data.
TYPE assoctype IS TABLE OF { datatype | rectype | objtype }
INDEX BY { BINARY_INTEGER | PLS_INTEGER | VARCHAR2(n) };• assoctype is the identifier assigned to the array type, based on which associative array variables are subsequently created. • datatype is a scalar data type, such as VARCHAR2 or NUMBER. • rectype is a previously defined record type. • objtype is a previously defined object type. • n is the maximum length of the character key.
2. Demonstration of Related Methods
2.1 FIRST
FIRST is a method that returns the subscript of the first element in the collection.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('FIRST element: ' || arr.FIRST);
END;
/2.2 LAST
LAST is a method that returns the subscript of the last element in the collection.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('LAST element: ' || arr.LAST);
END;
/2.3 COUNT
COUNT is a method that returns the number of elements in the collection.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || arr.COUNT);
END;
/2.4 NEXT
NEXT is a method that returns the subscript following the specified subscript.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('NEXT element: ' || arr.NEXT('arr4'));
END;
/2.5 PRIOR
The PRIOR method returns the subscript preceding the specified subscript in the collection.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('PRIOR element: ' || arr.PRIOR('arr2'));
END;
/2.6 EXISTS
The EXISTS method verifies whether a specified subscript exists in the collection. It returns TRUE if it exists, otherwise FALSE.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('The index exists: ' ||
CASE WHEN arr.EXISTS('arr5') = TRUE THEN 'true' ELSE 'false' END || ' arr5');
DBMS_OUTPUT.PUT_LINE('The index exists: ' ||
CASE WHEN arr.EXISTS('arr6') = TRUE THEN 'true' ELSE 'false' END || ' arr6');
END;
/2.7 DELETE
The DELETE method deletes entries in the collection. This section demonstrates deleting array elements at a specified position.
DECLARE
TYPE arr_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(50);
arr arr_typ;
BEGIN
arr('arr1') := -100;
arr('arr2') := -10;
arr('arr3') := 0;
arr('arr4') := 10;
arr('arr5') := 100;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || arr.COUNT);
DBMS_OUTPUT.PUT_LINE('The index exists: ' ||
CASE WHEN arr.EXISTS('arr3') = TRUE THEN 'true' ELSE 'false' END || ' arr3');
arr.DELETE('arr3');
DBMS_OUTPUT.PUT_LINE('The index exists: ' ||
CASE WHEN arr.EXISTS('arr3') = TRUE THEN 'true' ELSE 'false' END || ' arr3');
DBMS_OUTPUT.PUT_LINE('COUNT: ' || arr.COUNT);
END;
/3. Comprehensive Demonstration
Next, let's combine the above content, put together a simple test SQL and see.
-- Create a composite type
CREATE TYPE ob_type_test AS OBJECT (
col_a VARCHAR2(50),
col_b VARCHAR2(50),
col_c VARCHAR2(50)
);
DECLARE
TYPE arr_typ IS TABLE OF ob_type_test INDEX BY VARCHAR2(50);
TYPE arr_typ2 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
arr arr_typ;
var_a ob_type_test;
var_b VARCHAR(50);
BEGIN
-- Composite type assignment
var_a.col_a := 'a';
var_a.col_b := 'b';
var_a.col_c := 'c';
-- Construct associative array data
FOR i IN 1 .. 5 LOOP
arr('arr'||i) := var_a;
var_a.col_a := var_a.col_a || 'x';
var_a.col_b := var_a.col_b || 'x';
var_a.col_c := var_a.col_c || 'x';
END LOOP;
RAISE NOTICE '----------------------------';
-- Forward traversal
var_b := arr.FIRST;
WHILE var_b IS NOT NULL LOOP
RAISE NOTICE '% % % %', var_b, arr(var_b).col_a, arr(var_b).col_b, arr(var_b).col_c;
var_b := arr.NEXT(var_b);
END LOOP;
RAISE NOTICE '----------------------------';
-- Reverse traversal
var_b := arr.LAST;
WHILE var_b IS NOT NULL LOOP
RAISE NOTICE '% % % %', var_b, arr(var_b).col_a, arr(var_b).col_b, arr(var_b).col_c;
var_b := arr.PRIOR(var_b);
END LOOP;
RAISE NOTICE '----------------------------';
-- Print number of elements
RAISE NOTICE 'count: %', arr.COUNT;
-- Delete element arr3
arr.DELETE('arr3');
RAISE NOTICE 'count2: %', arr.COUNT;
RAISE NOTICE '----------------------------';
-- Check if arr0 to arr8 exist
FOR i IN 0 .. 8 LOOP
IF arr.EXISTS('arr'||i) THEN
RAISE NOTICE '% exists', 'arr'||i;
ELSE
RAISE NOTICE '% is not exists', 'arr'||i;
END IF;
END LOOP;
RAISE NOTICE '----------------------------';
-- Empty the entire array
arr.DELETE;
RAISE NOTICE 'count3: %', arr.COUNT;
RAISE NOTICE '----------------------------';
END;The results are as follows:
