 Nested table in Oracle
2010-06-08 11:56:55
I was tasked with researching on how nested tables work in Oracle 10gR2. After reading documents and doing some tests, here are the basic command to create a nested table. Let's say I would like to store all the employees in each department (no actual design involved). The table that I will be creating will have the following columns:
- department name
- employee list
where the employee list is actually a table.
First, I created a type object that will hold the employees first and last names:
SQL> create type emp_obj as object
2 (first_name varchar2(50),
3 last_name varchar2(50));
4 /
Type created.
Then I created a table that will hold the emp_obj type:
SQL> create type emp_obj_tab is table of emp_obj;
2 /
Type created.
Then, I created the actual table:
SQL> create table emp_tab (
2 dept_name varchar2(50),
3 emp_list emp_obj_tab
4 ) nested table emp_list store as emp_list_tab
5 /
Table created.
The "nested table" clause indicates that the emp_list is a physical table with the name emp_list_tab. Now, insert into the table:
SQL> insert into emp_tab values ('I.T.',emp_obj_tab(emp_obj('JOHN','DOE')));
1 row created.
SQL> commit;
Commit complete.
Let's select from the table:
SQL> select et.dept_name, el.first_name, el.last_name
2 from emp_tab et, table(et.emp_list) el
3 order by el.first_name
4 /
DEPT_ FIRST_NAME LAST_NAME
----- -------------------- --------------------
I.T. JOHN DOE
To insert another employee in the table under the I.T. dept:
SQL> insert into table(select emp_list from emp_tab where dept_name = 'I.T.')
2 values ('JOE','SMIHT');
1 row created.
SQL> commit;
Commit complete.
To update the employee name (as in this case, SMITH was spelled):
SQL> update table(select emp_list from emp_tab where dept_name = 'I.T.')
2 set last_name = 'SMITH'
3 where first_name = 'JOE'
4 and last_name = 'SMIHT';
1 row updated.
SQL> commit;
Commit complete.
SQL> select et.dept_name, el.first_name, el.last_name
2 from emp_tab et, table(et.emp_list) el
3 order by el.first_name
4 /
DEPT_ FIRST_NAME LAST_NAME
----- -------------------- --------------------
I.T. JOE SMITH
I.T. JOHN DOE
To delete an employee:
SQL> delete from table(select emp_list from emp_tab where dept_name = 'I.T.')
2 where first_name = 'JOHN' and last_name = 'DOE';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select et.dept_name, el.first_name, el.last_name
2 from emp_tab et, table(et.emp_list) el
3 order by el.first_name
4 /
DEPT_ FIRST_NAME LAST_NAME
----- -------------------- --------------------
I.T. JOE SMITH
Hope this helps. | 
