PolarDB:Varray

When to use a varray

Choose a varray when:

• You know the maximum number of elements in advance.

• You access elements sequentially.

• You need to store or retrieve all elements as a single unit.

If the maximum number of elements is unknown or the collection may become sparse, use a nested table instead.

How it works

All three SPL collection types share some behaviors, but varrays have two key constraints:

• Fixed upper bound: The maximum number of elements is set at type definition time and cannot be exceeded.

• Always dense: Unlike a nested table, a varray cannot be sparse — the assignment of values to keys has no gaps.

When you declare a varray variable, it starts as a null collection. Before you can reference or populate its elements, initialize it with a constructor. You can also initialize the varray by using an assignment statement where the right-hand side of the assignment is an initialized varray of the same type.

Define a varray type

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

TYPE varraytype IS { VARRAY | VARYING ARRAY }(maxsize)
  OF { datatype | objtype };

Where:

• varraytype — the identifier assigned to the varray type.

• maxsize — the maximum number of elements. No varray of this type can exceed this count.

• datatype — a scalar data type such as VARCHAR2 or NUMBER.

• objtype — a previously defined object type.

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

Declare a varray variable

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

varray varraytype

Where:

• varray — the identifier assigned to the variable.

• varraytype — the identifier of a previously defined varray type.

Initialize a varray

Call the type constructor to initialize the varray:

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

Where:

• varraytype — the constructor name, which is the same as the varray type name. The constructor establishes the number of elements in the varray, which must not exceed the maximum size limit.

• expr1, expr2, ... — expressions that are type-compatible with the element type. Specify NULL to set an element to null.

• An empty argument list returns an empty varray with no elements.

If the varray type is defined from an object type, each expression must return an object of that type. The object can be the return value of a function, the return value of the object type's constructor, or an element of another varray of the same type.

Example: initializing a two-element varray

DECLARE
    TYPE varray_typ IS VARRAY(2) OF CHAR(1);
    v_varray        varray_typ := varray_typ('A','B');

If you apply any collection method other than EXISTS to an uninitialized varray, a COLLECTION_IS_NULL exception is thrown.

Reference a varray element

varray(n)[.element]

Where:

• varray — the identifier of a previously declared varray variable.

• n — a positive integer index.

• .element — if the varray type is defined from an object type, specifies an attribute within that object type. Omit .element to reference the entire object.

Extend a varray

After initialization, use the EXTEND method to add additional elements to the varray up to the maximum size limit.

Exceptions

Example

The following example defines a varray of up to four department names, populates it from a cursor, and prints each name.

DECLARE
    TYPE dname_varray_typ IS VARRAY(4) OF VARCHAR2(14);
    dname_varray    dname_varray_typ;
    CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dname_varray := dname_varray_typ(NULL, NULL, NULL, NULL);
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dname_varray(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_varray(j));
    END LOOP;
END;

Output:

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