[Chapter 19] 19.4 Using Collections

19.4 Using Collections

There are three main programming tasks you must understand when you are working with collections in PL/SQL:

How to properly initialize the collection variable
How to assign values to elements without raising exceptions
How to add and remove "slots" for elements

In addition, to fully exploit the programming utility of collections, you will want to learn how to retrieve and store sets of data with them. This leads into our section on pseudo-functions, which allow you to perform magic tricks with collections. (Okay, maybe it's not real magic, but you're almost guaranteed to say "How did they do that?" the first time you try to program this stuff and find yourself bewildered.)

19.4.1 Initializing Collection Variables

With an index-by table datatype, initialization is a non-issue. Simply declaring an index-by table variable also initializes it, in an "empty" state. Then you can just assign values to subscripted table elements as you desire. Index values (subscripts) can be almost any positive or negative integer. A program can even assign subscripts to index-by tables arbitrarily, skipping huge ranges of subscripts without paying a memory or performance penalty.[ 2 ]

[2] This sparseness makes it possible to use an index-by table as an in-memory representation of almost any database table which uses an integer primary key. (See Chapter 10 for a discussion of this eminently useful technique.)

The allocation scheme for nested tables and VARRAYs is different from that of index-by tables. First off, if you don't initialize one of these collections, it will be "atomically null," and any attempt to read or write an element of an atomically null collection will generate a runtime error. For example:

DECLARE
   /* The variable cool_colors is not initialized in its
   || declaration; it is "atomically null."
   */
   cool_colors Color_tab_t;
BEGIN
   IF cool_colors IS NULL THEN         -- valid; will be TRUE
      ...
   IF cool_colors(1) IS NULL THEN      -- invalid
      ...
   cool_colors(1) := 'BLUE';           -- invalid

You must initialize the collection before using it. There are three ways you can initialize a collection:

Explicitly, using a constructor
Implicitly, via a fetch from the database
Implicitly, via a direct assignment of another collection variable

There is no requirement that you initialize any particular number of elements in a collection. Zero, one, or more are fine, and you can always add more values later. In particular, don't be confused by VARRAYs. Just because you specify a limit on the number of elements it can hold does not imply that you have to put that many elements in when you initialize it.

19.4.1.1 Initializing with a constructor

Earlier, we saw declarations that looked like this:

my_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN');
my_favorite_numbers Number_t := Number_t(42, 65536);

Color_tab_t( ) is the constructor function supplied by Oracle when we created the Color_tab_t collection type. This function accepts an arbitrary number of arguments, as long as each argument is of the "proper" datatype -- which in this case is VARCHAR2(30), since our original type definition statement was the following:

CREATE TYPE Color_tab_t AS TABLE OF 
VARCHAR2(30)
;

At initialization, Oracle allocates to the variable an amount of memory necessary to hold the values you supply as arguments. Initialization both creates the "slots" for the elements and populates them.

So, if I want to "fix" the earlier invalid example, I can simply initialize the variable:

DECLARE
   cool_colors Color_tab_t := Color_tab_t('VIOLET');  -- initialize
BEGIN
   IF cool_colors(1) IS NULL THEN   -- This is OK now!

What do you suppose Oracle does with the following initialization?

   working_colors Color_tab_t := Color_tab_t();

This is a way of creating an "empty" collection. Empty is a sort of enigmatic state in which the collection is not atomically null but still has no data. Whenever you create such an empty collection, you'll need to "extend" the collection variable later when you want to put elements into it. (The EXTEND built-in is explored later in this chapter.)

19.4.1.2 Initializing implicitly during direct assignment

You can copy the entire contents of one collection to another as long as both are built from the exact same datatype. When you do so, initialization comes along "for free."

Here's an example illustrating implicit initialization that occurs when we assign wedding_colors to be the value of earth_colors.

DECLARE
   earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');
   wedding_colors Color_tab_t;
BEGIN
   wedding_colors := earth_colors;
   wedding_colors(3) := 'CANVAS';
END;

This code initializes wedding_colors and creates three elements that match those in earth_colors. These are independent variables rather than pointers to identical values; changing the third element of wedding_colors to 'CANVAS' does not have any effect on the third element of earth_colors.

Note that assignment is not possible when datatypes are merely "type-compatible." Even if you have created two different types with the exact same definition, the fact that they have different names makes them different types. A collection variable cannot be assigned to another variable of a different datatype:

DECLARE
   TYPE Local_colors_t IS VARRAY(16) OF VARCHAR2(30);
   TYPE Remote_colors_t IS VARRAY(16) OF VARCHAR2(30);
   l_color Local_colors_t := Local_colors_t('THALO BLUE');
   r_color Remote_colors_t;
BEGIN
   r_color := l_color;  -- invalid
END;

This code will fail with the compile-time error "PLS-00382: expression is of wrong type," because r_color and l_color are of different types.

19.4.1.3 Initializing implicitly via fetch

If you use a collection as a type in a database table, Oracle provides some very elegant ways of moving the collection between PL/SQL and the table. As with direct assignment, when you use FETCH or SELECT INTO to retrieve a collection and drop it into a collection variable, you get automatic initialization of the variable. Collections can turn out to be incredibly useful!

Although we mentioned this briefly in an earlier example, let's take a closer look at how you can read an entire collection in a single fetch. First, we want to create a table containing a collection and populate it with a couple of values:

CREATE TABLE color_models (
   model_type VARCHAR2(12),
   colors Color_tab_t)
NESTED TABLE colors STORE AS color_model_colors_tab;

insert into color_models
   values ('RGB', Color_tab_t('RED','GREEN','BLUE'));
insert into color_models
   values ('CYMK',Color_tab_t('CYAN','YELLOW','MAGENTA','BLACK'));

Now we can show off the neat integration features. With one trip to the database we can retrieve all of the values of the "colors" column for a given row, and deposit them into a local variable:

DECLARE
   l_colors Color_tab_t;
BEGIN
   /* Retrieve all the nested values in a single fetch.
   || This is the cool part.
   */
   SELECT colors INTO l_colors FROM color_models
      WHERE model_type = 'RGB';

   /* Loop through each value and print it. This is only to demonstrate
   || that we really have the data in the local variable.
   */
   FOR element IN 1..l_colors.COUNT
   LOOP
      dbms_output.put_line (element || ' ' || l_colors(element));
   END LOOP;
END;

With SERVEROUTPUT turned on, SQL*Plus prints the following when this code fragment executes:

1 RED
2 GREEN
3 BLUE

Pretty neat, huh? A few important points to notice:

Oracle, not the programmer, assigns the subscripts of l_colors when fetched from the database.
Oracle's assigned subscripts begin with 1 (as opposed to 0, as you may be used to in some other languages) and increment by 1.
Fetching satisfies the requirement to initialize the local collection variable before assigning values to elements. We didn't initialize l_colors with a constructor, but PL/SQL knew how to deal with it.

You can also make changes to the contents of the nested table and just as easily move the data back into a database table. Just to be mischievous, let's create a Fuschia-Green-Blue color model:

DECLARE
   color_tab Color_tab_t;
BEGIN
   SELECT colors INTO color_tab FROM color_models
      WHERE model_type = 'RGB';
   FOR element IN 1..color_tab.COUNT
   LOOP
      IF color_tab(element) = 'RED'
      THEN
         color_tab(element) := 'FUSCHIA';
      END IF;
   END LOOP;
   /* Here is the cool part of this example. Only one insert
   || statement is needed -- it sends the entire nested table
   || back into the color_models table in the database.
   */
   INSERT INTO color_models VALUES ('FGB', color_tab);






END;

19.4.1.4 VARRAY integration

Does this database-to-PL/SQL integration work for VARRAYs too? You bet, although there are a couple of differences.

First of all, realize that when you store and retrieve the contents of a nested table in the database, Oracle makes no promises about preserving the order of the elements. This makes sense, because the server is just putting the nested data into a store table behind the scenes, and we all know that relational databases don't give two hoots about row order. By contrast, storing and retrieving the contents of a VARRAY does preserve the order of the elements.

Preserving the order of VARRAY elements is actually a fairly useful capability. It makes possible something you cannot do in a pure relational database: embedding meaning in the order of the data. For example, if you want to store someone's favorite colors in rank order, you can do it with a single VARRAY column. Every time you retrieve the column collection, its elements will be in the same order as when you last stored it. By contrast, abiding by a pure relational model, you would need two columns, one for an integer corresponding to the rank, and one for the color.

Thinking about this order-preservation of VARRAYs brings to mind some interesting utility functions. For example, you could fairly easily code a tool that would allow the insertion of a new "favorite" at the low end of the list by "shifting up" all the other elements.

A second difference between integration of nested tables and integration of VARRAYs with the database is that some SELECT statements you could use to fetch the contents of a nested table will have to be modified if you want to fetch a VARRAY. (See Section 19.5 later for some examples.)

19.4.2 Assigning Values to Elements: Index (Subscript) Considerations

In contrast to index-by tables, you can't assign values to arbitrarily numbered subscripts of nested tables and VARRAYs; instead, the indexes, at least initially, are monotonically increasing integers, assigned by the PL/SQL engine. That is, if you initialize n elements, they will have subscripts 1 through n. And, as implied above, you cannot rely on the assignment of particular subscripts to particular element values in nested tables. Yes, any element can be null, but null is different from nonexistent (sparse).

Nested tables are initially dense, with no skipped subscripts. Once a nested table exists, however, it is possible to remove any element from it, even one in the "middle." This will result in a sparse array.

VARRAYs, on the other hand, are always dense. Elements of VARRAYs can only be removed from the "end" of the array, so VARRAYs cannot be coerced into being sparse.

However, if what you want is a sparse array in PL/SQL, you would be much better off using an index-by table. The real strength of nested tables and VARRAYs is their ability to move gracefully in and out of the database.

19.4.3 Adding and Removing Elements

With an old-style index-by table, Oracle automatically allocates memory for new elements. When you want to add an element, you simply pick a value for the subscript and assign a value to that element. To remove an element, you could use the DELETE method. To illustrate:

DECLARE
   TYPE color_tab_type IS TABLE OF VARCHAR2(30)
      INDEX BY BINARY_INTEGER;
   color_tab color_tab_type;
BEGIN
   color_tab(3845) := 'SEAFOAM GREEN';
   color_tab(702) := 'CERULEAN BLUE';
   color_tab.DELETE(3845);
END;

What would happen if you tried this assignment with a nested table? Aha, you say, knowing that subscripts start with 1 and are monotonically increasing, I'll just try:

DECLARE
   /* colors starts life initialized by empty */
   colors Color_tab_t := Color_tab_t();
BEGIN
   colors(1) = 'SEAFOAM GREEN'; -- invalid

But this code produces an "ORA-06533, Subscript beyond count" error. This is why you need EXTEND.

19.4.3.1 Adding elements using EXTEND

Adding an element to a collection requires a separate allocation step. Making a "slot" in memory for a collection element is independent from assigning a value to it. If you haven't initialized the collection with a sufficient number of elements (null or otherwise), you must first use the EXTEND procedure on the variable. (For the formal syntax and usage of this procedure, refer to Section 19.6, "Collection Built-Ins" .)

DECLARE
   /* The colors variable begins life initialized but with
   || no elements allocated
   */
   colors Color_tab_t := Color_tab_t();
BEGIN
   colors.EXTEND;  -- allocate space for a single element
   colors(1) := 'GRANITE';  -- this works
   colors(2) := 'HUNTER GREEN';  -- invalid; we only extended by 1
END;

19.4.3.2 Removing elements using DELETE

When you DELETE an element, PL/SQL removes that element from the collection. Interestingly, it doesn't actually remove all traces of the element; in fact, a placeholder gets left behind. That means you can reassign the element later without having to re-allocate the space.

DELETE has three different forms depending on how much you want to remove: one, several (contiguous), or all of the elements. Section 19.6 describes all the forms of this procedure.

In physical terms, PL/SQL actually releases the memory only when your program deletes a sufficient number of elements to free an entire page of memory (unless you DELETE all of the elements, which frees all of the memory immediately). This de-allocation happens automatically and requires no accommodations or devices in your code.

19.4.3.3 ...And what about TRIM?

TRIM is another built-in which lets you remove elements from a collection; it's equally applicable to nested tables and VARRAYs. (Again, refer to Section 19.6 for details.) As its name implies, TRIM drops elements off the end of a collection. Unlike DELETE, TRIM leaves no placeholder behind when it does its work.

Although my programming exercise above didn't need TRIM, this built-in, combined with EXTEND, can be very useful if you want to program a "stack" abstraction. In general, the syntax is simply the following:


collection_name
.TRIM(
n
);

where n is the number of elements to remove. If you don't supply n , it defaults to 1.

Unfortunately, if you use TRIM and DELETE on the same collection, you can get some very surprising results. Consider this scenario: if you DELETE an element at the end of a nested table variable and then do a TRIM on the same variable, how many elements have you removed? You would think that you have removed two elements, but, in fact, you have removed only one. The placeholder that is left by DELETE is what TRIM acts upon.

TIP: To avoid confusion, Oracle Corporation recommends using either DELETE or TRIM, but not both, on a given collection.

19.4.4 Comparing Collections

Unfortunately, there is no built-in capability to compare collections and determine whether one is "equal to" or "greater than" the other. Attempts to do so will produce compile-time errors. The only comparison that is legal for collections is a test for nullness, as we saw previously:

DECLARE
   cool_colors Color_tab_t;
BEGIN
   IF cool_colors IS NULL THEN         -- valid; will be TRUE

If comparing collections is important to your application, you could put an object "container" around the collection, and use objects instead of collections as the structure that your applications manipulate. Doing so allows you to define your own object comparison semantics. Although Chapter 18 provides a detailed discussion of defining your own object comparisons using the MAP and ORDER methods, we'll divert momentarily to illustrate how this technique will help us compare collections.

Without repeating a lot of descriptive detail that you'll find in Chapter 18 , your object type specification might look quite simple:

CREATE TYPE Color_object_t AS OBJECT (
   crayons Color_array_t,
   ORDER MEMBER FUNCTION compare(c_obj Color_object_t) RETURN INTEGER);

This creates an object with a single attribute, crayons, and a special method that Oracle will use when it needs to compare instances of type Color_object_t.

The object type body could be implemented as follows:

CREATE TYPE BODY Color_object_t AS
   ORDER MEMBER FUNCTION compare(c_obj Color_object_t) RETURN INTEGER
      IS
   BEGIN
      /* If one object has more elements than the other, it is
      || by our convention defined to be "greater" than the other.
      */
      IF nvl(SELF.crayons.COUNT,0) > nvl(c_obj.crayons.COUNT,0)
      THEN
         RETURN 1;
      ELSIF nvl(SELF.crayons.COUNT,0) < nvl(c_obj.crayons.COUNT,0)
      THEN
         RETURN -1;
      ELSE
         /* Otherwise we compare the individual elements.
         || If the two arrays have the same number of elements,
         || we'll call them "equal."
         */
         RETURN 0;
      END IF;
   END compare;
END;

In PL/SQL, you can now compare objects of type Color_object_t to your heart's content, achieving a kind of de facto collection comparison:

DECLARE
   color_object_1 Color_object_t
      := Color_object_t(Color_array_t('BLUE','GREEN','RED'));
   color_object_2 Color_object_t := color_object_1;
BEGIN
   ...
   IF color_object_1 = color_object_2 THEN
      ...
END;

And if you needed this structure as a column in a table, it could go something like this:

CREATE TABLE kids_coloring_kits (
   NAME VARCHAR2(30),
   crayon_colors Color_object_t
);

Once the table is populated, you can then use SQL sorting features such as ORDER BY, GROUP BY, and DISTINCT on the crayon_colors column, since your ORDER member function tells Oracle how to compare values in the column.