Working with Collections in PL/SQL

In this article, I will explore another composite datatype, the collection. An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value.

Collections are used in some of the most important performance optimization features of PL/SQL, such as

• BULK COLLECT. SELECT statements that retrieve multiple rows with a single fetch, increasing the speed of data retrieval.
• FORALL. Inserts, updates, and deletes that use collections to change multiple rows of data very quickly
• Table functions. PL/SQL functions that return collections and can be called in the FROM clause of a SELECT statement.

You can also use collections to work with lists of data in your program that are not stored in database tables.

This article introduces you to collections and gives you a solid foundation in both collection syntax and features.

Collection Concepts and Terminology

Before exploring collections, it is helpful to have a common collections vocabulary that includes the following terms.

Index value. The location of the data in a collection. Index values are usually integers but for one type of collection can also be strings.

Element. The data stored at a specific index value in a collection. Elements in a collection are always of the same type (all of them are strings, dates, or records). PL/SQL collections arehomogeneous.

Sparse. A collection is sparse if there is at least one index value between the lowest and highest defined index values that is not defined. For example, a sparse collection has an element assigned to index value 1 and another to index value 10 but nothing in between. The opposite of a sparse collection is a dense one.

Method. A collection method is a procedure or function that either provides information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation (object-oriented syntax).

Types of Collections

Collections were first introduced in Oracle7 Server and have been enhanced in several ways through the years and across Oracle Database versions. There are now three types of collections to choose from, each with its own set of characteristics and each best suited to a different circumstance.

Associative array. The first type of collection available in PL/SQL, this was originally called a “PL/SQL table” and can be used only in PL/SQL blocks. Associative arrays can be sparse or dense and can be indexed by integer or string.

Nested table. Added in Oracle8 Database, the nested table can be used in PL/SQL blocks, in SQL statements, and as the datatype of columns in tables. Nested tables can be sparse but are almost always dense. They can be indexed only by integer. You can use the MULTISET operator to perform set operations and to perform equality comparisons on nested tables.

Varray. Added in Oracle8 Database, the varray (variable-size array) can be used in PL/SQL blocks, in SQL statements, and as the datatype of columns in tables. Varrays are always dense and indexed by integer. When a varray type is defined, you must specify the maximum number of elements allowed in a collection declared with that type.

You will rarely encounter a need for a varray (How many times do you know in advance themaximum number of elements you will define in your collection?). The associative array is the most commonly used collection type, but nested tables have some powerful, unique features (such as MULTISET operators) that can simplify the code you need to write to use your collection.