NestedTable

Nested Table:
  Nested table is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table may contain multiple rows. In one sense, it's a way of storing a one-to-many relationship within one table, Consider a table that contains information about departments, each of which may have many projects in progress at any one time. In a strictly relational model, you would create two separate tables - DEPT and PROJECT.
  nested table allow you to store the information about projects within the DEPT table. The PROJECT table records can be accessed directly via the DEPT table, without the need to perform a join. The ability to select the data without traversing joins may make data easier to access for users. Even if you do not define methods for accessing the nested data, you have clearly associated the department and project data. In a strictly relational model, the association between the DEPT and PROJECT tables would be accomplished via foreign key relationships.



SQL> create or replace type emp_ty as object
  2  (desg varchar2(10),
  3  dname varchar2(10),
  4  doj date);
  5  /

Type created.

SQL> create type emp_nt as table of emp_ty;
  2  /

Type created.

SQL> create table empdata
  2  (ename varchar2(10),
  3  details emp_nt)
  4  nested table details store as emp_nt_tab;

Table created.

SQL> desc empdata
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 DETAILS                                            EMP_NT

SQL> set describe depth 2
SQL> desc empdata
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 DETAILS                                            EMP_NT
   DESG                                             VARCHAR2(10)
   DNAME                                            VARCHAR2(10)
   DOJ                                              DATE


SQL> ed
Wrote file afiedt.buf

  1  insert into empdata values (
  2  'Raju',
  3  emp_nt(
  4  emp_ty('Clerk','Sales','12-Sep-05'),
  5  emp_ty('Asst','Mrkt','15-Oct-04'),
  6* emp_ty('Mngr','Sales','13-Aug-05')))
SQL> /

1 row created.

SQL> select * from empdata;

ENAME
----------
DETAILS(DESG, DNAME, DOJ)
--------------------------------------------------------------------------------
Raju
EMP_NT(EMP_TY('Clerk', 'Sales', '12-SEP-05'), EMP_TY('Asst', 'Mrkt', '15-OCT-04'
), EMP_TY('Mngr', 'Sales', '13-AUG-05'))


SQL>  select ename,n.desg,n.doj from empdata,table(empdata.details) n;

ENAME      DESG       DOJ
---------- ---------- ---------
Raju       Clerk      12-SEP-05
Raju       Asst       15-OCT-04
Raju       Mngr       13-AUG-05