Collections¶
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:
Collection |
Type declaration |
Index |
Sparse |
Dense |
Persistent |
Initialization |
|
|
|
---|---|---|---|---|---|---|---|---|---|
Nested table |
|
positive integer |
Yes* |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Associative array |
|
|
Yes |
Yes |
No |
No |
No |
No |
No |
Variable-size array |
|
positive integer |
No |
Yes |
Yes |
Yes |
Yes |
Yes |
No |
Here, ix_data_type
can be either a BINARY_INTEGER
, any of its subtypes, or a VARCHAR2
.
Associative arrays are thus the only collection type that can handle negative and non-integer index values.
Note
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.
You cannot 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.
Note
Oracle does not recommend using DELETE
and 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.
Running TRIM
on a previously deleted element causes a deleted element to be deleted.
Again.
The built-in DBMS_SQL
package contains a couple of collection shortcuts, for instance: NUMBER_TABLE
and VARCHAR2_TABLE
.
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
FOR
loop, which is appropriate for dense collections when the entire collection needs to be scanned:FOR ix IN l_coll.FIRST .. l_coll.LAST LOOP ...
.A
WHILE
loop, 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.