PolarDB:Associative arrays

An associative array is a type of collection that associates a unique key with a value. Associative arrays can use integers or strings as keys.

An associative array has the following characteristics:

• An associative array type must be defined before array variables of that array type can be declared. Data manipulation is implemented by using array variables.
• When an array variable is declared, an empty associative array is created. You can assign values to array elements.
• If INDEXBY BINARY_INTEGER or PLS_INTEGER is specified, a key of the associative array can be a negative integer, a positive integer, or zero.
• If INDEXBY VARCHAR2 is specified, a key of the associative array can be a string.
• No limit is predefined for the number of elements in the array. The number dynamically grows as elements are added.
• An associative array can be sparse, which means that gaps may exist in the assignment of values to keys.
• An attempt to reference an array element that has not been assigned a value results in an exception.

The TYPE IS TABLE OF ... INDEX BY statement is used to define an associative array type.

TYPE assoctype IS TABLE OF { datatype | rectype | objtype }
  INDEX BY { BINARY_INTEGER | PLS_INTEGER | VARCHAR2(n) };

assoctype specifies an identifier for the array type. datatype specifies a scalar data type, such as VARCHAR2 or NUMBER. rectype specifies a previously defined record type. objtype specifies a previously defined object type. n specifies the maximum length of a character key.

To use a type of an array, you must declare a variable of the array type. The following syntax is used to declare an array variable:

array assoctype

In the preceding syntax, array indicates an identifier that is assigned to the associative array. assoctype indicates the identifier for a previously defined array type.

The following syntax can be used to reference an element of the array:

array(n)[.field ]

array indicates the identifier for a previously declared array. n indicates a value of the data type specified by the INDEX BY clause. If the array is defined from a record type or object type, [.field ] must reference an individual field defined within the record type or attribute within the object type from which the array type is defined. To reference the entire record, omit [.field ].

The following code provides an example on how to read the first ten employee names from the emp table, store them in an array, and then display the data from the array:

DECLARE
    TYPE emp_arr_typ IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i) := r_emp.ename;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j));
    END LOOP;
END;

After the preceding code is run, the following sample output is generated:

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

The example can be modified to use a record type in the array definition.

DECLARE
    TYPE emp_rec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR2(10)
    );
    TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i).empno := r_emp.empno;
        emp_arr(i).ename := r_emp.ename;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || '     ' ||
            emp_arr(j).ename);
    END LOOP;
END;

After the preceding anonymous block is executed, the following sample output is generated:

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER

This example can be further modified to use the emp%ROWTYPE attribute to define emp_arr_typ, instead of using the emp_rec_typ record type. The following code provides an example:

DECLARE
    TYPE emp_arr_typ IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i).empno := r_emp.empno;
        emp_arr(i).ename := r_emp.ename;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || '     ' ||
            emp_arr(j).ename);
    END LOOP;
END;

In this case, the sample output is the same as that of the preceding example.

Instead of individually assigning each field of the record, a record-level assignment can be made from r_emp to emp_arr.

DECLARE
    TYPE emp_rec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR2(10)
    );
    TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i) := r_emp;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || '     ' ||
            emp_arr(j).ename);
    END LOOP;
END;

Keys in an associative array can be strings as shown in the following example:

DECLARE
    TYPE job_arr_typ IS TABLE OF NUMBER INDEX BY VARCHAR2(9);
    job_arr         job_arr_typ;
BEGIN
    job_arr('ANALYST')   := 100;
    job_arr('CLERK')     := 200;
    job_arr('MANAGER')   := 300;
    job_arr('SALESMAN')  := 400;
    job_arr('PRESIDENT') := 500;
    DBMS_OUTPUT.PUT_LINE('ANALYST  : ' || job_arr('ANALYST'));
    DBMS_OUTPUT.PUT_LINE('CLERK    : ' || job_arr('CLERK'));
    DBMS_OUTPUT.PUT_LINE('MANAGER  : ' || job_arr('MANAGER'));
    DBMS_OUTPUT.PUT_LINE('SALESMAN : ' || job_arr('SALESMAN'));
    DBMS_OUTPUT.PUT_LINE('PRESIDENT: ' || job_arr('PRESIDENT'));
END;

ANALYST  : 100
CLERK    : 200
MANAGER  : 300
SALESMAN : 400
PRESIDENT: 500