oracle巢狀表

/*

以前在做報表的時候會經常用到oracle的記憶體表(其實是oracle巢狀表的部分功能，這裡在下邊介紹)來提高效能。
利用oracle記憶體表進行臨時運算透過ref cursor來返回我們想要的結果集。
open cur for select * from table(fun_to_table_rb1_1(cur_qc,cur_qm));
關於這部分的一些測試可以參看：http://www.itpub.net/showthread.php?s=2642ceb4225ba625b06fa33ee7c44a12&threadid=617298

最近把oracle巢狀表的其他功能仔細看了看並做了個簡單整理。

oracle提供兩種使用巢狀表的方法：
1． PL/SQL程式碼中作為擴充套件PL/SQL語言；(這部分內容就是上邊所說oracle記憶體表是oracle巢狀表的部分功能)
2． 作為物理儲存機制，以持久地儲存集合。

*/

--建立測試表：

CREATE TABLE dept
　　(deptno NUMBER(2) PRIMARY KEY,
　　 dname VARCHAR2(14),
　　 loc VARCHAR2(13)
　　);
　　
CREATE TABLE emp
　　(empno NUMBER(4) PRIMARY KEY,
　　 ename VARCHAR2(10),
　　 job VARCHAR2(9),
　　 mgr NUMBER(4) REFERENCES emp,
　　 hiredate DATE,
　　 sal NUMBER(7,2),
　　 comm NUMBER(7,2),
　　 deptno NUMBER(2) REFERENCES dept
　　);
　　
INSERT INTO dept SELECT * FROM scott.dept;
INSERT INTO emp SELECT * FROM scott.emp;

--建立type

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)
　　);
　　
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;

--使用巢狀表

CREATE TABLE dept_and_emp
　　(deptno NUMBER(2) PRIMARY KEY,
　　 dname VARCHAR2(14),
　　 loc VARCHAR2(13),
　　 emps emp_tab_type
　　)
　　NESTED TABLE emps STORE AS emps_nest;

--可以在巢狀表上增加約束(這裡我們先不執行此步驟，等做完下一步測試我們再建立約束)
--ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique
--巢狀表不支援參照完整性約束，不能參考任何其他表甚至自己
--給巢狀表增加資料，我們看看這兩種方式的結果有何不同
方式1：INSERT INTO
　　dept_and_emp
　　SELECT dept.*,
　　 CAST(
　　MULTISET( SELECT empno, ename, job, mgr, hiredate, sal,
　　comm
　　 FROM
　　emp
　　 WHERE emp.deptno
　　= dept.deptno ) AS emp_tab_type )
　　 FROM
　　dept;
--Oracle同樣提供方法去掉集合的巢狀，像關係型表一樣處理（能夠將EMPS列當作一個表，並自然連線且不需要連線條件）：
SELECT d.deptno, d.dname, emp.* FROM dept_and_emp D, TABLE(d.emps) emp;
--這裡執行看到結果是14條資料

delete from dept_and_emp;

方式2：INSERT INTO dept_and_emp
SELECT dept.*, CAST(MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm
　　FROM
　　emp,dept
　　 WHERE emp.deptno
　　= dept.deptno ) AS emp_tab_type ) from dept;

SELECT d.deptno, d.dname, emp.* FROM dept_and_emp D, TABLE(d.emps) emp;
--這裡執行看到結果是56條資料，顯然是錯誤的

--第一個是按照where等連線條件符合的某一個dept的emp表的資料作為一個集合儲存，而第二個沒有任何關聯條件，就是把所有emp的資料
--全部作為一個dept的資料儲存，這個寫法顯然是錯誤的，如果我們把剛才講的約束給巢狀表加上，就可以起到防止這種錯誤的功效了。

--增加約束再執行我們上邊的第二個insert語句將會報錯
--我們按照上邊第一個insert語句插入資料，繼續我們下邊的測試。

--按照“每行實際是一張表”的思想來更新：
UPDATE TABLE( SELECT emps FROM dept_and_emp WHERE deptno = 10) SET comm = 100;

--插入與刪除的語法：
　　INSERT INTO TABLE(SELECT emps FROM dept_and_emp WHERE deptno=10)
　　VALUES (1234,'NewEmp','Clerk',7782,SYSDATE,1200,NULL);
　　
　　DELETE FROM TABLE(SELECT emps FROM dept_and_emp WHERE deptno=20)
　　WHERE ename='SCOTT';

--一般而言，必須總是連線，而不能單獨查詢巢狀表（如emp_nest）中的資料，但是如果確實需要，是可以的。
--hint NESTED_TABLE_GET_REFS被用於EXP和IMP處理巢狀表。

　　SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID, SYS_NC_ROWINFO$ FROM emps_nest;

--而察看EMPS_NEST的結構看不到NESTED_TABLE_ID,SYS_NC_ROWINFO$兩列。對父表DEPT_AND_EMP來說NESTED_TABLE_ID是一個外來鍵。
--使用這個hint就可以直接操作巢狀表了：
　　UPDATE /*+NESTED_TABLE_GET_REFS+*/ emps_nest SET ename=INITCAP(ename);
　　
--巢狀表的儲存:
--上例中，現實產生了兩張表：
/*
　　DEPT_AND_EMP
　　(deptnob NUMBER(2),
　　dname VARCHAR2(14),
　　loc VARCHAR2(13),
　　SYS_NC0000400005$,
RAW(16))
　　
　　EMPS_NEST
　　(SYS_NC_ROWINFO$,
　　NESTED_TABLE_ID,
RAW(16),
　　empno NUMBER(4),
　　ename VARCHAR2(10),
　　job VARCHAR2(9),
　　mgr NUMBER(4),
　　hiredate DATE,
　　sal NUMBER(7,2),
　　comm NUMBER(7,2))　
*/　
--預設情況下，每個巢狀表列都產生一個額外的RAW(16)隱藏列，並在其上建立了唯一約束，用以指向巢狀表。而巢狀表中有兩個
--隱藏列：SYS_NC_ROWINFO$是作為一個物件返回所有標量元素的一個偽列；另一個NESTED_TABLE_ID的外來鍵回指向父表。
--可以看到真實程式碼：
/*
　　CREATE TABLE DEPT_AND_EMP
　　(DEPTNO NUMBER(2,0),
　　 DNAME VARCHAR2(14),
　　 LOC VARCHAR2(13),
　　 EMPS EMP_TAB_TYPE)
　　PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
　　LOGGING STORAGE(INITIAL 131072 NEXT 131072
　　MINEXTENTS 1 MAXEXTENTS 4096
　　PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1
　　BUFFER_POOL DEFAULT)
　　TABLESPACE USER
　　NESTED TABLE EMPS
　　STORE AS EMPS_NEST
　　RETURN BY VALUE;
　　
　　RETURN BY VALUE用來描述巢狀表如何返回到客戶應用程式中。
　　NESTED_TABLE_ID列必須是索引的，那麼較好的解決辦法就是使用IOT儲存巢狀表。
　　CREATE TABLE DEPT_AND_EMP
　　(DEPTNO NUMBER(2,0),
　　 DNAME VARCHAR2(14),
　　 LOC VARCHAR2(13),
　　 EMPS EMP_TAB_TYPE)
　　PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
　　LOGGING STORAGE(INITIAL 131072 NEXT 131072
　　MINEXTENTS 1 MAXEXTENTS 4096
　　PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1
　　BUFFER_POOL DEFAULT) TABLESPACE USER
　　NESTED TABLE EMPS
　　STORE AS EMPS_NEST
　　((empno NOT NULL,
UNIQUE(empno),
PRIMARY KEY(nested_table_id,empno))
　　ORGANIZATION
　　INDEX COMPRESS 1)
　　RETURN BY VALUE;
　　
　　這樣與最初預設的巢狀表相比，使用了較少的儲存空間並有最需要的索引。
　　不使用巢狀表作為永久儲存機制的原因
　　1．增加了RAW(16)列的額外開銷，父表和子表都將增加這個額外的列；
　　2．當通常已經有唯一約束時，父表上的唯一約束是額外開銷；
　　3．沒有使用不支援的結構（NESTED_TABLE_GET_REFS），巢狀表不容易使用。
　　一般推薦在程式設計結構和檢視中使用巢狀表。如果要使用巢狀表作為儲存機制，
確保巢狀表是IOT，以避免NESTED_TABLE_ID和巢狀表本身中索引的額外開銷。

以上參考oracle高階專家程式設計。
*/

[@more@]