Slide 10.4: Nested tables (cont.)

Nested Tables (Cont.)

The nested tables are held their own database tables, existing separately from the table in which they are nested. But the data of a child table can only be accessed via the parent table.

Question: Retrieve the nested table of all dependents of employee 101.

The result contains one line for the employee, with dependents listed as one scalar nested table value .

Question: Retrieve the nested tables of all dependents of all employees.

This displays one line for each employee, with the dependents listed as one scalar nested table value.

SQL> SELECT  dependents  FROM  employees  WHERE  eid = 101; 

DEPENDENTS(SSNO, PNAME(LNAME, FNAME, MI), AGE)
----------------------------------------------------------------------
DEPENDENTS_T(
 PERSON_T( 322456776, NAME_T('Smith', 'Michael', 'J'), 8 ),
 PERSON_T( 123822332, NAME_T('Smith', 'Susan',   'R'), 12 ) )

SQL> SELECT  dependents  FROM  employees; 

DEPENDENTS(SSNO, PNAME(LNAME, FNAME, MI), AGE)
---------------------------------------------------------------------
DEPENDENTS_T(
 PERSON_T( 322456776, NAME_T('Smith', 'Michael', 'J'), 8 ),
 PERSON_T( 123822332, NAME_T('Smith', 'Susan',   'R'), 12 ) )

DEPENDENTS_T(
 PERSON_T( 565534555, NAME_T('Shaw', 'David', 'M'), 3 ) )

Question: Retrieve the eids of employees with more than one dependent.

Use the new TABLE( ) form to cast the scalar nested table column e.dependents as a table.

SQL> SELECT  eid  FROM  employees e 
  2    WHERE  2 <= ( SELECT  COUNT( * )  FROM  TABLE( e.dependents ) ); 

       EID
----------
       101