Oracle PL/SQL Programming, 4th Edition

Chapter 12. Collections

A collection is a data structure that acts like a list or a single-dimensional array. Collections are, in fact, the closest you can get to traditional arrays in the PL/SQL language. This chapter will help you decide which of the three different types of collections (associative array, nested table, and VARRAY) best fit your program requirements and show you how to define and manipulate those structures.

Here are some of the ways I’ve found collections handy:

Keep track of lists

Most generally, I use collections to keep track of lists of data elements within my programs. Yes, you could use relational tables or global temporary tables or delimited strings, but collections are very efficient structures that can be manipulated with very clean, maintainable code.

Emulate bidirectional or random-access cursors

PL/SQL only allows you to fetch forward through a cursor’s result set. But if I load the result set of a cursor into a collection, I can move back and forth through that set and can instantly (and repetitively) access any particular row in the set.

Improve performance of lookups

Performance improves when you store lists of subordinate information directly in the column of a table (as a nested table or VARRAY), rather than normalizing that data into a separate relational table. (Nested tables, VARRAYs, and associative arrays are collection types described in the upcoming section “Types of Collections.”)

Cache database information

Collections are appropriate for caching ...