[Chapter 19] 19.2 Creating the New CollectionsChapter 19Nested Tables and VARRAYs 19.2 Creating the New Collections There are two different ways of creating the new user-defined collection types: You can define a nested table type or VARRAY type "in the database" using the CREATE TYPE command, which 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 using TYPE ... IS ... syntax. This collection type will then be available only for use within PL/SQL. Let's look at a few examples that illustrate how to create collections. 19.2.1 Collections "In the Database" Before you can define a database table containing a nested table or VARRAY, you must first create the collection's datatype in the database using the CREATE TYPE statement. There is no good analogy for this command in Oracle7; it represents new functionality in the server. If we wanted to create a nested table datatype for variables that will hold lists of color names, we'll specify: CREATE TYPE Color_tab_t AS TABLE OF VARCHAR2(30);This command stores the type definition for the Color_tab_t nested table in the data dictionary. Once created, it can serve as the datatype for items in at least two different categories of database object: A "column" in a conventional tableAn attribute in an object typeDefining a VARRAY datatype is similar to defining a nested table, but you must also specify an upper bound on the number of elements collections of this type may contain. For example: CREATE TYPE Color_array_t AS VARRAY (16) OF VARCHAR2(30);Type Color_array_t has an upper limit of 16 elements regardless of where it is used. While these examples use VARCHAR2, collections can also consist of other primitive datatypes, object types, references to object types, or (in PL/SQL only) PL/SQL record types. To show something other than a table of scalars, let's look at an example of a VARRAY of objects. Here we define an object type that will contain information about documents: CREATE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000) );We can then define a collection type to hold a list of these objects:CREATE TYPE Doc_array_t AS VARRAY(10) OF Doc_t;In this case, we've chosen to make it a variable-size array type with a maximum of ten elements. Another useful application of collections is in their ability to have elements which are REFs (reference pointers) to objects in the database. That is, your collection may have a number of pointers to various persistent objects (see Chapter 18, Object Types, for more discussion of REFs). Consider this example: CREATE TYPE Doc_ref_array_t AS TABLE OF REF Doc_t;This statement says "create a user-defined type to hold lists of pointers to document objects." You can use a nested table of REFs as you would any other nested table: as a column, as an attribute in an object type, or as the type of a PL/SQL variable. NOTE: While Oracle 8.0.3 allows you to create homogeneous collections, in some cases we might want to build heterogeneous collections. It would be useful to be able to define a type like the following: CREATE TYPE Generic_ref_t AS TABLE OF REF ANY; -- not in 8.0.3This could allow you to make collections that hold references to more than one type of object in your database ... or, if OID's are globally unique, each REF could point to any object in any database on your entire network!119.2.1.1 Collection as a "column" in a conventional tableIn the following case, we are using 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 "out of line" 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. 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 its attributes is via the outer table. (See Section 19.5, "Collection Pseudo-Functions" 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. As you would expect, if you use a VARRAY as a column rather than as a nested table, no store table is required. Here, the colors collection is stored "in line" with the rest of the table: CREATE TABLE birds ( genus VARCHAR2(128), species VARCHAR2(128), colors Color_array_t );19.2.1.2 Collection as an attribute of an object typeIn this example, we are modeling automobile specifications, and each Auto_spec_t object will include a list of manufacturer's colors in which you can purchase the vehicle. (See Chapter 18 for more information about Oracle object types.) 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 in a way similar to the examples above; in other words, it wants to know what to name the store table. So the phrase ...NESTED TABLE available_colors STORE AS available_colors_stsays that you want the available_colors column to have a store table named available_colors_st. 19.2.2 Collections in PL/SQLWhether you use a predefined collection type or declare one in your program, using it requires that you declare a variable in a separate step. This declare-type-then-declare-variable motif should be familiar to you if you have ever used an index-by table or a RECORD type in a PL/SQL program. 19.2.2.1 Collection variablesUsing the collection types we've declared above, the following shows some legal declarations of PL/SQL variables: DECLARE -- A variable that will hold a list of available font colors font_colors Color_tab_t; /* The next variable will later hold a temporary copy of || font_colors. Note that we can use %TYPE to refer to the || datatype of font_colors. This illustrates two different || ways of declaring variables of the Color_tab_t type. */ font_colors_save font_colors%TYPE; -- Variable to hold a list of paint colors paint_mixture Color_array_t;But there is no reason you must use only types you have created in the database. You can declare them locally, or mix and match from both sources: DECLARE /* As with Oracle7 index-by tables, you can define || a table datatype here within a declaration section... */ TYPE Number_t IS TABLE OF NUMBER; /* ...and then you can use your new type in the declaration || of a local variable. The next line declares and initializes || in a single statement. Notice the use of the constructor, || Number_t(value, value, ...), to the right of the ":=" */ my_favorite_numbers Number_t := Number_t(42, 65536); /* Or you can just refer to the Color_tab_t datatype in the || data dictionary. This next line declares a local variable || my_favorite_colors to be a "nested" table and initializes it || with two initial elements using the default constructor. */ my_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN'); BEGIN /* Once the local variables exist, usage is independent of whether || they were declared from local types or from types that live in || the data dictionary. */ my_favorite_colors(2) := 'BLUE'; -- changes 2nd element to BLUE my_favorite_numbers(1) := 3.14159; -- changes first element to pi END;This code also illustrates default constructors, which are special functions Oracle provides whenever you create a type, that serve to initialize and/or populate their respective types. A constructor has the same name as the type, and accepts as arguments a comma-separated list of elements. 19.2.2.2 Collections as components of a recordUsing a collection type in a record is very similar to using any other type. You can use VARRAYs, nested tables, or index-by tables (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 );RECORD types cannot live in the database; they are only available within PL/SQL programs. Logically, however, you can achieve a similar result with object types. Briefly, object types can have a variety of attributes, and you can include the two new collection types as attributes within objects; or you can define a collection whose elements are themselves objects. 19.2.2.3 Collections as module parametersCollections can also serve as module parameters. In this case, you cannot return a user-defined type that is declared in the module itself. You will instead use types that you have built outside the scope of the module, either via CREATE TYPE or via public declaration in a package. /* This function provides a pseudo "UNION ALL" operation on || two input parameters of type Color_tab_t. That is, it creates an || OUT parameter which is the superset of the colors of the two || input parameters. */ CREATE PROCEDURE make_colors_superset (first_colors IN Color_tab_t, second_colors IN Color_tab_t, superset OUT Color_tab_t) AS working_colors Color_tab_t := Color_tab_t(); element INTEGER := 1; which INTEGER; BEGIN /* Invoke the EXTEND method to allocate enough storage || to the nested table working_colors. */ working_colors.EXTEND (first_colors.COUNT + second_colors.COUNT); /* Loop through each of the input parameters, reading their || contents, and assigning each element to an element of || working_colors. Input collections may be sparse. */ which := first_colors.FIRST; LOOP EXIT WHEN which IS NULL; working_colors(element) := first_colors(which); element := element + 1; which := first_colors.NEXT(which); END LOOP; which := second_colors.FIRST; LOOP EXIT WHEN which IS NULL; working_colors(element) := second_colors(which); element := element + 1; which := second_colors.NEXT(which); END LOOP; superset := working_colors; END;As a bit of an aside, let's take a look at the loops used in the code. The general form you can use to iterate over the elements of a collection is as follows: 1 which := collection_name.FIRST; 2 LOOP 3 EXIT WHEN which IS NULL; 4 -- do something useful with the current element... 5 which := collection_name.NEXT(which); 6 END LOOP;This works for both dense and sparse collections. The first assignment statement, at line 1, gets the subscript of the FIRST element in the collection; if it's NULL, that means there are no elements, and we would therefore exit immediately at line 3. But if there are elements in the collection, we reach line 4, where the program will do "something useful" with the value, such as assign, change, or test its value for some purpose. The most interesting line of this example is line 5, where we use the NEXT method on the collection to retrieve the next-higher subscript above "which" on the right-hand side. In the event that a particular subscript has been DELETEd, the NEXT operator simply skips over it until it finds a non-deleted element. Also in line 5, if NEXT returns a NULL, that is our cue that we have iterated over all of the collection's elements, and it's time to exit the loop when we get back to line 3. You might also ask why we should use the local variable working_colors in the example above? Why not simply use the superset parameter as the working variable in the program? As it turns out, when we EXTEND a nested table, it must also read the table. So we would have to make superset an IN OUT variable, because OUT variables cannot be read within the program. It's better programming style to avoid using an IN OUT variable when OUT would suffice -- -and more efficient, especially for remote procedure calls. 19.2.2.4 Collections as the datatype of a function's return valueIn the next example, the programmer has defined Color_tab_t as the type of a function return value, and it is also used as the datatype of a local variable. The same restriction about datatype 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;This example also illustrates a long-awaited feature: the retrieval of a complex data item in a single fetch. This is so cool that it bears repeating, so we'll talk more about it later in this chapter. How would you use this function in a PL/SQL program? Since 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 variableAssign a single element of the result to a variable (as long as the variable is of a type compatible with the collection's elements) The first option 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 two, 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;By the way, 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. The main syntactic rule is that function parameters come before subscripts. If your function has no parameters, you'll need to use the empty parameter list notation, ( ), as a placeholder: variable_of_element_type := function () (subscript);19.1 Types of Collections19.3 Syntax for Declaring Collection Datatypes Copyright (c) 2000 O'Reilly & Associates. All rights reserved.