3 Support for Collection Data Types

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.

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.

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.

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 data types 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.

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

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.

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

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

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.

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.

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.

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

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

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

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;

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

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 could be any expression which evaluates to a multiset. The limit on the cardinality of the multiset argument is 32.

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

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

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

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