Example 5-16 Nesting Results of Collection Queries

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

These queries retrieve this nested collection of employees.

DEPT_EMPS(IDNO, NAME, PHONE)
-------------------------------------------------------------
PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-650-555-0135'),
PERSON_TYP(2, 'Diane Smith', '1-650-555-0135'))

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.

Example 5-17 Unnesting Results of Collection Queries

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

Output:

IDNO       NAME                           PHONE
---------- ------------------------------ ---------------
1          John Smith                     1-650-555-0135
2          Diane Smith                    1-650-555-0135

Example 5-17shows 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.

InExample 5-17, 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.

Example 5-18 Using a Table Expression Containing a Subquery of a Collection

-- 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 Using a Table Expression 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;

Example 5-20 Unnesting Queries with Multilevel Collections Using the TABLE Function

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

Example 5-21 Piecewise Operations on Collections

-- 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-23 Piecewise INSERT on a Multilevel Collection

-- 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-26 Using UPDATE to Insert an Entire Multilevel Collection

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

Example 5-28 Using an Equality Comparison with Nested Tables

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

Example 5-29 Using an IN Comparison with Nested Tables

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

Example 5-30 Testing the SUBMULTISET OF Condition on a Nested Table

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

Example 5-31 Using MEMBER OF on a Nested Table

-- 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 Alternative to Using MEMBER OF on a Nested Table

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

Example 5-33 Using IS NOT on a Nested Table

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

Example 5-34 Using IS A SET on a Nested Table

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