3 Support for Collection Datatypes

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-3 creates a multilevel collection type that is a nested table of nested tables. The example models a system of stars in which each star has a nested table collection of the planets revolving around it, and each planet has a nested table collection of its satellites. In the example, the SQL statements create a table stars that contains a column planets whose type is a multilevel collection. This multilevel collection is a nested table of an object type that has a nested table attribute satellites. Separate nested table clauses are provided for the outer planets nested table and for the inner satellites one.

CREATE TYPE satellite_t AS OBJECT (
  name        VARCHAR2(20),
  diameter    NUMBER);
/
CREATE TYPE nt_sat_t AS TABLE OF satellite_t;
/
CREATE TYPE planet_t AS OBJECT (
  name        VARCHAR2(20),
  mass        NUMBER,
  satellites  nt_sat_t);
/
CREATE TYPE nt_pl_t AS TABLE OF planet_t;
/
CREATE TABLE stars (
  name     VARCHAR2(20),
  age      NUMBER,
  planets  nt_pl_t)
NESTED TABLE planets STORE AS planets_tab 
  (NESTED TABLE satellites STORE AS satellites_tab);

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);

CREATE TABLE stars (
  name     VARCHAR2(20),
  age      NUMBER,
  planets  nt_pl_t)
NESTED TABLE planets STORE AS planets_tab 
  ( PRIMARY KEY (NESTED_TABLE_ID, name) 
    ORGANIZATION INDEX COMPRESS
    NESTED TABLE satellites STORE AS satellites_tab );

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 user-defined 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.

The following example calls the constructor for the multilevel collection type nt_pl_t. This type is a nested table of planets, each of which contains a nested table of satellites as an attribute. The constructor for the outer nested table calls the planet_t constructor for each planet to be created; each planet constructor calls the constructor for the satellites nested table type to create its nested table of satellites; and the satellites nested table type constructor calls the satellite_t constructor for each satellite instance to be created.

INSERT INTO stars 
VALUES('Sun',23,
  nt_pl_t(
    planet_t(
      'Neptune',
      10,
      nt_sat_t(
        satellite_t('Proteus',67),
        satellite_t('Triton',82)
      )
    ),
    planet_t(
      'Jupiter',
      189,
      nt_sat_t(
        satellite_t('Callisto',97),
        satellite_t('Ganymede', 22)
      ) 
    ) 
  ) 
);

Nesting Results of Collection Queries

The following queries use the department_persons table shown in Example 3-1. 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.

For example, the following query retrieves the collection of employees. The collection of employees is nested:

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'))

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.

Like the preceding example, the following query retrieves the collection of employees, but the collection is unnested:

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 the preceding example shows, 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 preceding examples show a TABLE expression that contains the name of a collection. Alternatively, a TABLE expression can contain a subquery of a collection.

The following example returns the collection of employees whose department number is 101.

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.

Here is an example showing a TABLE expression used in the FROM clause of a SELECT embedded 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. The following example shows an unnesting query on a multilevel nested table collection of nested tables. From a table stars in which each star has a nested table of planets and each planet has a nested table of satellites, the query returns the names of all satellites from the inner set of nested tables.

SELECT t.name 
  FROM stars s, TABLE(s.planets) p, TABLE(p.satellites) t;

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

Outer-join syntax can also be used with queries of multilevel collections.

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.

UPDATE stars s
  SET s.planets = :v_planets
  WHERE s.name = 'Aurora Borealis';

INSERT INTO TABLE( SELECT planets FROM stars WHERE name = 'Sun') 
VALUES ('Saturn', 56, 
  nt_sat_t( 
    satellite_t('Rhea', 83)
  )
);

INSERT INTO TABLE( SELECT p.satellites 
  FROM TABLE( SELECT s.planets 
    FROM stars s
    WHERE s.name = 'Sun') p
  WHERE p.name = 'Uranus')
VALUES ('Miranda', 31);

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.

For example:

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

In this example, the nested tables contain person_typ objects which have an associated map method. See Example 1-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.

For example:

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.

For example:

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.

For example:

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

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

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.

For example:

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 varray or nested table. The return type is NUMBER. If the varray or nested table is a null collection, NULL is returned.

For example:

SELECT CARDINALITY(math_majors) 
  FROM students;

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.

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.

For example:

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

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.

For example:

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

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.

For example:

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'))

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.

For example:

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'))));

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.

For example:

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));

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.

For example:

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