12.1. Collections Overview
We will start with a description of the different types of collections and a number of examples to get you started.
12.1.1. Types of Collections
Oracle supports three different types of
collections. While these different types have much in common, they also each have their own particular characteristics. Many of the terms mentioned in the definitions below are further explained in the "Collection Concepts and Terminology" section, immediately following.
Associative arrays
These are single-dimensional, unbounded, sparse collections of homogeneous elements that are available only in PL/SQL. They were called PL/SQL tables in PL/SQL 2 and index-by tables in Oracle8 Database and Oracle8i Database (because when you declare such a collection, you explicitly state that they are "indexed by" the row number). In Oracle9i Database Release 1, the name was changed to associative arrays
. The motivation for the name change was that starting with that release, the INDEX BY syntax could be used to "associate" or index contents by VARCHAR2 or PLS_INTEGER.
Nested tables
These are also single-dimensional, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables can be defined in both PL/SQL and the database (for example, as a column in a table). Nested tables are multisets, which means that there is no inherent order to the elements in a nested table.
VARRAYs
Like the other two collection types, VARRAYs (variable-sized arrays) are also single-dimensional collections of homogeneous elements. However, they are always bounded and never sparse. When you define a type of VARRAY, you must also specify the maximum number of elements it can contain. Like nested tables
, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved.
12.1.2. Collections Concepts and Terminology
The following explanations will help you understand collections and more rapidly establish a comfort level with these data structures.
Collection type
Each collection variable in your program must be declared based on a pre-defined collection type. As I mentioned earlier, there are, very generally, three types of collections: associative arrays, nested tables, and VARRAYs. Within those generic types, there are specific types that you define with a TYPE statement in a block's declaration section. You can then declare and use instances of those types in your programs.
Collection or collection instance
The term "collection" may refer to any of the following:
A PL/SQL variable of type associative array, nested table, or VARRAY A table column of type nested table or VARRAY
Regardless of the particular type or usage, however, a collection is at its core a single-dimensional list of homogeneous elements.
A collection instance is an instance of a particular type of collection.
Partly due to the syntax and names Oracle has chosen to support collections, you will also find them referred to as arrays and tables.
Homogeneous elements
The datatype of each row in a collection is the same; thus, its elements are homogeneous. This datatype is defined
by the type of collection used to declare the collection itself. This datatype can, however, be a composite or complex datatype itself; you can declare a table of records, for example. And starting in Oracle9i Database Release 1, you can even define multilevel collections, in which the datatype of one collection is itself a collection type, or a record or object whose attribute contains a collection.
One-dimensional or single-dimensional
A PL/SQL collection always has just a single column of information in each row, and is in this way similar to a one-dimensional array. You cannot define a collection so that it can be referenced as follows:
my_collection (10, 44)
This is a two-dimensional structure and not currently supported with that traditional syntax. Instead, you can create multidimensional arrays
by declaring collections of collections, in which case the syntax you use will be something like this:
my_collection (44) (10)
Unbounded versus bounded
A collection is said to be bounded if there are predetermined limits to the possible values for row numbers in that collection. It is unbounded if there are no upper or lower limits on those row numbers. VARRAYs or variable-sized arrays are always bounded; when you define them, you specify the maximum number of rows allowed in that collection (the first row number is always 1). Nested tables and associative arrays are only theoretically bounded. We describe them as unbounded, because from a theoretical standpoint, there is no limit to the number of rows you can define in them.
Sparse versus dense
A collection (or array or list) is called dense if all rows between the first and last row are defined and given a value (including NULL). A collection is sparse if rows are not defined and populated sequentially; instead, there are gaps between defined rows, as demonstrated in the associative array example in the next section. VARRAYs are always dense. Nested tables always start as dense collections but can be made sparse. Associative arrays can be sparse or dense, depending on how you fill the collection.
Sparseness is a very valuable feature, as it gives you the flexibility to populate rows in a collection using a primary key or other intelligent key data as the row number. By doing so, you can define an order on the data in a collection or greatly enhance the performance of lookups.
Indexed by integers
All collections support the ability to reference a row via the row number, an integer value. The associative array TYPE declaration makes that explicit with its INDEX BY clause, but the same rule holds true for the other collection types.
Indexed by strings
Starting with Oracle9i Database Release 2, it is possible to index an associative array by string values (currently up to 32K in length) instead of by numeric row numbers. This feature is not available for nested tables or VARRAYs.
Outer table
This refers to the enclosing table in which you have used a nested table or VARRAY as a column's datatype.
Inner table
This is the enclosed collection that is implemented as a column in a table; it is also known as a nested table column.
Store table
This is the physical table that Oracle creates to hold values of the inner table (a nested table column).
12.1.3. Collection Examples
This section provides relatively simple examples of
each different type of collection with explanations of the major characteristics.
12.1.3.1 Using an associative array
In the following example, I declare an associative array type and then a collection based on that type. I populate it with four rows of data and then iterate through the collection, displaying the strings in the collection. A more thorough explanation appears after the code.
1 DECLARE
2 TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
3 INDEX BY PLS_INTEGER;
4 happyfamily list_of_names_t;
5 l_row PLS_INTEGER;
6 BEGIN
7 happyfamily (2020202020) := 'Eli';
8 happyfamily (-15070) := 'Steven';
9 happyfamily (-90900) := 'Chris';
10 happyfamily (88) := 'Veva';
11
12 l_row := happyfamily.FIRST;
13
14 WHILE (l_row IS NOT NULL)
15 LOOP
16 DBMS_OUTPUT.put_line (happyfamily (l_row));
17 l_row := happyfamily.NEXT (l_row);
18 END LOOP;
19* END;
SQL> /
Chris
Steven
Veva
Eli
Line(s) | Description |
|---|
2-3 | Declare the associative array TYPE, with its distinctive INDEX BY clause. A collection based on this type contains a list of strings, each of which can be as long as the first_name column in the person table. | 4 | Declare the happyfamily collection from the list_of_names_t type. | 9 -10 | Populate the collection with four names. Notice that I can use virtually any integer value that I like. The row numbers don't have to be sequential in an associative array; they can even be negative! | 12 | Call the FIRST method (a function that is "attached" to the collection) to get the first or lowest defined row number in the collection. | 14-18 | Use a WHILE loop to iterate through the contents of the collection, displaying each row. Line 17 show the NEXT method, which is used to move from the current defined row to the next defined row, "skipping over" any gaps. |
12.1.3.2 Using a nested table
In the following example, I first declare a nested table type as a schema-level type. In my PL/SQL block, I declare three nested tables based on that type. I put the names of everyone in my family into the happyfamily nested table. I put the names of my children in the children nested table. I then use the Oracle Database 10g set operator, MULTISET EXCEPT, to extract just the parents from the happyfamily nested table; finally, I display the names of the parents. A more thorough explanation appears after the code.
REM Section A
SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
2 /
Type created.
REM Section B
SQL>
1 DECLARE
2 happyfamily list_of_names_t := list_of_names_t ( );
3 children list_of_names_t := list_of_names_t ( );
4 parents list_of_names_t := list_of_names_t ( );
5 BEGIN
6 happyfamily.EXTEND (4);
7 happyfamily (1) := 'Eli';
8 happyfamily (2) := 'Steven';
9 happyfamily (3) := 'Chris';
10 happyfamily (4) := 'Veva';
11
12 children.EXTEND;
13 children (1) := 'Chris';
14 children.EXTEND;
15 children (2) := 'Eli';
16
17 parents := happyfamily MULTISET EXCEPT children;
18
19 FOR l_row IN parents.FIRST .. parents.LAST
20 LOOP
21 DBMS_OUTPUT.put_line (parents (l_row));
22 END LOOP;
23* END;
SQL> /
Steven
Veva
Line(s) | Description |
|---|
Section A | The CREATE TYPE statement creates a nested table type in the database itself. By taking this approach, I can declare nested tables in any PL/SQL block that has SELECT authority on the type. I can also declare columns in relational tables of this type. | 2-4 | Declare three different nested tables based on the schema-level type. Notice that in each case I also call a constructor function to initialize the nested table. This function always has the same name as the type and is created for us by Oracle. You must initialize a nested table before it can be used. | 6 | Call the EXTEND method to "make room" in my nested table for the members of my family. Here, in contrast to associative arrays, I must explicitly ask for a row in a nested table before I can place a value in that row. | 7-10 | Populate the happyfamily collection with our names. | 12-15 | Populate the children collection. In this case, I extend a single row at a time. | 17 | To obtain the parents in this family, I simply take the children out of the happyfamily. This is transparently easy to do in releases from Oracle Database 10g onwards, where we have high-level set operators like MULTISET EXCEPT (very similar to the SQL MINUS). | 19-22 | Because I know that my parents collection is densely filled from the MULTISET EXCEPT operation, I can use the numeric FOR loop to iterate through the contents of the collection. This construct will raise a NO_DATA_FOUND exception if used with a sparse collection. |
12.1.3.3 Using a VARRAY
In the following example, I demonstrate the use of VARRAYs as columns in a relational table. First, I declare two different schema-level VARRAY types. I then create a relational table, family, that has two VARRAY columns. Finally, in my PL/SQL code, I populate two local collections and then use them in an INSERT into the family table. A more thorough explanation appears after the code.
REM Section A
SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
2 /
Type created.
SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
2 /
Type created.
REM Section B
SQL> CREATE TABLE family (
2 surname VARCHAR2(1000)
3 , parent_names first_names_t
4 , children_names child_names_t
5 );
Table created.
REM Section C
SQL>
1 DECLARE
2 parents first_names_t := first_names_t ( );
3 children child_names_t := child_names_t ( );
4 BEGIN
5 parents.EXTEND (2);
6 parents (1) := 'Samuel';
7 parents (2) := 'Charina';
8 --
9 children.EXTEND;
10 children (1) := 'Feather';
11
12 --
13 INSERT INTO family
14 (surname, parent_names, children_names
15 )
16 VALUES ('Assurty', parents, children
17 );
18 END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM family
2 /
SURNAME
PARENT_NAMES
CHILDREN_NAMES
--------------------------------------------
Assurty
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')
Line(s) | Description |
|---|
Section A | Use CREATE TYPE statements to declare two different VARRAY types. Notice that with a VARRAY, I must specify the maximum length of the collection. Thus, my declarations in essence dictate a form of social policy: you can have at most two parents and at most one child. | Section B | Create a relational table, with three columns: a VARCHAR2 column for the surname of the family and two VARRAY columns, one for the parents and another for the children. | Section C, lines 2-3 | Declare two local VARRAYs based on the schema-level type. As with nested tables (and unlike with associative arrays), I must call the constructor function of the same name as the TYPE to initialize the structures. | 5 -10 | Extend and populate the collections with the names of parents and then the single child. If I try to extend to a second row, Oracle will raise the ORA-06532: Subscript outside of limit error. | 13-17 | Insert a row into the family table, simply providing the VARRAYs in the list of values for the table. Oracle certainly makes it easy for us to insert collections into a relational table! |
12.1.4. Where You Can Use Collections
The following sections describe the different places in your code where a collection can be declared and used. Because a collection type can be defined in the database itself (nested tables and VARRAYs only), you can find collections not only in PL/SQL programs but also inside tables and object types.
12.1.4.1 Collections as components of a record
Using a collection type in a record is similar to using any other type. You can use associative arrays, nested tables, VARRAYs, or any combination thereof in RECORD datatypes. For example:
DECLARE
TYPE toy_rec_t IS RECORD (
manufacturer INTEGER,
shipping_weight_kg NUMBER,
domestic_colors Color_array_t,
international_colors Color_tab_t
);
12.1.4.2 Collections as program parameters
Collections can also serve as parameters in functions and procedures. The format for the parameter declaration is the same as with any other:
parameter_name [ IN | IN OUT | OUT ] parameter_type
[ DEFAULT | := <default_value> ]
PL/SQL does not offer any predefined collection types. This means that before you can pass a collection as an argument, you must have already defined the collection type that will serve as the parameter type. You can do this by:
Defining a schema-level type with CREATE TYPE Declaring the collection type in a package specification Declaring that type in an outer scope from the definition of the module
Here is an example of using a schema-level type:
CREATE TYPE yes_no_t IS TABLE OF CHAR(1);
/
CREATE OR REPLACE PROCEDURE act_on_flags (flags_in IN yes_no_t)
IS
BEGIN
...
END act_on_flags;
/
Here is an example of using a collection type defined in a package specification: there is only one way to declare an associative array of Booleans (and all other base datatypes), so why not define them once in a package specification and reference them throughout my application?
/* File on web: aa_types.pks */
CREATE OR REPLACE PACKAGE aa_types
IS
TYPE boolean_aat IS TABLE OF BOOLEAN
INDEX BY BINARY_INTEGER;
...
END aa_types;
/
Notice that when I reference the collection type in my parameter list, I must qualify it with the package name:
CREATE OR REPLACE PROCEDURE act_on_flags (
flags_in IN aa_types.boolean_aat)
IS
BEGIN
...
END act_on_flags;
/
Finally, here is an example of declaring a collection type in an outer block and then using it in an inner block:
DECLARE
TYPE birthdates_aat IS VARRAY (10) OF DATE;
l_dates birthdates_aat := birthdates_aat ( );
BEGIN
l_dates.EXTEND (1);
l_dates (1) := SYSDATE;
DECLARE
FUNCTION earliest_birthdate (list_in IN birthdates_aat)
RETURN DATE
IS
BEGIN
...
END earliest_birthdate;
BEGIN
DBMS_OUTPUT.put_line (earliest_birthdate (l_dates));
END;
END;
12.1.4.3 Collections as datatypes of a function's return value
In the next example, we have defined Color_tab_t as the type of a function return value, and also used it as the datatype of a local variable. The same restriction about scope applies to this usage: types must be declared outside the module's scope.
CREATE FUNCTION true_colors (whose_id IN NUMBER) RETURN Color_tab_t
AS
l_colors Color_tab_t;
BEGIN
SELECT favorite_colors INTO l_colors
FROM personality_inventory
WHERE person_id = whose_id;
RETURN l_colors;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
How would you use this function in a PL/SQL program? Because it acts in the place of a variable of type Color_tab_t, you can do one of two things with the returned data:
Assign the entire result to a collection variable. Assign a single element of the result to a variable (as long as the variable is of a type compatible with the collection's elements).
Option #1 is easy. Notice, by the way, that this is another circumstance where you don't have to initialize the collection variable explicitly:
DECLARE
color_array Color_tab_t;
BEGIN
color_array := true_colors (8041);
END;
With Option #2, we actually give the function call a subscript. The general form is:
variable_of_element_type := function() (subscript);
Or, in the case of the true_colors function:
DECLARE
one_of_my_favorite_colors VARCHAR2(30);
BEGIN
one_of_my_favorite_colors := true_colors (whose_id=>8041) (1);
END;
Note that this code has a small problem: if there is no record in the database table where person_id is 8041, the attempt to read its first element will raise a COLLECTION_IS_NULL exception. We should trap and deal with this exception in a way that makes sense to the application.
In the previous example, I've used named parameter notation (whose_id=>) for readability, although it is not strictly required. (See Chapter 17 for more details.)
12.1.4.4 Collection as "columns" in a database table
Using a nested table or VARRAY, you can store and retrieve nonatomic data in a single column of a table. For example, the employee table used by the HR department could store the date of birth for each employee's dependents in a single column, as shown in Table 12-1.
Table 12-1. Storing a column of dependents as a collection in a table of employeesId (NUMBER) | Name (VARCHAR2) | Dependents_ages (Dependent_birthdate_t) |
|---|
10010 | Zaphod Beeblebrox | 12-JAN-1763 | | | | 4-JUL-1977 | | | | 22-MAR-2021 | 10020 | Molly Squiggly | 15-NOV-1968 | | | | 15-NOV-1968 | 10030 | Joseph Josephs | | 10040 | Cepheus Usrbin | 27-JUN-1995 | | | | 9-AUG-1996 | | | | 19-JUN-1997 | 10050 | Deirdre Quattlebaum | 21-SEP-1997 |
It's not terribly difficult to create such a table. First we define the collection type:
CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;
Now we can use it in the table definition:
CREATE TABLE employees (
id NUMBER,
name VARCHAR2(50),
...other columns...,
Dependents_ages Dependent_birthdate_t
);
We can populate this table using the following INSERT syntax, which relies on the type's default constructor to transform a list of dates into values of the proper datatype:
INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ...,
Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021'));
Now let's look at an example of a nested table datatype as a column. When we create the outer table personality_inventory, we must tell Oracle what we want to call the "store table."
CREATE TABLE personality_inventory (
person_id NUMBER,
favorite_colors Color_tab_t,
date_tested DATE,
test_results BLOB)
NESTED TABLE favorite_colors STORE AS favorite_colors_st;
The NESTED TABLE ... STORE AS clause
tells Oracle that we want the store table for the favorite_colors column to be called favorite_colors_st. There is no preset limit on how large this store table, which is located "out of line" (or separate from the rest of that row's data to accommodate growth) can grow.
You cannot directly manipulate data in the store table, and any attempt to retrieve or store data directly into favorite_colors_st will generate an error. The only path by which you can read or write the store table's attributes is via the outer table. (See the discussion of collection pseudo-functions in the later section, "Working with Collections in SQL," for a few examples of doing so.) You cannot even specify storage parameters for the store table; it inherits the physical attributes of its outermost table.
One chief difference between nested tables and VARRAYs surfaces when we use them as column datatypes
. Although using a VARRAY as a column's datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table's data. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays.
12.1.4.5 Collections as attributes of an object type
In this example, we are modeling automobile specifications. Each Auto_spec_t object will include a list of manufacturer's colors in which you can purchase the vehicle.
CREATE TYPE Auto_spec_t AS OBJECT (
make VARCHAR2(30),
model VARCHAR2(30),
available_colors Color_tab_t
);
Because there is no data storage required for the object type, it is not necessary to designate a name for the companion table at the time we issue the CREATE TYPE ... AS OBJECT
statement.
When the time comes to implement the type as, say, an object table, you could do this:
CREATE TABLE auto_specs OF Auto_spec_t
NESTED TABLE available_colors STORE AS available_colors_st;
This statement requires a bit of explanation. When you create a "table of objects," Oracle looks at the object type definition to determine what columns you want. When it discovers that one of the object type's attributes, available_colors, is in fact a nested table, Oracle treats this table as it did in earlier examples; in other words, it wants to know what to name the store table. So the phrase:
...NESTED TABLE available_colors STORE AS available_colors_st
says that you want the available_colors column to have a store table named available_colors_st.
See Chapter 25 for more information about Oracle object types.
12.1.5. Choosing a Collection Type
Which collection type makes sense for your application? In some cases, the choice is obvious. In others, there may be several acceptable choices. This section provides some guidance. Table 12-2 illustrates many of the differences between associative arrays, nested tables, and VARRAYs.
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. Why is this? They involve the least amount of coding. You don't have to initialize or extend them. They have historically been the most efficient collection type (although this distinction will probably fade over time). However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
The following guidelines will help you make your choice; we recommend, however, that you read the rest of the chapter first if you are not very familiar with collections already.
If you need sparse associative arrays (for example, for "data-smart" storage), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable (as illustrated in the later section on NEXT and PRIOR methods), but it is inefficient to do so for anything but the smallest collections. If your PL/SQL application requires negative subscripts, you also have to use associative arrays. If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays. If you want to enforce a limit to the number of rows stored in a collection, use VARRAYs. If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth. If you want to preserve the order of elements stored in the collection column and if your dataset will be small, use a VARRAY. What is "small?" I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K. Here are some other indications that a VARRAY would be appropriate: you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.
Table 12-2. Comparing Oracle collection typesCharacteristic | Associative array | Nested table | VARRAY |
|---|
Dimensionality | Single | Single | Single | Usable in SQL? | No | Yes | Yes | Usable as column datatype in a table? | No | Yes; data stored "out of line" (in separate table) | Yes; data stored "in line" (in same table) | Uninitialized state | Empty (cannot be null); elements undefined | Atomically null; illegal to reference elements | Atomically null; illegal to reference elements | Initialization | Automatic, when declared | Via constructor, fetch, assignment | Via constructor, fetch, assignment | In PL/SQL, elements | referenced via | BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)
VARCHAR2 (Oracle9i Database Release 2 and above) | Positive integer between 1 and 2,147,483,647 | Positive integer between 1 and 2,147,483,647 | Sparse? | Yes | Initially, no; after deletions, yes | No | Bounded? | No | Can be extended | Yes | Can assign value to any | element at any time? | Yes | No; may need to EXTEND first | No; may need to EXTEND first, and cannot EXTEND past upper bound | Means of extending | Assign value to element with a new subscript | Use built-in EXTEND procedure (or TRIM to condense), with no predefined | maximum | EXTEND (or TRIM), but only up to declared maximum size | Can be compared for equality? | No | Yes, in Oracle Database 10g | No | Can be manipulated with set operators | No | Yes, in Oracle Database 10g | No | Retains ordering and subscripts when stored in and retrieved from database? | N/A | No | Yes |
|
