Learning Oracle PL/SQL

9.2. Lists o' Stuff (Collections) in PL/SQL

A few chapters back, I added to the library application a way for patrons to search for books by catalog information. While this feature will tell users whether the book they want is known to the catalog, they won't know if the library actually has any copies. Such a system is not very helpful unless they just want to find out the number of pages or the publication date—information they could probably just get from Amazon.com anyway.

We could, of course, add the missing functionality to the library application using techniques you've already seen in this book, such as setting up a cursor to SELECT the data we want and fetching one row at a time inside a loop that also displays the results. However, I'd like to do it a different way, in order to present another feature of the PL/SQL language: collections. I won't have space to show all the user interface code, but I will give you enough of the underlying support functions to let you know how to work with this feature.

9.2.1. What Is a Collection?

A collection is a data structure that can hold some number of rows of data in a single variable. For example, I might want to assemble a list^[1] of barcodes into a collection and pass it from one program to another. Unlike a record, which holds only one row of data that can contain data of different types, in a collection the data in all the rows must be of the same type. It's sometimes said that records hold heterogeneous data while collections ...