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

?

一、聯合數組

1、聯合數組的特性 類似于一張簡單的SQL表，按照主鍵進行檢索數據 其數據行并不是按照預定義的順序存儲。當使用變量來檢索其數據時，每行數據會分配一個連續的下標且從1開始。 下標可以為負值，且下標的數據類型支持binary_integer,pls_integer,varchar2 其數據元素個數無限制 不能作為表列的數據類型使用，即只能作為PL/SQL復合數據類型使用 存放的數據類型是臨時數據，故不支持insert,select into 等SQL語句,等同于SQL server中的表變量 2、語法 TYPE type_name IS TABLE OF element_type [NOT NULL] -->element_type為聯合數組元數據指定數據類型(先使用TYPE聲明表結構) INDEX BY key_type; -->元素下標的數據類型 table_name TYPE_NAME; -->再使用聲明的TYPE類型聲明實際數組名 3、示例 --使用PLS_INTEGE類型聯合數組 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 -->定義一個聯合數組類型，且其下標的數據類型為PLS_INTEGER 8 INDEX BY PLS_INTEGER; 9 10 dname_tab name_type; -->聲明類型為name_type的聯合數組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; -->對聯合數據進行循環賦值 19 DBMS_OUTPUT.put_line( 'Dname is(' 20 || v_counter 21 || '):' 22 || dname_tab( v_counter ) ); -->輸出聯合數組中的所有元素 23 END LOOP; 24 END; 25 / Dname is(1):ACCOUNTING Dname is(2):RESEARCH Dname is(3):SALES PL/SQL procedure successfully completed. --使用VARCHAR2類型聯合數組 scott@CNMMBO> DECLARE 2 TYPE score_type IS TABLE OF NUMBER 3 INDEX BY VARCHAR2( 10 ); -->注意此處聲明的聯合數組的下標數據類型為varchar2 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( 'First element is ' 11 || score_tab.FIRST ); -->輸出聯合數組score_tab中的第一個元素的下標 12 DBMS_OUTPUT.put_line( 'last element is ' 13 || score_tab.LAST ); -->輸出聯合數組score_tab中的最后一個元素的下標 14 DBMS_OUTPUT.put_line( 'The score of Scott is ' 15 || score_tab( 'SCOTT' ) ); -->輸出下標為SCOTT的元素的值 16 END; 17 / First element is JOHN -->注意此處輸出的是JOHN(第一個和最后一個是按字母排序得到的) last element is SCOTT The score of Scott is 95 PL/SQL procedure successfully completed. --下標超范圍的情況 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 -->當下標超范圍時，將收到ORA-01403錯誤 ORA-06512: at line 10

二、嵌套表

1、嵌套表的特點： 元素下表從1開始，個數沒有限制.即元素個數可以動態增長 嵌套表的數組元素值可以是稀疏的，即可以使得中間的某個元素沒有賦值 嵌套表的語法與聯合數組類似，不同的是僅僅是少了index by子句 嵌套表必需先初始化，然后才能引用其中的元素。如果初始化為空值，則后續需要使用extend來擴展其大小 嵌套表初始化時為密集的，但允許有間隙，即允許使用內置過程delete從嵌套表中刪除元素 嵌套表類型可以作為表列的數據類型來使用 2、語法 TYPE type_name IS TABLE OF element_type [NOT NULL]; -->element_type為索引表元數據指定數據類型(先使用TYPE聲明表結構) table_name TYPE_NAME; -->再使用聲明的TYPE類型聲明實際嵌套表 3、示例 --聲明嵌套表并輸出嵌套表的實際內容 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. --將嵌套表作為表列的數據類型來使用 scott@CNMMBO> CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 ); 2 / Type created. scott@CNMMBO> CREATE TABLE tb_tmp -->創建表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. --為嵌套表插入數據 scott@CNMMBO> insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual; -->傳統方式插入失敗 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. --查看存在嵌套表數據類型的表中的記錄 scott@CNMMBO> col mail format a30 scott@CNMMBO> select * from tb_tmp; -->查詢數據時帶有嵌套表的類型 EMPNO ENAME MAIL ---------- ---------- ------------------------------ 8888 Jack MAIL_TYPE('Jack@yahoo.com', 'J ack@163.com') scott@CNMMBO> DECLARE 2 mail_tab mail_type; -->聲明一個mail_type數據類型 3 BEGIN 4 SELECT mail 5 INTO mail_tab -->將數據保存到 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

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

?

四、更多參考

啟用用戶進程跟蹤

父游標、子游標及共享游標

綁定變量及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執行計劃

啟用 AUTOTRACE 功能

函數使得索引列失效

Oracle 綁定變量窺探

?

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

?

PL/SQL 聯合數組與嵌套表