Oracle PL/SQL Programming, 5th 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 in the PL/SQL language to traditional arrays. 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:

Maintain in-program lists of data

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 (which would involve many context switches) or delimited strings, but collections are very efficient structures that can be manipulated with very clean, maintainable code.

Improve multirow SQL operations by an order of magnitude or more

You can use collections in conjunction with FORALL and BULK COLLECT to dramatically improve the performance of multirow SQL operations. These “bulk” operations are covered in detail in Chapter 21.

Cache database information

Collections are appropriate for caching database information that is static and frequently queried in a single session (or simply queried repeatedly in a single program) to speed up performance of those queries.

I have noticed over the years that relatively few developers know about and use collections. This always comes as a surprise, because I find them to be so handy. ...