Collections in PL/SQL: An Introduction

Written by Bob Watkins, Managing Partner of B. Watkins

Collections are an essential tool in PL/SQL programming. In this article, we’ll take a look at what collections are, why they’re important, and how to declare and use them.

What are collections?

Collections are datatypes that you use to declare variables in your PL/SQL program. Unlike built-in datatypes like NUMBER and VARCHAR2, collection datatypes are user-defined: you must first declare them, give them a name, and decide how they are structured.  Then you can declare variables based upon the new datatypes.

Oracle defines a collection as:
“an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.”  (Oracle Database PL/SQL Language Reference, 11g Release 1, p. 1-10)

Creating a collection in a PL/SQL program is a three-step process:

1. Decide which kind of collection to use, based on your requirements.
2. Use the TYPE command to declare a new user-defined datatype that defines the structure of the collection.
3. Declare one or more variables using the new datatype.

Why are collections important?

Collections can be used to create essential programming data structures such as arrays, sets, hashes, stacks, and queues.   If your processing requires any of these types of data structures, you can use collections in PL/SQL to implement them.

One of the best uses of collections, however, is to speed up database processing by doing multi-row fetches. When you use a SELECT … INTO… statement, you’re fetching only one row at a time from the database. Even when you use a cursor FETCH statement or the cursor FOR loop, you’re still only getting one row at a time. But the BULK COLLECT form of SELECT … INTO … can fetch multiple rows at once, leading to fewer round trips to the database and therefore improved performance.

Yet another reason that collections are useful in PL/SQL is that Oracle cursors are sequential: you must fetch the rows in order, and can only move forward through the cursor.  (Some databases have random-access cursors, which allow you to re-visit a row previously FETCHed, but Oracle does not.) Collections enable you to read the result set of a cursor just once, load it into a collection, then access the rows as many times as you want and in any order without re-opening the cursor.

Which kind of collection should I use?

PL/SQL includes three different kinds of collections:

• PL/SQL Tables, also called Associative Arrays.
• Nested Tables
• Variable arrays (Varrays)

Each has different properties and can be used to create different data structures. The table below summarizes the characteristics of the three kinds.

Nested Tables and Varrays are SQL datatypes as well as PL/SQL datatypes. In a database table, for example, you can specify that the telephone number column is actually a Varray containing multiple phone numbers. PL/SQL Tables cannot be used in the database in this way. They’re only found within PL/SQL programs.

However, PL/SQL tables may use zero or negative numbers as element subscripts, or even text strings. Nested Table and Varray subscripts are always positive integers.

Of the three types, only the Varray has a predefined upper limit to the number of elements it contains. The other two have no declared limit (though there are maximum limits.) Likewise, the Varray must remain dense; that is, it cannot have gaps in the element numbering. It can have any number of elements up to its maximum, but those elements must be numbered 1 to the current number of elements. Nested tables and PL/SQL tables allow gaps in numbering of their elements.

Nested tables and Varrays cannot be used until they are initialized by calling a function called a constructor function.  PL/SQL tables are ready for use immediately upon declaration.

Let’s say we want to create a lookup table of the U.S. states. Based on the descriptions above, which of the three collection types should we use? You might think to use an array, because we know there are only 50 states.  That could change in the future, however. We could set the maximum to 60, or even 100, to account for this. But the real determining factor is that the lookup value will be alphanumeric. We’ll have to use a PL/SQL table (associative array), because it’s the only type that allows non-numeric subscripts.

Or, assume we want to store up to five phone numbers in each customer row in the database. We could create five columns in the customer table. Or we could create one column that is a collection. The fact that we want to create this column in the database rules out PL/SQL tables: it must be a Nested Table or a Varray.  Because of the hard limit of 5 phone numbers and the desire to not have gaps in our phone numbers list, a Varray is the best choice.

Declaring and using collections

Here are three examples of declaring and using collections.  If you have the HR example schema from Oracle in your test database, you can log into it and run these examples yourself.
The first example, demo1.sql, uses a PL/SQL table to contain descriptions for alphanumeric action codes (A for Add, C for Change, D for Delete) within a program. First, we declare the new datatype as a PL/SQL table, indexed by a VARCHAR2 subscript:

The INDEX BY clause makes this a PL/SQL table, not a Nested Table. The subscripts are text with up to two characters, and the data values are also text with up to 60 characters. Once the datatype is declared, we can now build variables of that type:

Once this type is declared, we can declare as many variables of this new type as we wish. In addition to the action codes, we could have a table of US State codes, or a table of country codes, and use the same collection type (ALPHA_LOOKUP_TABLE) for all of them.

Next, we load the collection with data. Because this table is small, we just use PL/SQL assignment statements.  Note the use of text strings as subscripts to define which element we’re working with:

When we need to build a print line and include the description in it, we can use the actions collection.  A local variable named action_code has one of the action codes in it. We use the PL/SQL table to look up the matching description:

The EXISTS method checks first to see that the current value of action_code exists as an element in the collection. If it does, we use action_code to retrieve the description.  If it does not, we use ‘Unknown’ as a description.  If we tried to look up an action code that didn’t exist, we would get a NO_DATA_FOUND exception.

The second example, demo2.sql, shows how to create single-column collections and use them to BULK COLLECT data from the database, instead of using a cursor and fetching rows one at a time.
We declare the collection datatypes first, then declare variables of those types:

Note that there are no INDEX BY clauses; this declares the collections to be Nested Tables, not PL/SQL tables. Because of this, we must initialize each collection by calling its constructor function, which has the same name as the user-defined type.  So the definition of loc_addresses above says: “Create a variable named loc_addresses, of datatype LOC_ADDR_TABLE, and set its initial value to the results of the LOC_ADDR_TABLE constructor function.”  We do the same thing with the variable loc_countries.

Each entry in loc_addresses is a street address. Because we will be loading the collection from the database, we declare it using the indirect reference “locations.street_address%TYPE”. This guarantees the collection will match the datatype in the database.

Now we can use BULK COLLECT to load the collections all at once, instead of declaring a cursor and looping through all the result set.

All of the street addresses go into the collection loc_addresses, and all of the country IDs go into the collection loc_countries. For any given subscript, i, the address and country will match up.  Here is an example of processing these collections:

The COUNT method gives us the number of elements in the collections. For a Nested Table, the subscripts will be integers, numbered from 1, so we can use a FOR…LOOP to process them. The country ID is given by the expression loc_countries(i), and we can use a CASE statement to do something different for each country code.  Likewise, the street address is given by the expression loc_countries(i).

The final example, demo3.sql, shows how to create a collection of records. Each element in the collection will contain not just one variable, but several.  First, a user-defined Record datatype is declared. This defines what one row of the eventual table will look like:

Now that LOC_RECORD_TYPE is a datatype, we can build a collection of them, just as we built a collection of VARCHAR2 datatypes earlier. But now, each element will have all the subfields of the record.

This is a Nested Table (no INDEX BY clause). Finally, we need to build a variable of the new collection datatype and initialize it:

Now we can load the table the same way as we did in demo2.sql, via SELECT INTO.

Processing this Nested Table is different, however, because of the record structure. Each element contains multiple variables, not just one, so we have to say not only which element, but which column in the record to use.

Once again, the COUNT method gives the number of elements. For each element i, the country is given by loc_table(i).loc.country (for the i-th element, pull out the loc_country column).  The street address is given by the expression loc_table(i).loc_address.

Although these examples processed the data sequentially just like a cursor, the performance benefit is that we were able to fetch all the rows at once, avoiding repeat trips to the database. In the case of the action codes, we were able to avoid database access completely by holding the lookup table in the memory of our PL/SQL program.

There are many other uses for collections beyond those shown here, but they are all declared the same way. First, decide which type to use. Second, declare the collection’s structure as a user-defined TYPE. Finally, create one or more variables of the new datatype.