Arrays

PL/SQL provides a data structure similar to arrays. It stores a fixed-size sequence of elements of the same type, which is used to store ordered data sets.

An array consists of consecutive memory locations. The least significant bit corresponds to the first element, and the most significant bit corresponds to the last element.

As shown in the following example, you can create an array type by using the command line:

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>;

As shown in the following example, you can dynamically create an array type in a PL/SQL block:

TYPE varray_type_name IS VARRAY(n) of <element_type>;

In this example, varray_type_name is the name of the array type, n is the number of elements, and element_type is the type of elements.

obclient> DECLARE
    TYPE alpha IS TABLE OF VARCHAR2(1);
    l alpha := alpha('A', 'B', 'C', 'D');
BEGIN
    FOR i IN l.FIRST .. l.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(l(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('-');
END;
/
Query OK, 0 rows affected

A
B
C
D
-

The basic structure of a nested table is similar to that of an array. However, the two differ in the following aspects:

• An array must be defined with a specified length, whereas a nested table does not need to be. A nested table can be dynamically extended.

• An array is continuous. When elements are deleted, the indexes of the array move. A nested table is initialized as continuous, but when elements are deleted, the indexes do not move. The deleted elements still occupy positions.