????? 通常情況下，在PL/SQL中，處理單行單列的數(shù)據(jù)可以使用標(biāo)量變量，而處理單行多列的數(shù)據(jù)則使用PL/SQL記錄是不錯的選擇。單列多行數(shù)據(jù)
則由聯(lián)合數(shù)組或嵌套表來完成，其特點是類似于單列數(shù)據(jù)庫表。在Oracle 9i 之前稱為PL/SQL索引表，9i 之后稱之為聯(lián)合數(shù)組。嵌套表也是集合
類型中的一種，下面分別介紹這兩種集合數(shù)據(jù)類型的使用方法。

?

一、聯(lián)合數(shù)組

1、聯(lián)合數(shù)組的特性 類似于一張簡單的SQL表，按照主鍵進行檢索數(shù)據(jù) 其數(shù)據(jù)行并不是按照預(yù)定義的順序存儲。當(dāng)使用變量來檢索其數(shù)據(jù)時，每行數(shù)據(jù)會分配一個連續(xù)的下標(biāo)且從1開始。 下標(biāo)可以為負(fù)值，且下標(biāo)的數(shù)據(jù)類型支持binary_integer,pls_integer,varchar2 其數(shù)據(jù)元素個數(shù)無限制 不能作為表列的數(shù)據(jù)類型使用，即只能作為PL/SQL復(fù)合數(shù)據(jù)類型使用 存放的數(shù)據(jù)類型是臨時數(shù)據(jù)，故不支持insert,select into 等SQL語句,等同于SQL server中的表變量 2、語法 TYPE type_name IS TABLE OF element_type [NOT NULL] -->element_type為聯(lián)合數(shù)組元數(shù)據(jù)指定數(shù)據(jù)類型(先使用TYPE聲明表結(jié)構(gòu)) INDEX BY key_type; -->元素下標(biāo)的數(shù)據(jù)類型 table_name TYPE_NAME; -->再使用聲明的TYPE類型聲明實際數(shù)組名 3、示例 --使用PLS_INTEGE類型聯(lián)合數(shù)組 scott@CNMMBO> DECLARE -->定義游標(biāo) 2 CURSOR name_cur IS 3 SELECT dname 4 FROM dept 5 WHERE deptno < 40; 6 7 TYPE name_type IS TABLE OF dept.dname%TYPE -->定義一個聯(lián)合數(shù)組類型，且其下標(biāo)的數(shù)據(jù)類型為PLS_INTEGER 8 INDEX BY PLS_INTEGER; 9 10 dname_tab name_type; -->聲明類型為name_type的聯(lián)合數(shù)組dname_tab 11 v_counter INTEGER := 0; 12 BEGIN 13 FOR name_rec IN name_cur 14 LOOP 15 v_counter := 16 v_counter 17 + 1; 18 dname_tab( v_counter ) := name_rec.dname; -->對聯(lián)合數(shù)據(jù)進行循環(huán)賦值 19 DBMS_OUTPUT.put_line( 'Dname is(' 20 || v_counter 21 || '):' 22 || dname_tab( v_counter ) ); -->輸出聯(lián)合數(shù)組中的所有元素 23 END LOOP; 24 END; 25 / Dname is(1):ACCOUNTING Dname is(2):RESEARCH Dname is(3):SALES PL/SQL procedure successfully completed. --使用VARCHAR2類型聯(lián)合數(shù)組 scott@CNMMBO> DECLARE 2 TYPE score_type IS TABLE OF NUMBER 3 INDEX BY VARCHAR2( 10 ); -->注意此處聲明的聯(lián)合數(shù)組的下標(biāo)數(shù)據(jù)類型為varchar2 4 5 score_tab score_type; 6 BEGIN 7 score_tab( 'SCOTT' ) := 95; -->對不同下標(biāo)分別進行賦值 8 score_tab( 'JOHN' ) := 98; 9 score_tab( 'ROBINSON' ) := 96; 10 DBMS_OUTPUT.put_line( 'First element is ' 11 || score_tab.FIRST ); -->輸出聯(lián)合數(shù)組score_tab中的第一個元素的下標(biāo) 12 DBMS_OUTPUT.put_line( 'last element is ' 13 || score_tab.LAST ); -->輸出聯(lián)合數(shù)組score_tab中的最后一個元素的下標(biāo) 14 DBMS_OUTPUT.put_line( 'The score of Scott is ' 15 || score_tab( 'SCOTT' ) ); -->輸出下標(biāo)為SCOTT的元素的值 16 END; 17 / First element is JOHN -->注意此處輸出的是JOHN(第一個和最后一個是按字母排序得到的) last element is SCOTT The score of Scott is 95 PL/SQL procedure successfully completed. --下標(biāo)超范圍的情況 scott@CNMMBO> DECLARE 2 TYPE score_type IS TABLE OF NUMBER 3 INDEX BY VARCHAR2( 10 ); 4 5 score_tab score_type; 6 BEGIN 7 score_tab( 'SCOTT' ) := 95; 8 score_tab( 'JOHN' ) := 98; 9 score_tab( 'ROBINSON' ) := 96; 10 DBMS_OUTPUT.put_line( 'The score of Jason is ' 11 || score_tab( 'JASON' ) ); 12 END; 13 / DECLARE * ERROR at line 1: ORA-01403: no data found -->當(dāng)下標(biāo)超范圍時，將收到ORA-01403錯誤 ORA-06512: at line 10

二、嵌套表

1、嵌套表的特點： 元素下表從1開始，個數(shù)沒有限制.即元素個數(shù)可以動態(tài)增長 嵌套表的數(shù)組元素值可以是稀疏的，即可以使得中間的某個元素沒有賦值 嵌套表的語法與聯(lián)合數(shù)組類似，不同的是僅僅是少了index by子句 嵌套表必需先初始化，然后才能引用其中的元素。如果初始化為空值，則后續(xù)需要使用extend來擴展其大小 嵌套表初始化時為密集的，但允許有間隙，即允許使用內(nèi)置過程delete從嵌套表中刪除元素 嵌套表類型可以作為表列的數(shù)據(jù)類型來使用 2、語法 TYPE type_name IS TABLE OF element_type [NOT NULL]; -->element_type為索引表元數(shù)據(jù)指定數(shù)據(jù)類型(先使用TYPE聲明表結(jié)構(gòu)) table_name TYPE_NAME; -->再使用聲明的TYPE類型聲明實際嵌套表 3、示例 --聲明嵌套表并輸出嵌套表的實際內(nèi)容 scott@CNMMBO> DECLARE 2 CURSOR name_cur IS 3 SELECT dname 4 FROM dept 5 WHERE deptno < 40; 6 7 TYPE name_type IS TABLE OF dept.dname%TYPE; 8 9 dname_tab name_type := name_type( ); -->需要初始化,否則將收到 ORA-06531：Reference to uninitialized collection. 10 v_counter INTEGER := 0; 11 BEGIN 12 FOR name_rec IN name_cur 13 LOOP 14 v_counter := 15 v_counter 16 + 1; 17 dname_tab.EXTEND; -->需要擴展，否則將收到 ORA-06533: Subscript beyond count 18 dname_tab( v_counter ) := name_rec.dname; 19 DBMS_OUTPUT.put_line( 'Dname (' 20 || v_counter 21 || ') is :' 22 || dname_tab( v_counter ) ); 23 END LOOP; 24 END; 25 / Dname (1) is :ACCOUNTING Dname (2) is :RESEARCH Dname (3) is :SALES PL/SQL procedure successfully completed. --將嵌套表作為表列的數(shù)據(jù)類型來使用 scott@CNMMBO> CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 ); 2 / Type created. scott@CNMMBO> CREATE TABLE tb_tmp -->創(chuàng)建表tb_tmp 2 ( 3 empno NUMBER( 4 ) 4 , ename VARCHAR2( 10 ) 5 , mail mail_type -->列mail的類型為mail_type 6 ) 7 NESTED TABLE mail -->注意此處需要指定嵌套表的存儲方式 8 STORE AS mail_tab; Table created. --為嵌套表插入數(shù)據(jù) scott@CNMMBO> insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual; -->傳統(tǒng)方式插入失敗 insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got CHAR scott@CNMMBO> insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual; -->分割字符串方式插入失敗 insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got CHAR scott@CNMMBO> insert into tb_tmp select 8888,'Jack',mail_type('Jack@yahoo.com','Jack@163.com') from dual; -->插入時指定嵌套表類型 1 row created. scott@CNMMBO> commit; Commit complete. --查看存在嵌套表數(shù)據(jù)類型的表中的記錄 scott@CNMMBO> col mail format a30 scott@CNMMBO> select * from tb_tmp; -->查詢數(shù)據(jù)時帶有嵌套表的類型 EMPNO ENAME MAIL ---------- ---------- ------------------------------ 8888 Jack MAIL_TYPE('Jack@yahoo.com', 'J ack@163.com') scott@CNMMBO> DECLARE 2 mail_tab mail_type; -->聲明一個mail_type數(shù)據(jù)類型 3 BEGIN 4 SELECT mail 5 INTO mail_tab -->將數(shù)據(jù)保存到 mail_tab 變量中 6 FROM tb_tmp 7 WHERE empno = 8888; 8 9 FOR i IN 1 .. mail_tab.COUNT -->輪循輸出嵌套表類型中的值 10 LOOP 11 DBMS_OUTPUT.put_line( 'Jack mail address is ' 12 || mail_tab( i ) ); 13 END LOOP; 14 END; 15 / Jack mail address is Jack@yahoo.com Jack mail address is Jack@163.com PL/SQL procedure successfully completed. --更新表中含有嵌套表類型中的值 scott@CNMMBO> DECLARE 2 mail_tab mail_type := mail_type( 'Jackson@yahoo.com', 'Jackson@163.com' ); 3 BEGIN 4 UPDATE tb_tmp 5 SET mail = mail_tab; 6 7 COMMIT; 8 DBMS_OUTPUT.put_line( 'Jack mail address was updated' ); 9 END; 10 / Jack mail address was updated PL/SQL procedure successfully completed. scott@CNMMBO> select * from tb_tmp; EMPNO ENAME MAIL ---------- ---------- ------------------------------ 8888 Jack MAIL_TYPE('Jackson@yahoo.com', 'Jackson@163.com') --刪除表中存在嵌套表類型的記錄 scott@CNMMBO> delete from tb_tmp where empno=8888; 1 row deleted. scott@CNMMBO> commit; Commit complete. scott@CNMMBO> select * from tb_tmp; no rows selected --> Author: Robinson Cheng --> Blog: http://blog.csdn.net/robinson_0612

三、總結(jié)
1、不論是聯(lián)合數(shù)組還是嵌套表，應(yīng)當(dāng)以集合的方式來看待與處理
2、聯(lián)合數(shù)組在聲明其類型時需要指定index by子句，而嵌套表則不需要
3、聯(lián)合數(shù)組和嵌套表兩者元素個數(shù)無限制???
4、聯(lián)合數(shù)組不需要初始化，而嵌套表則需要對其進行初始化。其次是嵌套表初始化賦空值的情形則后續(xù)需要使用extend方式來擴展集合尺寸的大小
5、聯(lián)合數(shù)組不能作為表上列的數(shù)據(jù)類型，而嵌套表無此限制

?

四、更多參考

啟用用戶進程跟蹤

父游標(biāo)、子游標(biāo)及共享游標(biāo)

綁定變量及其優(yōu)缺點

dbms_xplan之display_cursor函數(shù)的使用

dbms_xplan之display函數(shù)的使用

執(zhí)行計劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執(zhí)行計劃

啟用 AUTOTRACE 功能

函數(shù)使得索引列失效

Oracle 綁定變量窺探

?

?????????????????

?

PL/SQL 聯(lián)合數(shù)組與嵌套表