Define and use nested tables - PolarDB

A nested table is a collection type that maps positive integers to element values. Unlike arrays, nested tables have no declared size limit and can be sparse — keys may have gaps where no value is assigned.

Nested table characteristics

Characteristic	Details
Type definition required	Define a nested table type before declaring variables of that type.
Null by default	A newly declared nested table variable is a null collection. Call the constructor to initialize it before use.
Initialization rules	Initialization is mandatory in Oracle but optional in SPL.
Integer keys	Keys are positive integers.
Dynamic sizing	Use the constructor to set the initial element count. Use `EXTEND` to add more elements. Both are required in Oracle but optional in SPL.
Sparse capability	Keys can have gaps — not all positions need to hold a value.
Bounds checking	Referencing an element beyond the initialized or extended size raises `SUBSCRIPT_BEYOND_COUNT`.

Define a nested table type

Use the TYPE IS TABLE statement in the declaration section of an SPL program:

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

Parameter	Description
`tbltype`	Identifier for the nested table type
`datatype`	Scalar data type, such as `VARCHAR2` or `NUMBER`
`rectype`	A previously defined record type
`objtype`	A previously defined object type

To make a nested table type available to all SPL programs in the database, use CREATE TYPE instead.

Declare a variable

After defining the type, declare a variable of that type:

table tbltype

Parameter	Description
`table`	Identifier for the nested table variable
`tbltype`	Identifier of a previously defined nested table type

Initialize the nested table

Call the type constructor to initialize the nested table:

tbltype ([ { expr1 | NULL } [, { expr2 | NULL } ] [, ...] ])

The constructor name is the same as the nested table type name.
Pass NULL to set an element to null.
Pass an empty argument list to create an empty nested table with no elements.
If the table type is defined from an object type, each expression must return an object of that type.

Calling any collection method other than EXISTS on an uninitialized nested table raises COLLECTION_IS_NULL.

The following example initializes a nested table of CHAR(1) with two elements:

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

Reference an element

Access an individual element using:

table(n)[.element ]

Parameter	Description
`table`	Identifier of a declared nested table variable
`n`	A positive integer index
`.element`	A field or attribute within the record or object type. When the table type is based on a record or object type, specify `.element` to access an individual field or attribute. Omit `.element` to reference the entire record or object.

Exceptions

Exception	When it occurs
`COLLECTION_IS_NULL`	Any collection method other than `EXISTS` is called on an uninitialized nested table
`SUBSCRIPT_BEYOND_COUNT`	An element is referenced at an index beyond the initialized or extended size

Examples

Populate from a query with a known row count

This example declares a nested table of VARCHAR2(14), initializes it with four null slots using the constructor, and populates it from a cursor over the dept table.

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;

Output:

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

Populate using EXTEND when the row count is unknown

When the number of rows returned is not known in advance, initialize an empty table and call EXTEND inside the loop to grow the table one element at a time.

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;

emp_tbl_typ() creates an empty nested table. EXTEND appends one slot per iteration, allowing the table to grow with the result set.

Output:

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

Use a nested table of an object type

First, define the object type:

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

Then declare, initialize, and populate a nested table of that object type. The constructor dept_tbl_typ takes calls to the object constructor dept_obj_typ as its arguments.

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;

Output:

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