Nested Tables (Cont.)

Nested Cursors
The top-level SELECT provides a cursor-like loop over its FROM tables to generate the rows to retrieve. The nested cursor, CURSOR( ), then allows us to add a second loop to scan the nested tables within each row as they are encountered in the outer SELECT loop.

Question: Display all eid values of rows in the employees table, and for each eid the social security numbers of any dependents of that employee under 16 years of age.
  1. The first query uses the first method: unnesting via table products .
  2. The second query uses the second method: nested cursors . A CURSOR expression returns a nested cursor. 
SQL> SELECT  e.eid, d.ssno AS dep_ssno 
  2    FROM  employees e,  TABLE( e.dependents ) d  WHERE  d.age < 16; 

       EID   DEP_SSNO
---------- ----------
       101  322456776
       101  123822332
       102  565534555

SQL> SELECT  e.eid,  CURSOR ( 
  2      SELECT  d.ssno AS dep_ssno  FROM  TABLE( e.dependents ) d  
  3        WHERE  d.age < 16 ) dep_tab 
  4    FROM  employees e; 

       EID DEP_TAB
---------- --------------------
       101 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

  DEP_SSNO
----------
 322456776
 123822332

       102 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

  DEP_SSNO
----------
 565534555