数据库表--nested table

使用嵌套表有两种方法，第一，在PL/SQL代码中使用，用来扩展PL/SQL语言；第二，作为一种物理存储机制，持久的存储集合。

创建对象类型
JEL@JEL >create or replace type emp_type as object (empno number(4),ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2));
  2  /

Type created.
创建嵌套表类型
JEL@JEL >create or replace type emp_tab_type as table of emp_type;
  2  /

Type created.

创建嵌套表
JEL@JEL >create table dept_and_emp
  2  ( deptno number(2) primary key,
  3  dname varchar2(14),
  4  loc varchar2(13),
  5  emps emp_tab_type
  6  )
  7  nested table emps store as emps_nt;

Table created.

JEL@JEL >alter table emps_nt add constraint emps_empno_unique unique(empno);

Table altered.

这样，除了dept_and_emp之外，还会创建一个真正的物理表emps_nt,这两个表是分开的。此外，嵌套表比支持引用完整性约束，因为他们不能引用任何表，甚至他们自己。

JEL@JEL >select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT_AND_EMP                   TABLE
EMPS_NT                        TABLE

插入数据
JEL@JEL >insert into  dept_and_emp values (1,'Dept 1','shandong',emp_tab_type(emp_type(1,'zhangsan','kaifa',1,sysdate-100,8000,1),emp_type(2,'lisi','kaifa',2,sysdate-200,9000,2),emp_type(3,'wangwu','guanli',3,sysdate-300,10000,1)));

1 row created.

查询数据
JEL@JEL >col emps for a40
JEL@JEL >select * from dept_and_emp;

    DEPTNO DNAME          LOC           EMPS(EMPNO, ENAME, JOB, MGR, HIREDATE, S
---------- -------------- ------------- ----------------------------------------
         1 Dept 1         shandong      EMP_TAB_TYPE(EMP_TYPE(1, 'zhangsan', 'ka
                                        ifa', 1, '02-SEP-13', 8000, 1), EMP_TYPE
                                        (2, 'lisi', 'kaifa', 2, '25-MAY-13', 900
                                        0, 2), EMP_TYPE(3, 'wangwu', 'guanli', 3
                                        , '14-FEB-13', 10000, 1))


oracle 提供了一种方法，可以取消集合的嵌套。把emps强制转换成一个表，不需要连接条件
JEL@JEL >select d.deptno,d.dname,emp.* from dept_and_emp d,table(d.emps) emp;

    DEPTNO DNAME           EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- ---------- ---------- --------- ---------- ---------
       SAL       COMM
---------- ----------
########## Dept 1              1 zhangsan   kaifa              1 02-SEP-13
      8000          1

########## Dept 1              2 lisi       kaifa              2 25-MAY-13
      9000          2

########## Dept 1              3 wangwu     guanli             3 14-FEB-13
     10000          1

给部门1下的员工发100的奖金（更新1行）
JEL@JEL >update table(select emps from dept_and_emp where deptno=1) set comm=100;

3 rows updated.

JEL@JEL >commit;

Commit complete.

给部门2下的员工发奖金（更新0行，没有部门2）
JEL@JEL >update table(select emps from dept_and_emp where deptno=2) set comm=100;
update table(select emps from dept_and_emp where deptno=2) set comm=100
*
ERROR at line 1:
ORA-22908: reference to NULL table value

如果返回少于一行（一个嵌套表都没有），更新会失败，就好像我们的更新中漏写了表名一样

跟多个部门发奖金（更新多于一行，有多个嵌套表）
JEL@JEL >insert into  dept_and_emp values (2,'Dept 1','shandong',emp_tab_type(emp_type(4,'zhangsan','kaifa',1,sysdate-100,8000,1),emp_type(5,'lisi','kaifa',2,sysdate-200,9000,2),emp_type(6,'wangwu','guanli',3,sysdate-300,10000,1)));

1 row created.

JEL@JEL >commit;

Commit complete.
JEL@JEL >update table(select emps from dept_and_emp where deptno>0) set comm=100;
update table(select emps from dept_and_emp where deptno>0) set comm=100
             *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

更新失败

在嵌套表模型中，每一个父行有一个表，而关系模型中，每个父行有一个行集。

向嵌套表中增加一行
JEL@JEL >insert into table(select emps from dept_and_emp where deptno=1) values (7,'zhaoliu','kaifa',5,sysdate-20,5000,200);

1 row created.

JEL@JEL >commit;

Commit complete.

删除嵌套表中一行

通过NESTED_TABLE_GET_REFS可以查看并单独更新嵌套表，但推荐使用消除嵌套的方法查看嵌套表结构（table函数）
select /*+NESTED_TABLE_GET_REFS*/ nested_table_id,sys_nc_rowinfo$ from emps_nt;

NESTED_TABLE_ID
--------------------------------
SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
--------------------------------------------------------------------------------
ED3CF30863A2B551E040A8C0655011C2
EMP_TYPE(1, 'zhangsan', 'kaifa', 1, '02-SEP-13', 8000, 100)

ED3CF30863A2B551E040A8C0655011C2
EMP_TYPE(2, 'lisi', 'kaifa', 2, '25-MAY-13', 9000, 100)

ED3CF30863A2B551E040A8C0655011C2
EMP_TYPE(3, 'wangwu', 'guanli', 3, '14-FEB-13', 10000, 100)


NESTED_TABLE_ID
--------------------------------
SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
--------------------------------------------------------------------------------
ED3CF30863A4B551E040A8C0655011C2
EMP_TYPE(4, 'zhangsan', 'kaifa', 1, '02-SEP-13', 8000, 1)

ED3CF30863A4B551E040A8C0655011C2
EMP_TYPE(5, 'lisi', 'kaifa', 2, '25-MAY-13', 9000, 2)

ED3CF30863A4B551E040A8C0655011C2
EMP_TYPE(6, 'wangwu', 'guanli', 3, '14-FEB-13', 10000, 1)


NESTED_TABLE_ID
--------------------------------
SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
--------------------------------------------------------------------------------
ED3CF30863A2B551E040A8C0655011C2
EMP_TYPE(7, 'zhaoliu', 'kaifa', 5, '21-NOV-13', 5000, 200)


7 rows selected.

JEL@JEL >select /*+NESTED_TABLE_GET_REFS*/ empno,ename from emps_nt;

     EMPNO ENAME
---------- ----------
         1 zhangsan
         2 lisi
         3 wangwu
         4 zhangsan
         5 lisi
         6 wangwu
         7 zhaoliu

7 rows selected.

JEL@JEL >update /*+NESTED_TABLE_GET_REFS*/ emps_nt set ename='AA' where ename ='zhangsan';

2 rows updated.

JEL@JEL >commit;

Commit complete.

JEL@JEL >select /*+NESTED_TABLE_GET_REFS*/ empno,ename from emps_nt;

     EMPNO ENAME
---------- ----------
         1 AA
         2 lisi
         3 wangwu
         4 AA
         5 lisi
         6 wangwu
         7 zhaoliu

7 rows selected.


并不把嵌套表做为一种持久存储机制，原因如下：
1、这会增加不必要的RAW（16）列存储开销
2、这会在父表上增加另外一个唯一约束
3、嵌套表本身使用起来并不容易，如果是大批量更新，则无法简单的消除嵌套
一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机制，
确保嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1063009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1063009/