Making Sense of Collections
We have noticed over the years that relatively few developers know about and use collections. This always comes as a surprise, because we have found them to be so handy. One challenge is that collections are relatively complicated. Three different types of collections, multiple steps involved in defining and using them, usage in both PL/SQL programs and database objects, more complex syntax than simply working with individual variables: all of these factors conspire to limit usage of collections.
We have organized this chapter so that we can be comprehensive in our treatment of collections, avoid redundancy in treatment of similar topics across different collection types, and offer guidance in your usage of collections. The resulting chapter is rather long. Here is a quick guide to the remainder of its contents:
Declaring collection types and collections
First, we start by showing you how to declare different types (or templates) of collections, along with the syntax to instantiate specific collections from those types.
Collection built-ins
Next, we explore the many built-in functions or methods that Oracle provides to help you examine and manipulate the contents of a collection.
Working with collections
Now it is time to build on all those "preliminaries" to explore some of the nuances of working with collections, including the initialization process necessary for nested tables and VARRAYs, different ways to populate and access collection data, and so on.
We then finish up the chapter with (a) a look at collection "pseudo-functions" designed to let us manipulate collections as relational tables, and vice versa; and (b) some details on how to maintain collections in the database and choose the most appropriate collection type.
11.3 Declaring Collection Types and Collections
Before you can work with a collection, you must declare it. When you declare a collection, it must be based on a collection type. So the first thing you must learn to do is define a collection type.
There are two different ways of creating user-defined collection types:
· You can define a nested table type or VARRAY type "in the database" using the CREATE TYPE command. This makes the datatype available to use for a variety of purposes: columns in database tables, variables in PL/SQL programs, and attributes of object types.
· You can declare the collection type within a PL/SQL program usingTYPE . . . IS . . . syntax. This collection type will then be available only for use within the block in which the TYPE is defined.