PL/SQL has three homogeneous one-dimensional collection types: associative arrays (PL/SQL or index-by tables), nested tables, and variable-size or varying arrays (varrays). Homogeneous refers to the fact that the data elements in a collection all have the same data type. Collections may be nested to simulate multi-dimensional data structures, but these are currently not supported with the traditional syntax you may be familiar with from other programming languages, such as C# or Java.
Summarized in the table below are the distinguishing features of each of these three collection types, where we have omitted
[ CREATE [ OR REPLACE ] ] TYPE type_identifier IS ... from the declarations:
ix_data_type can be either a
BINARY_INTEGER, any of its subtypes, or a
Associative arrays are thus the only collection type that can handle negative and non-integer index values.
When it comes to performance, the difference between integers and small strings (i.e. fewer than 100 characters) as indexes is minimal. For large strings the overhead of hashing can be quite significant, as demonstrated by Steven Feuerstein and Bill Pribyl.
We have added an asterisk to the ‘Yes’ in the column ‘Sparse’ for nested tables because technically they can be sparse, although in practice they are often dense; they only become sparse when elements in the middle are deleted after they have been inserted. The only collection type that can be used in PL/SQL blocks but neither in SQL statements nor as the data type of database columns is an associative array. Although both nested tables and varrays can be stored in database columns, they are stored differently. Nested table columns are stored in a separate table and are intended for ‘large’ collections, whereas varray columns are stored in the same table and thought to be best at handling ‘small’ arrays.
Nested tables and variable-size arrays require initialization with the default constructor function (i.e. with the same identifier as the type), with or without arguments.
All collections support the
DELETE method to remove all or specific elements; the latter only applies to nested tables and associative arrays though.
DELETE non-leading or non-trailing elements from a varray, as that would make it sparse.
As you can see, the
TRIM method is only available to nested tables and varrays; it can only be used to remove elements from the back of the collection.
Notwithstanding this restriction, associative arrays are by far the most common PL/SQL collection type, followed by nested tables.
Variable-size arrays are fairly rare because they require the developer to know in advance the maximum number of elements.
Oracle does not recommend using
TRIM on the same collection, as the results may be counter-intuitive:
DELETE removes an element but retains its placeholder, whereas
TRIM removes the placeholder too.
TRIM on a previously deleted element causes a deleted element to be deleted.
DBMS_SQL package contains a couple of collection shortcuts, for instance:
These are nothing but associative arrays indexed by a
BINARY_INTEGER based on the respective data types.
To iterate through a collection you have two options:
- A numeric
FORloop, which is appropriate for dense collections when the entire collection needs to be scanned:
FOR ix IN l_coll.FIRST .. l_coll.LAST LOOP ....
WHILEloop, which is appropriate for sparse collections or when there is a termination condition based on the collection’s elements:
WHILE ( l_coll IS NOT NULL ) LOOP ....
When using the
FORALL statement on a sparse collection, the
INDICES OF or
VALUES OF option of the bounds clause may prove equally useful.
Now that we have covered the basics of collections, let’s go back to the performance benefits of bulk processing.