Oracle Magazine - July/August 2012

1. Declare an associative array of numbers, indexed by integer:

Code Listing 1: Nested table example

TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

2. Declare an associative array of numbers, indexed by string:

TYPE numbers_aat IS TABLE OF NUMBER INDEX BY VARCHAR2(100);

3. Declare a nested table of numbers:

TYPE numbers_nt IS TABLE OF NUMBER;

4. Declare a varray of numbers:

TYPE numbers_vat IS VARRAY( 10)

OF NUMBER;

1 DECLARE

2 TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); 3

4 happyfamily list_of_names_t := list_of_names_t (); 5 children list_of_names_t := list_of_names_t (); 6 parents list_of_names_t := list_of_names_t (); 7 BEGIN

8 happyfamily.EXTEND ( 4);

9 happyfamily ( 1) := ‘Veva’; 10 happyfamily ( 2) := ‘Chris’; 11 happyfamily ( 3) := ‘Eli’; 12 happyfamily ( 4) := ‘Steven’; 13

14 children.EXTEND; 15 children ( children.LAST) := ‘Chris’;

16 children.EXTEND; 17 children ( children.LAST) := ‘Eli’; 18 19 parents := happyfamily MULTISET EXCEPT children; 20 21 FOR l_row IN 1 .. parents.COUNT 22 LOOP 23 DBMS_OUTPUT.put_line (parents (l_row)); 24 END LOOP; 25 END;

Note: I use the suffixes _aat, _nt, and _vat, for associative array type, nested table type, and varray type, respectively.

You might be wondering why the syntax for defining a collection type does not use the word collection. The answer is that the IS TABLE OF syntax was first introduced in Oracle7 Server, when there was just one type of collection, the PL/SQL table.

From these examples, you can draw the
following conclusions about collection types:

•;If the T YPE statement contains an INDEX BY clause, the collection type is an associative array.

•;If the T YPE statement contains the VARRAY keyword, the collection type is a varray.

•;If the T YPE statement does not contain an INDEX B Y clause or a VARRAY keyword, the collection type is a nested table.

•;Only the associative array offers a choice of indexing datatypes. Nested tables as well as varrays are always indexed by integer.

•;When you define a varray type, you specify
the maximum number of elements that
can be defined in a collection of that type.

Once you’ve declared a collection type, you can use it to declare a collection variable as you would declare any other kind of variable:

Lines

2

4–6

8 9–12

14–17

19

21–24

Explanation

I declare a new nested table type. Each element in a collection declared with this type is a string whose maximum length is 100.

I declare three nested tables—happyfamily, children, and parents—based on my new collection type. Note that I also assign a default value to each variable by calling a constructor function that has the same name as the type. I “make room” in my happyfamily collection for four elements by calling the EX TEND method. I assign the names of the members of my immediate family (my wife, Veva; my two sons, Chris and Eli; and myself). Note the use of typical single-dimension array syntax to identify an element in the array: array_name (index_value). Now I populate the children nested table with just the names of my sons. Rather than do a “bulk” extend as on line 8, I extend one index value at a time. Then I assign the name to the just-added index value by calling the LAST method, which returns the highest defined index value in the collection. Unless you know how many elements you need in advance, this approach of extending one row and then assigning a value to the new highest index value is the way to go. Both of my children are adults and have moved out of the ancestral home. So who’s left in this place with too many bedrooms? Start with the happyfamily and subtract (with the MULTISE T EXCEP T operator) the children. Assign the result of this set operation to the parents collection. It should be just Veva and Steven. The result of a MULTISE T operation is always either empty or densely filled and starts with index value 1. So I will iterate through all the elements in the collection, from 1 to the COUN T (the number of elements defined in the collection) and display the element found at each index value.

INITIALIZING COLLEC TIONS When you work with nested tables and varrays, you must initialize the collection variable before you can use it. You do this by calling the constructor function for that type. This function is created automatically by Oracle Database when you declare the type. The constructor function constructs an instance of the type associated with the function. You can call this function with no arguments, or you can pass it one or more expressions of the same type as the elements of the collection, and they will be inserted into your collection.