5 Support for Collection Data Types

Storing Elements of Nested Tables

Elements of a nested table are actually stored in a separate storage table.

Oracle stores nested table data in a single storage table associated with the object table for both nested table types that are columns in a relational table or attributes in an object table. The storage table contains a column that identifies the parent table row or object that each element of the nested table belongs to. See Figure 9-2, "Nested Table Storage".

The NESTED TABLE..STORE AS clause specifies storage names for nested tables. Storage names are used to create an index on a nested table.

Example 5-5 demonstrates creating and populating a nested table, and specifying the nested table storage using the person_typ object and the people_typ nested table as defined in Example 5-1.

-- Requires 5-1
CREATE TABLE students (
   graduation DATE, 
   math_majors people_typ, -- nested tables (empty)
   chem_majors people_typ, 
   physics_majors people_typ)
  NESTED TABLE math_majors STORE AS math_majors_nt  -- storage tables
  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', '650-555-0130'), 
                    person_typ(31, 'Sarah Chen', '415-555-0120'),
                    person_typ(45, 'Chris Woods', '415-555-0124')),
      chem_majors = 
        people_typ (person_typ(51, 'Joe Lane', '650-555-0140'), 
                    person_typ(31, 'Sarah Chen', '415-555-0120'),
                    person_typ(52, 'Kim Patel', '650-555-0135')),
   physics_majors = 
        people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), 
                    person_typ(45, 'Chris Woods', '415-555-0124'))
WHERE graduation = '01-JUN-03';

SELECT m.idno math_id, c.idno chem_id, p.idno physics_id  FROM students s,
 TABLE(s.math_majors) m, TABLE(s.chem_majors) c, TABLE(s.physics_majors) p;

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 5-6, the nested table is stored in the system tablespace:

-- Requires Ex. 5-1, must remove code in Ex. 5-2 if created
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 immediately preceding parent table.

You can issue an ALTER TABLE.. MOVE statement to move a table to a different tablespace. If you do this on a table with nested table columns, only the parent table moves; no action is taken on the storage tables of the nested table. To move a storage table for a nested table to a different tablespace, issue ALTER TABLE.. MOVE on the storage table. For example:

ALTER TABLE people_tab MOVE TABLESPACE system;  -- moving table
ALTER TABLE people_column_nt MOVE TABLESPACE example; -- moving storage table

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

Nesting Results of Collection Queries

The following queries use the department_persons table shown in Example 5-3. 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 this way nests the elements of the collection in the result row that the collection is associated with.

Example 5-16 shows the query retrieving the nested collection of employees.

-- Requires Ex. 5-1 and Ex. 5-3
SELECT d.dept_emps 
  FROM department_persons d;

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 produces a nested result.

Unnesting Results of Collection Queries

Not all tools or applications can 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 using a TABLE expression with the collection. TABLE expressions enable 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.

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

The query in Example 5-17, like that of Example 5-16, retrieves the collection of employees, but here the collection is unnested.

-- Requires Ex. 5-1 and 5-3
SELECT e.* 
  FROM department_persons d, TABLE(d.dept_emps) e;

Example 5-17 shows that a TABLE expression can have its own table alias. 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. To reference a table column, a TABLE expression can use the table alias of any table appearing to the left of it in a FROM clause. This 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 with or without 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 5-18 returns the collection of employees whose department number is 101.

-- Requires Ex. 5-1 and 5-3
SELECT *
  FROM TABLE(SELECT d.dept_emps 
               FROM department_persons d
               WHERE d.dept_no = 101);

Subqueries in a TABLE expression have these restrictions:

• 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 5-19 shows a TABLE expression used in the FROM clause of a SELECT embedded in a CURSOR expression.

-- Requires Ex. 5-1 and 5-3
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 also used with multilevel collections, both varrays and nested tables. Example 5-20 shows an unnesting query on a multilevel nested table collection of nested tables. From the table region_tab where 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.

-- Requires Ex. 5-10 and 5-15
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;

The output should be as follows:

REGION_NAME               COUNTRY_NAME                             LOCATION_ID
------------------------- ---------------------------------------- -----------
Europe                    Italy                                           1000
Europe                    Italy                                           1100
Europe                    Switzerland                                     2900
Europe                    Switzerland                                     3000
Europe                    United Kingdom                                  2400
Europe                    United Kingdom                                  2500
Europe                    United Kingdom                                  2600
 
7 rows selected.

LOCATION_ID CITY
----------- ------------------------------
       1000 Roma
       1100 Venice
       2900 Geneva
       3000 Bern
       2400 London
       2500 Oxford
       2600 Stretford
 
7 rows selected.

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".

Piecewise Operations on Nested Tables

For piecewise operations on nested table columns, use the TABLE expression.

The TABLE expression uses a subquery to extract the nested table, so that the INSERT, UPDATE, or DELETE statement applies to the nested table rather than the top-level table.

CAST operators are also helpful. With them, you can do set operations on nested tables using SQL notation, without actually storing the nested tables in the database.

The DML statements in Example 5-21 demonstrate piecewise operations on nested table columns.

-- Requires Ex. 5-1 and 5-3
INSERT INTO TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101)
   VALUES (5, 'Kevin Taylor', '1-408-555-0199');

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-408-555-0199')
   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 5-22 shows VALUE used to return object instance rows for updating:

-- Requires Ex. 5-1, 5-3
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-650-555-0148')
   WHERE p.idno = 2;

Piecewise Operations on Multilevel Nested Tables

Piecewise DML is possible only on multilevel nested tables, not on multilevel varrays. You can perform DML operation atomically on both VARRAYs and nested tables multilevel collections as described in "Collections as Atomic Data Items".

Example 5-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:

-- Requires Ex. 5-10 and 5-15
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 5-24 performs a piecewise insert into an inner nested table to add a location for a country. Like the preceding example, this example uses a TABLE expression containing a subquery that selects the inner nested table to specify the target for the insert.

-- Requires Ex. 5-10 and 5-15
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;

Atomical Changes on VARRAYs and Nested Tables

This section discusses atomical changes to nested tables and VARRAYs.

Note: While nested tables can also be changed in a piecewise fashions, varrays cannot.

Example 5-25 shows how you can manipulate SQL varray object types with PL/SQL statements. In this example, varrays are transferred between PL/SQL variables and SQL tables. You can insert table rows containing collections, update a row to replace its collection, and select collections into PL/SQL variables.

However, you cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray. You can also do this with nested tables, but nested tables have the option of doing piecewise updates and deletes.

CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 
                                       'Executive', 'Marketing');
   some_dnames dnames_var;
BEGIN
   UPDATE depts SET dept_names  = new_dnames WHERE region = 'Europe';
   COMMIT;
   SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe';
   FOR i IN some_dnames.FIRST .. some_dnames.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i));
   END LOOP;
END;
/

Collections as Atomic Data Items

The section "Constructors for Multilevel Collections" shows how to insert an entire multilevel collection with an INSERT statement. Multilevel collections (both VARRAY and nested tables) 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 5-26 updates region_tab by setting the countries collection as a unit to the value of v_country.

-- Requires Ex. 5-10 and 5-15
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;
/
-- Invocation:
SELECT r.region_name, c.country_name, l.location_id 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l
     WHERE r.region_id = 2;

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. Equality is determined in the existing order of the elements, because nested tables are unordered.

-- Requires Ex. 5-1 and 5-5
SELECT p.name 
  FROM students, TABLE(physics_majors) p 
WHERE math_majors = physics_majors;

In Example 5-28, the nested tables contain person_typ objects, which have an associated map method. See Example 5-1. Since the two nested tables in the WHERE clause are not equal, no rows are selected.

IN Comparison

The IN condition checks whether or not 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.

-- Requires Ex. 5-1 and 5-5
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 or not a nested table is a subset of another nested table, returning the result as a Boolean value. The OF keyword is optional and does not change the functionality of SUBMULTISET.

This condition is implemented only for nested tables.

-- Requires Ex. 5-1 and 5-5
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 or not 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.

In Example 5-31, the person_typ is an element of the same type as the elements of the nested table math_majors.

-- Requires Ex. 5-1 and 5-5
SELECT graduation 
  FROM students 
WHERE person_typ(12, 'Bob Jones', '1-650-555-0130') MEMBER OF math_majors;

Example 5-32 presents an alternative approach to the MEMBER OF condition, which performs more efficiently for large collections.

-- Requires Ex. 5-1 and 5-5
SELECT graduation
  FROM students
WHERE person_typ(12, 'Bob Jones', '1-650-555-0130') in (select value(p) 
    from TABLE( math_majors) p);

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.

-- Requires Ex. 5-1 and 5-5
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 or not a given nested table is composed of unique elements, returning a Boolean value.

-- Requires Ex. 5-1 and 5-5
SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IS A SET;

CARDINALITY

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

-- Requires Ex. 5-1 and 5-5
SELECT CARDINALITY(math_majors) 
  FROM students;

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

COLLECT

The COLLECT function is an aggregate function which creates a multiset from a set of elements. The function takes a column of the element type as input and creates 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 Language 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 the second. The input nested tables and the output nested table will all be of the same nested table type.

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 are part of the result. If a particular element occurs m times in ntab1 and n times in ntab2, the result shows (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 and is also present in ntab2 is eliminated, irrespective of the number of occurrences.

-- Requires Ex. 5-1 and 5-5
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 Language Reference.

MULTISET INTERSECT

The MULTISET INTERSECT operator returns a nested table whose values are common to 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 contains the element MIN(m, n) times. With the DISTINCT option, the duplicates from the result are eliminated, including duplicates of NULL values if they exist.

-- Requires Ex. 5-1 and 5-5
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 Language 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 in ntab1 and ntab2 are 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 contains the element (m + n) times. With the DISTINCT option, the duplicates from the result are eliminated, including duplicates of NULL values if they exist.

-- Requires Ex. 5-1 and 5-5
SELECT math_majors MULTISET UNION DISTINCT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';

-- Requires Ex. 5-1 and 5-5
SELECT math_majors MULTISET UNION ALL physics_majors
  FROM students 
WHERE graduation = '01-JUN-03';

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

POWERMULTISET

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

-- Requires Ex. 5-1 and 5-5
SELECT * FROM TABLE(POWERMULTISET( people_typ (
           person_typ(12, 'Bob Jones', '1-650-555-0130'), 
           person_typ(31, 'Sarah Chen', '1-415-555-0120'), 
           person_typ(45, 'Chris Woods', '1-415-555-0124'))));

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

POWERMULTISET_BY_CARDINALITY

The POWERMULTISET_BY_CARDINALITY function returns all non-empty submultisets of a nested table of the specified cardinality. The output is 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 can be any expression which evaluates to a nested table. The length parameter must be a positive integer, otherwise an error is returned. The limit on the cardinality of the nested table argument is 32.

-- Requires Ex. 5-1 and 5-5
SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ (
           person_typ(12, 'Bob Jones', '1-650-555-0130'), 
           person_typ(31, 'Sarah Chen', '1-415-555-0120'), 
           person_typ(45, 'Chris Woods', '1-415-555-0124')),2));

For more information about the POWERMULTISET_BY_CARDINALITY function, see Oracle Database SQL Language 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.

-- Requires Ex. 5-1 and 5-5
SELECT SET(physics_majors) 
  FROM students 
WHERE graduation = '01-JUN-03';

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