Oracle嵌套表使用和存儲(chǔ)分析

1、Oracle 嵌套表的使用一、嵌套表的定義：嵌套表是表中之表。 一個(gè)嵌套表是某些行的集合， 它在主表中表示為其中的一列。對(duì)主表中的每一條記錄，嵌套表可以包含多個(gè)行。在某種意義上，它是在一個(gè)表中存儲(chǔ)一對(duì)多關(guān)系的一種方法?？疾橐粋€(gè)包含部門(mén)信息的表， 在任何時(shí)間內(nèi)每個(gè)部門(mén)會(huì)有很多項(xiàng)目正在實(shí)施。在一個(gè)嚴(yán)格的關(guān)系模型中， 將需要建立兩個(gè)獨(dú)立的表 department 和 project。嵌套表允許在 department 表中存放關(guān)于項(xiàng)目的信息。勿需執(zhí)行聯(lián)合操作，就可以通過(guò) department 表直接訪問(wèn)項(xiàng)目表中的記錄。這種不經(jīng)聯(lián)合而直接選擇數(shù)據(jù)的能力使得用戶對(duì)數(shù)據(jù)訪問(wèn)更加容易。 甚至在并沒(méi)有定義方法

2、來(lái)訪問(wèn)嵌套表的情況下，也能夠很清楚地把部門(mén)和項(xiàng)目中的數(shù)據(jù)聯(lián)系在一起。 在嚴(yán)格的關(guān)系模型中， department 和 project 兩個(gè)表的聯(lián)系需要通過(guò)外部關(guān)鍵字（外鍵）關(guān)系才能實(shí)現(xiàn)。二、舉例說(shuō)明嵌套表的使用：假設(shè)有一個(gè)關(guān)于動(dòng)物飼養(yǎng)員的表， 希望其中具有他們飼養(yǎng)的動(dòng)物的信息。 用一個(gè)嵌套表，就可以在同一個(gè)表中存儲(chǔ)飼養(yǎng)員和其飼養(yǎng)的全部動(dòng)物的信息。1、創(chuàng)建類型 animal_ty ：此類型中，對(duì)于每個(gè)動(dòng)物都包含有一個(gè)記錄，記載了其品種、名稱和出生日期信息。CREATE TYPE animal_ty AS OBJECT (breed varchar2(25),name varchar2(25),b

3、irthdate date);2、創(chuàng)建 animals_nt ：此類型將用作一個(gè)嵌套表的基礎(chǔ)類型。CREATE TYPE animals_nt as table of animal_ty;3、創(chuàng)建表 breeder ：飼養(yǎng)員的信息表create table breeder(breedername varchar2(25),animals animal_nt)nested table animals store as animals_nt_tab;4、向嵌套表中插入記錄insert into breedervalues('mary',animal_nt(animal_ty(

4、9;dog','butch','31-MAR-97'),animal_ty('dog','rover','31-MAR-97'),animal_ty('dog','julio','31-MAR-97');insert into breedervalues('jane',animal_nt(animal_ty('cat','an','31-MAR-97'),animal_ty('cat

5、9;,'jame','31-MAR-97'),animal_ty('cat','killer','31-MAR-97');commit;5、查詢嵌套表select name,birthdate fromtable(select animals from breeder);select name,birthdate fromtable(select animals from breederwhere breedername=mary)where name=dog;三、嵌套表的特點(diǎn)：1、對(duì)象復(fù)用：如果編寫(xiě)面向?qū)ο蟮拇a

6、，就提高了重用以前編寫(xiě)的代碼模塊的機(jī)會(huì)。同樣，如果創(chuàng)建面向?qū)ο蟮臄?shù)據(jù)庫(kù)對(duì)象， 也就提高了數(shù)據(jù)庫(kù)對(duì)象能夠被重用的機(jī)會(huì)。2、標(biāo)準(zhǔn)支持：如果創(chuàng)建標(biāo)準(zhǔn)的對(duì)象，那么它們被重用的機(jī)會(huì)就會(huì)提高。如果有多個(gè)應(yīng)用或多個(gè)表使用同一數(shù)據(jù)庫(kù)對(duì)象集合， 那么它就是既成事實(shí)的數(shù)據(jù)庫(kù)對(duì)象標(biāo)準(zhǔn)。3、定義訪問(wèn)路徑：對(duì)于每一個(gè)對(duì)象，用戶可定義在其上運(yùn)行的過(guò)程和函數(shù)，從而可以使數(shù)據(jù)和訪問(wèn)此數(shù)據(jù)的方法聯(lián)合起來(lái)。有了用這種方式定義的訪問(wèn)路徑，就可以標(biāo)準(zhǔn)化數(shù)據(jù)訪問(wèn)的方法并提高對(duì)象的可復(fù)用性。-以前在做報(bào)表的時(shí)候會(huì)經(jīng)常用到oracle的內(nèi)存表功能，這里在下邊介紹 ) 來(lái)提高性能。利用 oracle內(nèi)存表進(jìn)行臨時(shí)運(yùn)算通過(guò)ref cursor

7、( 其實(shí)是 oracle嵌套表的部分來(lái)返回我們想要的結(jié)果集。open cur for select * from table(fun_to_table_rb1_1(cur_qc,cur_qm);關(guān)于這部分的一些測(cè)試可以參看：最近把oracle嵌套表的其他功能仔細(xì)看了看并做了個(gè)簡(jiǎn)單整理。oracle提供兩種使用嵌套表的方法：1 PL/SQL 代碼中作為擴(kuò)展 PL/SQL語(yǔ)言； ( 這部分內(nèi)容就是上邊所說(shuō) oracle 內(nèi)存表是 oracle 嵌套表的部分功能 )2 作為物理存儲(chǔ)機(jī)制，以持久地存儲(chǔ)集合。*/- 創(chuàng)建測(cè)試表：CREATE TABLE dept(deptno NUMBER(2) PRI

8、MARY 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 ;INSERT INTO emp SELECT * FROM ;-

9、創(chuàng)建 typeCREATE 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),

10、loc VARCHAR2(13),emps emp_tab_type)NESTED TABLE emps STORE AS emps_nest;- 可以在嵌套表上增加約束 ( 這里我們先不執(zhí)行此步驟，等做完下一步測(cè)試我們?cè)賱?chuàng)建約束 )-ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique- 嵌套表不支持參照完整性約束，不能參考任何其他表甚至自己- 給嵌套表增加數(shù)據(jù)，我們看看這兩種方式的結(jié)果有何不同方式 1：INSERT INTOdept_and_emp SELECT dept.*, CAST(MULTISET( SELECT empno, e

11、name, job, mgr, hiredate, sal, commFROMempWHERE= ) AS emp_tab_type )FROMdept;-Oracle 同樣提供方法去掉集合的嵌套，像關(guān)系型表一樣處理（能夠?qū)?EMPS列當(dāng)作一個(gè)表，并自然連接且不需要連接條件）：SELECT , , emp.* FROM dept_and_emp D, TABLE emp;- 這里執(zhí)行看到結(jié)果是 14 條數(shù)據(jù)delete from dept_and_emp;方式 2：INSERT INTO dept_and_empSELECT dept.*, CAST(MULTISET( SELECT empn

12、o, ename, job, mgr, hiredate, sal, commFROMemp,deptWHERE= ) AS emp_tab_type ) from dept;SELECT , , emp.* FROM dept_and_emp D, TABLE emp;- 這里執(zhí)行看到結(jié)果是 56 條數(shù)據(jù)，顯然是錯(cuò)誤的- 第一個(gè)是按照 where 等連接條件符合的某一個(gè) dept 的 emp表的數(shù)據(jù)作為一個(gè)集合存儲(chǔ)，而第二個(gè)沒(méi)有任何關(guān)聯(lián)條件，就是把所有 emp的數(shù)據(jù)- 全部作為一個(gè) dept 的數(shù)據(jù)存儲(chǔ)，這個(gè)寫(xiě)法顯然是錯(cuò)誤的，如果我們把剛才講的約束給嵌套表加上，就可以起到防止這種錯(cuò)誤的功效了

13、。- 增加約束再執(zhí)行我們上邊的第二個(gè) insert 語(yǔ)句將會(huì)報(bào)錯(cuò)- 我們按照上邊第一個(gè) insert 語(yǔ)句插入數(shù)據(jù)，繼續(xù)我們下邊的測(cè)試。- 按照“每行實(shí)際是一張表”的思想來(lái)更新：UPDATE TABLE( SELECT emps FROM dept_and_emp WHERE deptno = 10) SET comm = 100;- 插入與刪除的語(yǔ)法：INSERT INTO TABLE(SELECT emps FROM dept_and_emp WHERE deptno=10) VALUES (1234,'NewEmp','Clerk',7782,SYSDAT

14、E,1200,NULL);DELETE FROM TABLE(SELECT emps FROM dept_and_emp WHERE deptno=20) WHERE ename='SCOTT'- 一般而言，必須總是連接，而不能單獨(dú)查詢嵌套表（如 emp_nest）中的數(shù)據(jù)，但是如果確實(shí)需要，是可以的。-hint NESTED_TABLE_GET_REFS被用于 EXP和 IMP處理嵌套表。SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID, SYS_NC_ROWINFO$ FROM emps_nest;- 而察看 EMPS_N

15、EST的結(jié)構(gòu)看不到 NESTED_TABLE_ID,SYS_NC_ROWINFO兩列。對(duì)$父表 DEPT_AND_EMP來(lái)說(shuō) NESTED_TABLE_ID是一個(gè)外鍵。- 使用這個(gè) hint 就可以直接操作嵌套表了：UPDATE /*+NESTED_TABLE_GET_REFS+*/ emps_nest SET ename=INITCAP(ename);- 嵌套表的存儲(chǔ) :- 上例中，現(xiàn)實(shí)產(chǎn)生了兩張表：/*DEPT_AND_EMP(deptnob NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13),SYS_NC0000400005$,RAW(16)EMP

16、S_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)*/- 默認(rèn)情況下，每個(gè)嵌套表列都產(chǎn)生一個(gè)額外的 RAW(16)隱藏列，并在其上創(chuàng)建了唯一約束，用以指向嵌套表。而嵌套表中有兩個(gè)- 隱藏列： SYS_NC_ROWINFO是作為一個(gè)對(duì)象返回所有標(biāo)量$元素的一個(gè)偽列；另一個(gè) NESTED_TABLE_的ID外鍵回指向父表。- 可以看到真

17、實(shí)代碼：/*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 USE

18、RNESTED TABLE EMPSSTORE AS EMPS_NESTRETURN BY VALUE;RETURN BY VALUE用來(lái)描述嵌套表如何返回到客戶應(yīng)用程序中。NESTED_TABLE_列ID必須是索引的， 那么較好的解決辦法就是使用IOT 存儲(chǔ)嵌套表。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 255LOGGING STORAGE(INITIA

19、L 131072 NEXT 131072MINEXTENTS 1 MAXEXTENTS 4096PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1BUFFER_POOL DEFAULT) TABLESPACE USERNESTED TABLE EMPSSTORE AS EMPS_NEST(empno NOT NULL,UNIQUE(empno),PRIMARY KEY(nested_table_id,empno)ORGANIZATIONINDEX COMPRESS 1)RETURN BY VALUE;這樣與最初默認(rèn)的嵌套表相比，使用了較少的存儲(chǔ)空間并有最需要的

20、索引。不使用嵌套表作為永久存儲(chǔ)機(jī)制的原因1增加了 RAW(16)列的額外開(kāi)銷，父表和子表都將增加這個(gè)額外的列；2當(dāng)通常已經(jīng)有唯一約束時(shí)，父表上的唯一約束是額外開(kāi)銷；3沒(méi)有使用不支持的結(jié)構(gòu) （ NESTED_TABLE_GET_REFS），嵌套表不容易使用。一般推薦在編程結(jié)構(gòu)和視圖中使用嵌套表。如果要使用嵌套表作為存儲(chǔ)機(jī)制，確保嵌套表是 IOT，以避免 NESTED_TABLE_和ID嵌套表本身中索引的額外開(kāi)銷。以上參考 oracle高級(jí)專家編程。本文來(lái)自 CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：可變數(shù)組一、可變數(shù)組的定義：可變數(shù)組與嵌套表相似，也是一種集合。一個(gè)可變數(shù)組是對(duì)象的一個(gè)集合，其中每個(gè)對(duì)象都具

21、有相同的數(shù)據(jù)類型?？勺償?shù)組的大小由創(chuàng)建時(shí)決定。在表中建立可變數(shù)組后， 可變數(shù)組在主表中作為一個(gè)列對(duì)待。從概念上講， 可變數(shù)組是一個(gè)限制了行集合的嵌套表。可變數(shù)組，允許用戶在表中存儲(chǔ)重復(fù)的屬性。 例如：假設(shè)用戶有一個(gè) project 表，并在項(xiàng)目中指定了工作人員， 一個(gè)項(xiàng)目可以有多個(gè)工人， 而一個(gè)工人也可以為多個(gè)項(xiàng)目工作。在嚴(yán)格的關(guān)系模型中，用戶可以創(chuàng)建一個(gè) project 表，一個(gè) worker 表和存儲(chǔ)它們之間關(guān)系的交叉表 project_worker 。用戶可使用可變數(shù)組在 project 表中存儲(chǔ)工人的名字。 如果項(xiàng)目限定的工人數(shù)不超過(guò) 10 人，可以建立一個(gè)以 10 個(gè)數(shù)據(jù)項(xiàng)為限的可變

22、數(shù)組。 接下來(lái)就可處理此可變數(shù)組， 從而對(duì)于每一個(gè)項(xiàng)目， 可以選取其中所有工人的名字， 而勿需查詢表 worker 。二、舉例說(shuō)明可變數(shù)組的使用：1、創(chuàng)建類型 comm_infoCREATE TYPE comm_info AS OBJECT ( /*此類型為通訊方式的集合no number(3), /*通訊類型號(hào)comm_type varchar2(20), /*通訊類型comm_no varchar2(30); /*號(hào)碼2、創(chuàng)建可變數(shù)組 comm_info_listCREATE TYPE comm_info_list ASVARRAY(50) OF comm_info;3、創(chuàng)建表create table user_info(user_id number(6), /*用戶 ID 號(hào)user_name varchar2(20), /*用戶名稱user_comm comm_info_list); /*與用戶聯(lián)系的通訊方式4、向可變數(shù)組插入記錄insert into user_infovalues(1,'mary',comm_info_list(co