Nested Tables

A nested table is a table within a table.
Nested table is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold an entire sub-table.
Nested tables allow items of TABLE type to include tables with distinct column names.

The nested table type is useful for models requiring referential integrity, and is especially suited to master-detail and one-to many relationships.

Declaring a Nested Table: Syntax 

CREATE TYPE tablename (columnname datatype [NOT NULL]
    {, columnname datatype [NOT NULL] ...}
    [, PRIMARY KEY {columnname {, columnname ...})])
  [NESTED TABLE columnname STORE AS tablename
    {, NESTED TABLE columnname STORE AS tablename ...}];

Question: Create a table type called dependents_t to hold tables of person_t objects. 

SQL> CREATE TYPE  dependents_t  AS TABLE OF  person_t;
Type created.



      Two atoms are walking down the street.    
      The first one sees the second one and asks,    
      “Hey, what’s the matter? You look a little down.”    
      The second atom says, “Yeah, I lost an electron.”    
      The first atom says, “Oh man, are you sure?”    
      The second atom says, “I'm positive.”