Slide 10.4: Nested tables (cont.)

Slide 10.3: Nested tables (cont.)
Slide 10.5: Nested tables (cont.)
Home Print version

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

◀
Previous

Slide 10.3: Nested tables (cont.)
Slide 10.5: Nested tables (cont.)
Home Print version

▶
Next

“Failure is the condiment that gives success its flavor.”
― Truman Capote