Nested Tables (Cont.)
  1. Q: Create a relational table employees with columns, eid, eperson, and dependents, the last of which can take on multiple values.

    This clause specifies a tablename, dependents_tab, to contain multiple person_t objects that is in the dependents column of employees.

  2. Q: Insert rows in employees for John Smith and David Andrews. 
SQL> CREATE TABLE  employees  (  
  2    eid         INT  PRIMARY KEY,  
  3    eperson     person_t,  
  4    dependents  dependents_t  
  5  )  NESTED TABLE  dependents  STORE AS  dependents_tab;  
  6  /  

Table created.

SQL> INSERT INTO  employees  VALUES (  
  2    101,  person_t( 123897766, name_t( 'Smith', 'John', 'P' ), 45 ),  
  3    dependents_t(   
  4      person_t( 322456776, name_t( 'Smith', 'Michael', 'J' ), 8 ),  
  5      person_t( 123822332, name_t( 'Smith', 'Susan', 'R' ), 12 ) ) );  

1 row created.

SQL> INSERT INTO  employees  VALUES (  
  2    102,  person_t( 432112233, name_t( 'Andrews', 'Davis', 'S' ), 32 ),  
  3    dependents_t(  
  4      person_t( 565534555, name_t( 'Shaw', 'David', 'M' ), 3 ) ) );

The two dependents of employee 101 constitute a small object (nested) table, as does the single dependent of employee 102, although in fact all dependents objects lie in the single dependents_tab table.

dependents_tab
eid eperson dependents
101 person_t( 123897766,   name_t('Smith','John','P'), 45 ) person_t( 322456776,   name_t('Smith,'Michael','J'), 8 )
person_t( 123822332,   name_t('Smith,'Susan','R'), 12 )
102 person_t( 432112233,   name_t('Andrews','David','S'), 32 ) person_t( 565534555,   name_t('Shaw','David','M'), 3 )