/*
以前在做報表的時候會經常用到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?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),
mgr NUMBER(4),
comm NUMBER(7,2)
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;
--使用嵌套表
CREATE TABLE dept_and_emp
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 )
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
= dept.deptno ) AS emp_tab_type ) from dept;
--這裡執行看到結果是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存儲嵌套表。
BUFFER_POOL DEFAULT) TABLESPACE USER
((empno NOT NULL,
UNIQUE(empno),
PRIMARY KEY(nested_table_id,empno))
ORGANIZATION
INDEX COMPRESS 1)
這樣與最初預設的嵌套表相比，使用了較少的存儲空間并有最需要的索引。
不使用嵌套表作為永久存儲機制的原因
1．增加了RAW(16)列的額外開銷，父表和子表都将增加這個額外的列；
2．當通常已經有唯一限制時，父表上的唯一限制是額外開銷；
3．沒有使用不支援的結構（NESTED_TABLE_GET_REFS），嵌套表不容易使用。
一般推薦在程式設計結構和視圖中使用嵌套表。如果要使用嵌套表作為存儲機制，
確定嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的額外開銷。
以上參考oracle進階專家程式設計。
轉載自：http://www.itpub.net/thread-640129-1-1.html