Skip Headers
Oracle® Database Application Developer's Guide - Object-Relational Features
10g Release 2 (10.2)

Part Number B14260-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

3 Support for Collection Datatypes

This chapter provides basic information about working with varrays and nested table collection datatypes. It explains how to create and manage collection datatypes.

This chapter contains these topics:

Creating Collection Datatypes

Oracle supports the varray and nested table collection datatypes.

If you need to store only a fixed number of items, or to loop through the elements in order, or you will often want to retrieve and manipulate the entire collection as a value, then use a varray.

If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or perform mass insert, update, or delete operations, then use a nested table. See "Design Considerations for Collections".

Creating an Instance of a VARRAY or Nested Table

You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the type's constructor method. The name of a type's constructor method is simply the name of the type. You specify the elements of the collection as a comma-delimited list of arguments to the method.

Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection. See "Design Considerations for Nested Tables" for more information on using nested tables.

Constructor Methods for Collections

Example 3-1 shows how a literal invocation of the constructor method is used in a SQL statement to insert values into a nested table type people_typ.

Example 3-1 Using the Constructor Method to Insert Values into a Nested Table

CREATE TYPE people_typ AS TABLE OF person_typ;
/
CREATE TABLE people_tab (
    group_no NUMBER,
    people_column people_typ )
    NESTED TABLE people_column STORE AS people_column_nt;

INSERT INTO people_tab VALUES (
            100,
            people_typ( person_typ(1, 'John Smith', '1-800-555-1212'),
                        person_typ(2, 'Diane Smith', NULL)));

When you declare a table column to be of an object type or collection type, you can include a DEFAULT clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The DEFAULT clause must contain a literal invocation of the constructor method for that object or collection.

Example 3-2 shows how to use literal invocations of constructor methods to specify defaults for person_typ and people_typ:

Example 3-2 Creating the department_persons Table Using the DEFAULT Clause

CREATE TABLE department_persons (
  dept_no    NUMBER PRIMARY KEY,
  dept_name  CHAR(20),
  dept_mgr   person_typ DEFAULT person_typ(10,'John Doe',NULL),
  dept_emps  people_typ DEFAULT people_typ() )
  NESTED TABLE dept_emps STORE AS dept_emps_tab;

INSERT INTO department_persons VALUES 
   ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-800-555-4412'),
           people_typ( person_typ(1, 'John Smith', '1-800-555-1212'), 
                        person_typ(2, 'Diane Smith', NULL) ) );
INSERT INTO department_persons VALUES 
  ( 104, 'Life Sciences', person_typ(70,'James Hall', '1-800-555-4621'), 
    people_typ() );

Note that the term people_typ() is a literal invocation of the constructor method for an empty people_typ table.

Varrays

A varray is an ordered set of data elements. All elements of a given varray are of the same datatype or a subtype of the declared one. Each element has an index, which is a number corresponding to the element's position in the array. The index number is used to access a specific element.

When you define a varray, you specify the maximum number of elements it can contain, although you can change this number later. The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called varrays.

The following statement creates an array type email_list_arr that has no more than ten elements, each of datatype VARCHAR2(80).

CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/

In Example 3-3, a VARRAY type is created that is an array of an object type. The phone_varray_typ VARRAY type is used as a datatype for a column in the dept_phone_list table. The INSERT statements show how to insert values into phone_varray_typ in the table.

Example 3-3 Creating and Populating a VARRAY Datatype

CREATE TYPE phone_typ AS OBJECT (
    country_code   VARCHAR2(2), 
    area_code      VARCHAR2(3),
    ph_number      VARCHAR2(7));
/
CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
/
CREATE TABLE dept_phone_list (
  dept_no NUMBER(5), 
  phone_list phone_varray_typ);

INSERT INTO dept_phone_list VALUES (
   100,
   phone_varray_typ( phone_typ ('01', '650', '5061111'),
                      phone_typ ('01', '650', '5062222'),
                      phone_typ ('01', '650', '5062525')));

Creating an array type, as with a SQL object type, does not allocate space. It defines a datatype, which you can use as:

  • The datatype of a column of a relational table.

  • An object type attribute.

  • The type of a PL/SQL variable, parameter, or function return value.

A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB. See "Storage Considerations for Varrays".

You can create a VARRAY type of XMLType or of a LOB type for procedural purposes, such as in PL/SQL or in view queries. However, database storage of a varray of those types is not supported. This means that you cannot create an object table or an object type column of a varray type of XMLType or of a LOB type.


See Also:

Oracle Database SQL Reference for information and examples on the STORE AS LOB clause of the CREATE TABLE statement

Nested Tables

A nested table is an unordered set of data elements, all of the same datatype. No maximum is specified in the definition of the table and the order of the elements is not preserved. You select, insert, delete, and update in a nested table just as you do with ordinary tables using the TABLE expression.

Elements of a nested table are actually stored in a separate storage table that contains a column that identifies the parent table row or object to which each element belongs. A nested table has a single column, and the type of that column is a built-in type or an object type. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.

In Example 3-4, the table type used for the nested tables is declared with the CREATE TYPE ... IS TABLE OF statement. A table type definition does not allocate space. It defines a type, which you can use as:

  • The datatype of a column of a relational table

  • An object type attribute

  • A PL/SQL variable, parameter, or function return type

When a column in a relational table is of nested table type, Oracle stores the nested table data for all rows of the relational table in the same storage table. Similarly, with an object table of a type that has a nested table attribute, Oracle stores nested table data for all object instances in a single storage table associated with the object table. See Figure 8-2, "Nested Table Storage".

In Example 3-4, the NESTED TABLE clause specifies the storage name for the nested table. The storage name is used when creating an index on a nested table. The example uses person_typ defined in Example 2-1 and people_typ defined in Example 3-1.

Example 3-4 Creating and Populating Simple Nested Tables

CREATE TABLE students (
   graduation DATE, 
   math_majors people_typ, 
   chem_majors people_typ, 
   physics_majors people_typ)
  NESTED TABLE math_majors STORE AS math_majors_nt
  NESTED TABLE chem_majors STORE AS chem_majors_nt
  NESTED TABLE physics_majors STORE AS physics_majors_nt;

CREATE INDEX math_idno_idx ON math_majors_nt(idno);
CREATE INDEX chem_idno_idx ON chem_majors_nt(idno);
CREATE INDEX physics_idno_idx ON physics_majors_nt(idno);

INSERT INTO students (graduation) VALUES ('01-JUN-03');
UPDATE students
  SET math_majors = 
        people_typ (person_typ(12, 'Bob Jones', '111-555-1212'), 
                    person_typ(31, 'Sarah Chen', '111-555-2212'),
                    person_typ(45, 'Chris Woods', '111-555-1213')),
      chem_majors = 
        people_typ (person_typ(51, 'Joe Lane', '111-555-1312'), 
                    person_typ(31, 'Sarah Chen', '111-555-2212'),
                    person_typ(52, 'Kim Patel', '111-555-1232')),
   physics_majors = 
        people_typ (person_typ(12, 'Bob Jones', '111-555-1212'), 
                    person_typ(45, 'Chris Woods', '111-555-1213'))
WHERE graduation = '01-JUN-03';

A convenient way to access the elements of a nested table individually is to use a nested cursor or the TABLE function. See "Querying Collections".

Specifying a Tablespace When Storing a Nested Table

A nested table can be stored in a different tablespace than its parent table. In Example 3-5, the nested table is stored in the system tablespace:

Example 3-5 Specifying a Different Tablespace for Storing a Nested Table

CREATE TABLE people_tab (
    people_column people_typ )
    NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE system);

If the TABLESPACE clause is not specified, then the storage table of the nested table is created in the tablespace where the parent table is created. For multilevel nested tables, Oracle creates the child table in the same tablespace as its immediate preceding parent table.

The user can issue ALTER TABLE MOVE statement to move a table to a different tablespace. If the user issues ALTER TABLE MOVE statement on a table with nested table columns, it only moves parent table, no action is taken on the nested table's storage tables. If the user wants to move a nested table s storage table to a different tablespace, issue ALTER TABLE MOVE on the storage table. For example:

ALTER TABLE people_tab MOVE TABLESPACE system;
ALTER TABLE people_column_nt MOVE TABLESPACE example;

Now the people_tab table is in system tablespace and nested table is stored in the example tablespace.

Varray Storage

Multilevel varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.

  • In a varray of varrays, the entire varray is stored inline in the row unless it is larger than approximately 4000 bytes or LOB storage is explicitly specified.

  • In a varray of nested tables, the entire varray is stored in a LOB, with only the LOB locator stored in the row. There is no storage table associated with nested table elements of a varray. The entire nested table collection is stored inside the varray.

You can explicitly specify LOB storage for varrays. The following example does this for the varray elements of a nested table. As Example 3-6 also shows, you can use the COLUMN_VALUE keyword with varrays as well as nested tables.

Example 3-6 Specifying LOB Storage for a VARRAY Elements of a Nested Table

CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/

CREATE TYPE email_list_typ AS TABLE OF email_list_arr;
/

CREATE TABLE dept_email_list (
  dept_no NUMBER, 
  email_addrs email_list_typ)
  NESTED TABLE email_addrs STORE AS email_addrs_nt
  (VARRAY COLUMN_VALUE STORE AS LOB dept_emails_lob);

Example 3-7 shows explicit LOB storage specified for a varray of varray type.

Example 3-7 Specifying LOB Storage for a VARRAY Type

CREATE TYPE email_list_typ2 AS OBJECT (
    section_no   NUMBER, 
    emails       email_list_arr);
/

CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ2;
/

CREATE TABLE dept_email_list2 (
  dept_no NUMBER, 
  email_addrs email_varray_typ)
  VARRAY email_addrs STORE AS LOB dept_emails_lob2;

See "Storage Considerations for Varrays". See also Oracle Database Application Developer's Guide - Large Objects.

Increasing the Size and Precision of VARRAYs and Nested Tables

When the element type of a VARRAY type is a variable character or RAW type or a numeric type, you can increase the size of the variable character or RAW type or increase the precision of the numeric type. A new type version is generated for the VARRAY type. The same changes can be applied to nested table types.

Options like INVALIDATE and CASCADE are provided to either invalidate all dependent objects or propagate the change to its type and table dependents.

Example 3-8 is illustrates the use ALTER TYPE to increase the size of a VARRAY and a nested table element type.

Example 3-8 Increasing the Size of an Element Type in a VARRAY and Nested Table

CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
ALTER TYPE email_list_arr MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;

CREATE TYPE email_list_tab AS TABLE OF VARCHAR2(30);
/
ALTER TYPE email_list_tab MODIFY ELEMENT TYPE VARCHAR2(40) CASCADE;

Increasing VARRAY Limit Size

The ALTER TYPE ... MODIFY LIMIT syntax allows increasing the number of elements of a VARRAY type. If the number of elements of the VARRAY type is increased, a new type version is generated for the VARRAY type and this is maintained as part of the history of the type changes.

Options like INVALIDATE and CASCADE are provided to either invalidate all dependent objects or propagate the change to its type and table dependents.

Example 3-9 Increasing the VARRAY Limit Size

CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
CREATE TYPE email_list_typ AS OBJECT (
    section_no   NUMBER, 
    emails       email_list_arr);
/

CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;
/

ALTER TYPE email_varray_typ MODIFY LIMIT 100 INVALIDATE;

When a VARRAY type is altered, changes are propagated to the dependent tables. See "Propagating VARRAY Size Change".

Creating a Varray Containing LOB References

In Example 3-10, email_addrs of type email_list_typ already exists in table dept_email_list as shown in the SQL examples in "Varray Storage".

To create a varray of LOB references, first define a VARRAY type of type REF email_list_typ2. Next define a column of the array type in dept_email_list3.

Example 3-10 Creating a VARRY Containing LOB References

CREATE TYPE ref_email_varray_typ AS VARRAY(5) OF REF email_list_typ;
/

CREATE TABLE dept_email_list3 (
  dept_no NUMBER, 
  email_addrs ref_email_varray_typ)
  VARRAY email_addrs STORE AS LOB dept_emails_lob3;

Multilevel Collection Types

Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are:

  • Nested table of nested table type

  • Nested table of varray type

  • Varray of nested table type

  • Varray of varray type

  • Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type

Like ordinary, single-level collection types, multilevel collection types can be used as columns in a relational table or with object attributes in an object table.

Nested Table Storage Tables for Multilevel Collection Types

A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. With a multilevel nested table collection of nested tables, the inner set of nested tables also requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.

Example 3-11 creates a multilevel collection type that is a nested table of nested tables. The example models a system of corporate regions in which each region has a nested table collection of the countries, and each country has a nested table collection of its locations. This example is based on the regions, countries, and locations tables of the Oracle HR sample schema.

In Example 3-11, the SQL statements create a table region_tab that contains a column countries whose type is a multilevel collection. This multilevel collection is a nested table of an object type that has a nested table attribute locations. Separate nested table clauses are provided for the outer countries nested table and for the inner locations one.

Example 3-11 Multilevel Nested Table Storage

CREATE TYPE location_typ AS OBJECT (
  location_id      NUMBER(4),
  street_address   VARCHAR2(40),
  postal_code      VARCHAR2(12),
  city             VARCHAR2(30),
  state_province   VARCHAR2(25));
/

CREATE TYPE nt_location_typ AS TABLE OF location_typ;
/

CREATE TYPE country_typ AS OBJECT (
  country_id     CHAR(2),
  country_name   VARCHAR2(40),
  locations      nt_location_typ);
/

CREATE TYPE nt_country_typ AS TABLE OF country_typ;
/

CREATE TABLE region_tab (
  region_id     NUMBER,
  region_name   VARCHAR2(25),
  countries     nt_country_typ)
  NESTED TABLE countries STORE AS nt_countries_tab
   (NESTED TABLE locations STORE AS nt_locations_tab);

In Example 3-11 you can refer to the inner locations nested table by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute, it has no name. The keyword COLUMN_VALUE is provided for this case; you use it in place of a name for an inner nested table as shown in Example 3-12.

Example 3-12 Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword

CREATE TYPE inner_table AS TABLE OF NUMBER;
/
CREATE TYPE outer_table AS TABLE OF inner_table;
/
CREATE TABLE tab1 (
  col1 NUMBER,
  col2 outer_table)
NESTED TABLE col2 STORE AS col2_ntab
  (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);

Physical attributes for the storage tables can be specified in the nested table clause, as shown in Example 3-13.

Example 3-13 Specifying Physical Attributes for Nested Table Storage

CREATE TABLE region_tab (
  region_id     NUMBER,
  region_name   VARCHAR2(25),
  countries     nt_country_typ)
  NESTED TABLE countries STORE AS nt_countries_tab (
   (PRIMARY KEY (NESTED_TABLE_ID, country_id))
   ORGANIZATION INDEX COMPRESS
   NESTED TABLE locations STORE AS nt_locations_tab);

Every nested table storage table contains a column, referenceable by NESTED_TABLE_ID, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns: one, referenceable by NESTED_TABLE_ID, that keys its rows back to rows in its own parent table, and one hidden column referenced by the NESTED_TABLE_ID column in its nested table children.

In Example 3-13, nested table countries is made an index-organized table (IOT) by adding the ORGANIZATION INDEX clause and assigning the nested table a primary key in which the first column is NESTED_TABLE_ID. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with NESTED_TABLE_ID as the first column and index-organizing the table cause Oracle to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.

Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection. See "Nested Table Storage".

Assignment and Comparison of Multilevel Collections

As with single-level collections, both the source and the target must be of the same declared data type in assignments of multilevel collections.

Only items whose datatypes are nested table collection types, including multilevel collection types, can be compared. See "Comparisons of Collections".

Constructors for Multilevel Collections

Like single-level collection types, multilevel collection types are created by calling the respective type's constructor method. Like the constructor methods for other object types, a constructor for a multilevel collection type is a system-defined function that has the same name as the type and returns a new instance of it—in this case, a new multilevel collection. Constructor parameters have the names and types of the object type's attributes.

Example 3-14 shows the constructor call for the multilevel collection type nt_country_typ. This type is a nested table of countries, each of which contains a nested table of locations as an attribute. The constructor for the outer nested table calls the country_typ constructor for each country to be created; each country constructor calls the constructor for the locations nested table type to create its nested table of locations; and the locations nested table type constructor calls the location_typ constructor for each location instance to be created.

Example 3-14 Using Constructors for Multilevel Collections

INSERT INTO region_tab 
VALUES(1, 'Europe', nt_country_typ( 
  country_typ( 'IT', 'Italy', nt_location_typ (
    location_typ(1000, '1297 Via Cola di Rie','00989','Roma', ''),
    location_typ(1100, '93091 Calle della Testa','10934','Venice','') ) 
    ),
  country_typ( 'CH', 'Switzerland', nt_location_typ (
    location_typ(2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve'),
    location_typ(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE') ) 
    ),
  country_typ( 'UK', 'United Kingdom', nt_location_typ (
    location_typ(2400, '8204 Arthur St', '', 'London', 'London'),
    location_typ(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB',
                 'Oxford', 'Oxford'),
    location_typ(2600, '9702 Chester Road', '09629850293', 'Stretford',
                 'Manchester') )
      ) 
  )
);

Operations on Collection Datatypes

This section describes the operations on collection datatypes.

Querying Collections

There are two general ways to query a table that contains a column or attribute of a collection type. One way returns the collections nested in the result rows that contain them. The other way distributes or unnests collections such that each collection element appears on a row by itself.

Nesting Results of Collection Queries

The following queries use the department_persons table shown in Example 3-2. The column dept_emps is a nested table collection of person_typ type. The dept_emps collection column appears in the SELECT list like an ordinary, scalar column. Querying a collection column in the SELECT list like this nests the elements of the collection in the result row with which the collection is associated.

In Example 3-15 the query retrieves the nested collection of employees.

Example 3-15 Nesting Results of Collection Queries

SELECT d.dept_emps 
  FROM department_persons d;


DEPT_EMPS(IDNO, NAME, PHONE)
-------------------------------------------------------------
PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-800-555-1212'),
PERSON_TYP(2, 'Diane Smith', '1-800-555-1243'))

The results are also nested if an object-type column in the SELECT list contains a collection attribute, even if that collection is not explicitly listed in the SELECT list itself. For example, the query SELECT * FROM department_persons would produce a nested result.

Unnesting Results of Collection Queries

Not all tools or applications are able to deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this by using a TABLE expression with the collection. A TABLE expression enables you to query a collection in the FROM clause like a table. In effect, you join the nested table with the row that contains the nested table.

The TABLE expression can be used to query any collection value expression, including transient values such as variables and parameters.

As in Example 3-15, the query in Example 3-16 retrieves the collection of employees, but the collection is unnested.

Example 3-16 Unnesting Results of Collection Queries

SELECT e.* 
  FROM department_persons d, TABLE(d.dept_emps) e;


IDNO NAME PHONE
---------- ------------------------------ ---------------
1 John Smith 1-800-555-1212
2 Diane Smith 1-800-555-1243

As shown in Example 3-16, a TABLE expression can have its own table alias. In the example, a table alias for the TABLE expression appears in the SELECT list to select columns returned by the TABLE expression.

The TABLE expression uses another table alias to specify the table that contains the collection column that the TABLE expression references. The expression TABLE(d.dept_emps) specifies the department_persons table as containing the dept_emps collection column. A TABLE expression can use the table alias of any table appearing to the left of it in a FROM clause to reference a column of that table. This way of referencing collection columns is called left correlation.

In the example, the department_persons table is listed in the FROM clause solely to provide a table alias for the TABLE expression to use. No columns from the department_persons table other than the column referenced by the TABLE expression appear in the result

The following example produces rows only for departments that have employees.

SELECT d.dept_no, e.* 
  FROM department_persons d, TABLE(d.dept_emps) e;

To get rows for departments that have no employees, you can use outer-join syntax:

SELECT d.dept_no, e.* 
  FROM department_persons d, TABLE(d.dept_emps) (+) e;

The (+) indicates that the dependent join between department_persons and e.dept_emps should be NULL-augmented. That is, there will be rows of department_persons in the output for which e.dept_emps is NULL or empty, with NULL values for columns corresponding to e.dept_emps.

Unnesting Queries Containing Table Expression Subqueries

The examples in "Unnesting Results of Collection Queries" show a TABLE expression that contains the name of a collection. Alternatively, a TABLE expression can contain a subquery of a collection.

Example 3-17 returns the collection of employees whose department number is 101.

Example 3-17 Using a Table Expression Containing a Subquery of a Collection

SELECT *
  FROM TABLE(SELECT d.dept_emps 
               FROM department_persons d
               WHERE d.dept_no = 101);

There are these restrictions on using a subquery in a TABLE expression:

  • The subquery must return a collection type

  • The SELECT list of the subquery must contain exactly one item

  • The subquery must return only a single collection; it cannot return collections for multiple rows. For example, the subquery SELECT dept_emps FROM department_persons succeeds in a TABLE expression only if table department_persons contains just a single row. If the table contains more than one row, the subquery produces an error.

Example 3-18 shows a TABLE expression used in the FROM clause of a SELECT embedded in a CURSOR expression.

Example 3-18 Using a Table Expression in a CURSOR Expression

SELECT d.dept_no, CURSOR(SELECT * FROM TABLE(d.dept_emps)) 
  FROM department_persons d
   WHERE d.dept_no = 101;

Unnesting Queries with Multilevel Collections

Unnesting queries can be used with multilevel collections, too, for both varrays and nested tables. Example 3-19 shows an unnesting query on a multilevel nested table collection of nested tables. From a table region_tab in which each region has a nested table of countries and each country has a nested table of locations, the query returns the names of all regions, countries, and locations.

Example 3-19 Unnesting Queries with Multilevel Collections Using the TABLE Function

SELECT r.region_name, c.country_name, l.location_id 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;
 
-- the following query is optimized to run against the locations table
SELECT l.location_id, l.city 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

Because no columns of the base table region_tab appear in the second SELECT list, the query is optimized to run directly against the locations storage table.

Outer-join syntax can also be used with queries of multilevel collections. See "Viewing Object Data in Relational Form with Unnesting Queries".

Performing DML Operations on Collections

Oracle supports the following DML operations on nested table columns:

  • Inserts and updates that provide a new value for the entire collection

  • Piecewise Updates

    • Inserting new elements into the collection

    • Deleting elements from the collection

    • Updating elements of the collection.

Oracle does not support piecewise updates on VARRAY columns. However, VARRAY columns can be inserted into or updated as an atomic unit.

For piecewise updates of nested table columns, the DML statement identifies the nested table value to be operated on by using the TABLE expression.

The DML statements in Example 3-20 demonstrate piecewise operations on nested table columns.

Example 3-20 Piecewise Operations on Collections

INSERT INTO TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101)
   VALUES (5, 'Kevin Taylor', '1-800-555-6212');

UPDATE TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101) e   
   SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-800-555-6233')
   WHERE e.idno = 5;
     
DELETE FROM TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101) e
   WHERE e.idno = 5;

Example 3-21 shows VALUE used to return object instance rows for updating:

Example 3-21 Using VALUE to Update a Nested Table

UPDATE TABLE(SELECT d.dept_emps FROM department_persons d 
               WHERE  d.dept_no = 101) p
   SET VALUE(p) = person_typ(2, 'Diane Smith', '1-800-555-1243')
   WHERE p.idno = 2;

Performing DML on Multilevel Collections

For multilevel nested table collections, DML can be done atomically, on the collection as a whole, or piecewise, on selected elements. For multilevel varray collections, DML operations can be done only atomically.

Collections as Atomic Data Items

The section "Constructors for Multilevel Collections" shows an example of inserting an entire multilevel collection with an INSERT statement. Multilevel collections can also be updated atomically with an UPDATE statement. For example, suppose v_country is a variable declared to be of the countries nested table type nt_country_typ. Example 3-22 updates region_tab by setting the countries collection as a unit to the value of v_country.

Example 3-22 Using UPDATE to Insert an Entire Multilevel Collection

INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');

DECLARE
  v_country nt_country_typ;
BEGIN
  v_country :=  nt_country_typ( country_typ( 
   'US', 'United States of America', nt_location_typ (
   location_typ( 1500,'2011 Interiors Blvd','99236','San Francisco','California'),
   location_typ(1600,'2007 Zagora St','50090','South Brunswick','New Jersey'))));
  UPDATE region_tab r 
    SET r.countries = v_country WHERE r.region_id = 2;
END;
/

Piecewise Operations on Nested Tables

Piecewise DML is possible only on nested tables, not on varrays.

Example 3-23 shows a piecewise insert operation on the countries nested table of nested tables. The example inserts a new country, complete with its own nested table of location_typ:

Example 3-23 Piecewise INSERT on a Multilevel Collection

INSERT INTO TABLE( SELECT countries FROM region_tab r WHERE r.region_id = 2) 
  VALUES ( 'CA', 'Canada', nt_location_typ( 
       location_typ(1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario')));

Example 3-24 performs a piecewise insert into an inner nested table to add a location for a country. Like the preceding, this example uses a TABLE expression containing a subquery that selects the inner nested table to specify the target for the insert.

Example 3-24 Piecewise INSERT into an Inner Nested Table

INSERT INTO TABLE( SELECT c.locations 
  FROM TABLE( SELECT r.countries FROM region_tab r WHERE r.region_id = 2) c
  WHERE c.country_id = 'US')
  VALUES (1700, '2004 Lakeview Rd', '98199', 'Seattle', 'Washington');
 
SELECT r.region_name, c.country_name, l.location_id 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

Comparisons of Collections

The conditions listed in this section allow comparisons of nested tables. There is no mechanism for comparing varrays. The SQL examples in this section use the nested tables created in Example 3-4.

Equal and Not Equal Comparisons

The equal (=) and not equal (<>) conditions determine whether the input nested tables are identical or not, returning the result as a Boolean value.

Two nested tables are equal if they have the same named type, have the same cardinality, and their elements are equal. Elements are equal depending on whether they are equal by the elements own equality definitions, except for object types which require a map method.

Example 3-25 Using an Equality Comparison with Nested Tables

SELECT p.name 
  FROM students, TABLE(physics_majors) p 
WHERE math_majors = physics_majors;

In Example 3-25, the nested tables contain person_typ objects which have an associated map method. See Example 2-1.

In Comparisons

The IN condition checks whether a nested table is in a list of nested tables, returning the result as a Boolean value. NULL is returned if the nested table is a null nested table.

Example 3-26 Using an IN Comparison with Nested Tables

SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IN (math_majors, chem_majors);

Subset of Multiset Comparison

The SUBMULTISET [OF] condition checks whether a nested table is a subset of a another nested table, returning the result as a Boolean value. The OF keyword is optional and does not change the functionality of SUBMULTISET.

This operator is implemented only for nested tables because this is a multiset function only.

Example 3-27 Testing the SUBMULTISET OF Condition on a Nested Table

SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors SUBMULTISET OF math_majors;

Member of a Nested Table Comparison

The MEMBER [OF] or NOT MEMBER [OF] condition tests whether an element is a member of a nested table, returning the result as a Boolean value. The OF keyword is optional and has no effect on the output.

Example 3-28 Using MEMBER OF on a Nested Table

SELECT graduation 
  FROM students 
WHERE person_typ(12, 'Bob Jones', '1-800-555-1212') MEMBER OF math_majors;

In Example 3-28, person_typ (12, 'Bob Jones', '1-800-555-1212') is an element of the same type as the elements of the nested table math_majors.

Empty Comparison

The IS [NOT] EMPTY condition checks whether a given nested table is empty or not empty, regardless of whether any of the elements are NULL. If a NULL is given for the nested table, the result is NULL. The result is returned as a Boolean value.

Example 3-29 Using IS NOT on a Nested Table

SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IS NOT EMPTY;

Set Comparison

The IS [NOT] A SET condition checks whether a given nested table is composed of unique elements, returning a Boolean value.

Example 3-30 Using IS A SET on a Nested Table

SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IS A SET;

Multisets Operations

This section describes multiset operations with nested tables. For a description of additional operations, see "Comparisons of Objects, REF Variables, and Collections". The SQL examples in this section use the nested tables created in Example 3-4.

For more information about using operators with nested tables, see Oracle Database SQL Reference.

CARDINALITY

The CARDINALITY function returns the number of elements in a varray or nested table. The return type is NUMBER. If the varray or nested table is a null collection, NULL is returned.

Example 3-31 Determining the CARDINALITY of a Nested Table

SELECT CARDINALITY(math_majors) 
  FROM students;

For more information about the CARDINALITY function, see Oracle Database SQL Reference.

COLLECT

The COLLECT function is an aggregate function which would create a multiset from a set of elements. The function would take a column of the element type as input and create a multiset from rows selected. To get the results of this function you must use it within a CAST function to specify the output type of COLLECT. See "CAST" for an example of the COLLECT function.

For more information about the COLLECT function, see Oracle Database SQL Reference.

MULTISET EXCEPT

The MULTISET EXCEPT operator inputs two nested tables and returns a nested table whose elements are in the first nested table but not in the second nested table. The input nested tables and the output nested table are all type name equivalent.

The ALL or DISTINCT options can be used with the operator. The default is ALL.

  • With the ALL option, for ntab1 MULTISET EXCEPT ALL ntab2, all elements in ntab1 other than those in ntab2 would be part of the result. If a particular element occurs m times in ntab1 and n times in ntab2, the result will have (m - n) occurrences of the element if m is greater than n otherwise 0 occurrences of the element.

  • With the DISTINCT option, any element that is present in ntab1 which is also present in ntab2 would be eliminated, irrespective of the number of occurrences.

Example 3-32 Using the MULTISET EXCEPT Operation on Nested Tables

SELECT math_majors MULTISET EXCEPT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';

For more information about the MULTISET EXCEPT operator, see Oracle Database SQL Reference.

MULTISET INTERSECTION

The MULTISET INTERSECT operator returns a nested table whose values are common in the two input nested tables. The input nested tables and the output nested table are all type name equivalent.

There are two options associated with the operator: ALL or DISTINCT. The default is ALL. With the ALL option, if a particular value occurs m times in ntab1 and n times in ntab2, the result would contain the element MIN(m, n) times. With the DISTINCT option the duplicates from the result would be eliminated, including duplicates of NULL values if they exist.

Example 3-33 Using the MULTISET INTERSECT Operation on Nested Tables

SELECT math_majors MULTISET INTERSECT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';

For more information about the MULTISET INTERSECT operator, see Oracle Database SQL Reference.

MULTISET UNION

The MULTISET UNION operator returns a nested table whose values are those of the two input nested tables. The input nested tables and the output nested table are all type name equivalent.

There are two options associated with the operator: ALL or DISTINCT. The default is ALL. With the ALL option, all elements that are in ntab1 and ntab2 would be part of the result, including all copies of NULLs. If a particular element occurs m times in ntab1 and n times in ntab2, the result would contain the element (m + n) times. With the DISTINCT option the duplicates from the result are eliminated, including duplicates of NULL values if they exist.

Example 3-34 Using the MULTISET UNION Operation on Nested Tables

SELECT math_majors MULTISET UNION DISTINCT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';


PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'),
       PERSON_TYP(31, 'Sarah Chen', '1-800-555-2212'),
       PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'))
SELECT math_majors MULTISET UNION ALL physics_majors
  FROM students 
WHERE graduation = '01-JUN-03';


PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'),
    PERSON_TYP(31, 'Sarah Chen', '1-800-555-2212'),
    PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'),
    PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'),
    PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'))

For more information about the MULTISET UNION operator, see Oracle Database SQL Reference.

POWERMULTISET

The POWERMULTISET function generates all non-empty submultisets from a given multiset. The input to the POWERMULTISET function could be any expression which evaluates to a multiset. The limit on the cardinality of the multiset argument is 32.

Example 3-35 Using the POWERMULTISET Operation on Multiset

SELECT * FROM TABLE(POWERMULTISET( people_typ (
           person_typ(12, 'Bob Jones', '1-800-555-1212'), 
           person_typ(31, 'Sarah Chen', '1-800-555-2212'), 
           person_typ(45, 'Chris Woods', '1-800-555-1213'))));

For more information about the POWERMULTISET function, see Oracle Database SQL Reference.

POWERMULTISET_BY_CARDINALITY

The POWERMULTISET_BY_CARDINALITY function returns all non-empty submultisets of a nested table of the specified cardinality. The output would be rows of nested tables.

POWERMULTISET_BY_CARDINALITY(x, l) is equivalent to TABLE(POWERMULTISET(x)) p where CARDINALITY(value(p)) = l, where x is a multiset and l is the specified cardinality.

The first input parameter to the POWERMULTISET_BY_CARDINALITY could be any expression which evaluates to a nested table. The length parameter should be a positive integer, otherwise an error will be returned. The limit on the cardinality of the nested table argument is 32.

Example 3-36 Using the POWERMULTISET_BY_CARDINALITY Function

SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ (
           person_typ(12, 'Bob Jones', '1-800-555-1212'), 
           person_typ(31, 'Sarah Chen', '1-800-555-2212'), 
           person_typ(45, 'Chris Woods', '1-800-555-1213')),2));

For more information about the POWERMULTISET_BY_CARDINALITY function, see Oracle Database SQL Reference.

SET

The SET function converts a nested table into a set by eliminating duplicates, and returns a nested table whose elements are DISTINCT from one another. The nested table returned is of the same named type as the input nested table.

Example 3-37 Using the SET Function on a Nested Table

SELECT SET(physics_majors) 
  FROM students 
WHERE graduation = '01-JUN-03';

For more information about the SET function, see Oracle Database SQL Reference.