Nested tables - PolarDB - Alibaba Cloud Documentation Center

A nested table is a type of collection that associates a positive integer with a value.

A nested table has the following characteristics:

A nested table type must be defined after which nested table variables can be declared of that nested table type. Data manipulation occurs by using the nested table variable or simply "table" for short.
When a nested table variable is declared, the nested table initially does not exist (it is a null collection). The null table must be initialized with a constructor. You can also initialize the table by using an assignment statement where the right-hand side of the assignment is an initialized table of the same type. Note: Initialization of a nested table is mandatory in Oracle, but optional in SPL.
The key is a positive integer.
The constructor establishes the number of elements in the table. The EXTEND method adds additional elements to the table. Note: Usage of the constructor to establish the number of elements in the table and usage of the EXTEND method to add additional elements to the table are required in Oracle but optional in SPL.
The table can be sparse - the assignment of values to keys may have gaps:
An attempt to reference a table element beyond its initialized or extended size will result in a SUBSCRIPT_BEYOND_COUNT exception.

The TYPE IS TABLE statement is used to define a nested table type within the declaration section of an SPL program:

TYPE tbltype IS TABLE OF { datatype | rectype | objtype };

tbltype is an identifier assigned to the nested table type. 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.

Note You can use the CREATE TYPE statement to define a nested table type that is available to all SPL programs in the database.

To use the table, a variable must be declared of that nested table type. The syntax for declaring a table variable is as follows:

table tbltype
table is an identifier assigned to the nested table. tbltype is the identifier of a previously defined nested table type.

A nested table is initialized by using the constructor of the nested table type.
tbltype ([ { expr1 | NULL } [, { expr2 | NULL } ] [, ...] ])
tbltype is the identifier of the constructor of the nested table type. tbltype has the same name as the nested table type. expr1, expr2, ... are expressions that are type-compatible with the element type of the table. If NULL is specified, the corresponding element is set to null. If the parameter list is empty, an empty nested table is returned, which means no elements exist in the table. If the table is defined from an object type, exprn must return an object of that object type. The object can be the return value of a function or the constructor of the object type, or the object can be an element of another nested table of the same type.

If a collection method other than EXISTS is applied to an uninitialized nested table, a COLLECTION_IS_NULL exception is thrown.

The following code is an example of a constructor for a nested table:

DECLARE
    TYPE nested_typ IS TABLE OF CHAR(1);
    v_nested        nested_typ := nested_typ('A','B');

An element of the table is referenced by using the following syntax:

table(n)[.element ]

table is the identifier of a previously declared table. n is a positive integer. If the table type of table is defined from a record type or object type, [.element ] must reference an individual field within the record type or attribute within the object type from which the nested table type is defined. Alternatively, the entire record or object can be referenced by omitting [.element ].

The following code is an example of a nested table where it is known that four elements exist:

DECLARE
    TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
    dname_tbl       dname_tbl_typ;
    CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dname_tbl := dname_tbl_typ(NULL, NULL, NULL, NULL);
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dname_tbl(i) := r_dept.dname;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DNAME');
    DBMS_OUTPUT.PUT_LINE('----------');
    FOR j IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE(dname_tbl(j));
    END LOOP;
END;

The above example produces the following output:

DNAME
----------
ACCOUNTING
OPERATIONS
RESEARCH
SALES

The following example reads the first ten employee names from the emp table, stores them in a nested table, and then displays the results from the table. The SPL code is written to assume that the number of employees to be returned is not known beforehand.

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

Note the creation of an empty table with the constructor emp_tbl_typ() as the first statement in the executable topic of the anonymous block. The EXTEND collection method is then used to add an element to the table for each employee returned from the result set.

The output is as follows:

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

The following example shows how a nested table of an object type can be used. First, an object type is created with attributes for the department name and location.

CREATE TYPE dept_obj_typ AS OBJECT (
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
);

The following anonymous block defines a nested table type whose element consists of the dept_obj_typ object type. A nested table variable is declared, initialized, and then populated from the dept table. Finally, the elements from the nested table are displayed.

DECLARE
    TYPE dept_tbl_typ IS TABLE OF dept_obj_typ;
    dept_tbl        dept_tbl_typ;
    CURSOR dept_cur IS SELECT dname, loc FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dept_tbl := dept_tbl_typ(
        dept_obj_typ(NULL,NULL),
        dept_obj_typ(NULL,NULL),
        dept_obj_typ(NULL,NULL),
        dept_obj_typ(NULL,NULL)
    );
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dept_tbl(i).dname := r_dept.dname;
        dept_tbl(i).loc   := r_dept.loc;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DNAME          LOC');
    DBMS_OUTPUT.PUT_LINE('----------     ----------');
    FOR j IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(dept_tbl(j).dname,14) || ' ' ||
            dept_tbl(j).loc);
    END LOOP;
END;

Note The parameters comprising the constructor dept_tbl_typ for the nested table are calls to the constructor dept_obj_typ for the object type.

The following output is generated from the anonymous block:

DNAME          LOC
----------     ----------
ACCOUNTING     NEW YORK
OPERATIONS     BOSTON
RESEARCH       DALLAS
SALES          CHICAGO