資料庫表--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和巢狀表本身中索引的額外開銷