聯(lián)合數(shù)組以前被稱為PL/SQL表。在表中不能使用聯(lián)合數(shù)組，只能將它們用作程序設(shè)計(jì)的結(jié)構(gòu)體。只能在PL/SQL中訪問聯(lián)合數(shù)組。
注意到聯(lián)合數(shù)組帶來的一些關(guān)鍵問題是非常重要的。這些問題使我們介紹它們的用法時(shí)，需要采取一些特別的方法。這些問題包括：
聯(lián)合數(shù)組不需要初始化，也沒有構(gòu)造函數(shù)語法。在對(duì)它們進(jìn)行賦值以前，也不需要專門為其分配存儲(chǔ)空間，也就不需要使用集合API的EXTEND方法。
在ORACLE 10G中，以及在ORACLE 10G以前的版本中，都可以使用數(shù)字索引聯(lián)合數(shù)組。另外，在ORACLE 10G中，還可以使用具有唯一性的變長字符串作為聯(lián)合數(shù)組的索引。
可以使用任意的整數(shù)作為聯(lián)合數(shù)組的索引，這就說明聯(lián)合數(shù)組的索引可以是任意正數(shù)、負(fù)數(shù)或0。
可以顯式地將等價(jià)的%ROWTYPE、記錄類型和對(duì)象類型的返回值，轉(zhuǎn)換成聯(lián)合數(shù)組的結(jié)構(gòu)體。
聯(lián)合數(shù)組是使用FORALL語句或BULK COLLECT子句的關(guān)鍵，而后者則允許數(shù)據(jù)庫到程序設(shè)計(jì)單元的批轉(zhuǎn)換。
在使用了全球化設(shè)置，例如NLS_COMP或NLS_SORT初始化參數(shù)的數(shù)據(jù)庫中，將字符串用作聯(lián)合數(shù)組索引的時(shí)候，需要我們進(jìn)行特殊的處理。
1、定義聯(lián)合數(shù)組和用作PL/SQL的程序結(jié)構(gòu)體
在PL/SQL語言中定義聯(lián)合數(shù)組的語法有兩種，一種是：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];
可以將正數(shù)、負(fù)數(shù)或者0值用作聯(lián)合數(shù)組的索引。ORACLE 10G中的PLS_INTEGER何BINARY_INTEGER類型都是不受限制的數(shù)據(jù)類型，這兩個(gè)數(shù)據(jù)類型都映射到C/C++、C#和JAVA的調(diào)用規(guī)范中。
變長字符串的最大長度為4000個(gè)字符。
另一種定義聯(lián)合數(shù)組的語法是：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY key_type;
其中的key_type允許我們使用VARCHAR2、STRING或LONG類型。使用VARCHAR2和STRING時(shí)，都需要定義大小。使用LONG類型時(shí)，則不需要定義大小，因?yàn)樗峭ㄟ^定義VARCHAR（32760）進(jìn)行定義的。
聯(lián)合數(shù)組不需要進(jìn)行初始化，也沒有構(gòu)造函數(shù)語法。這是與其他兩種集合類型（VARRAYS和嵌套表）有著本質(zhì)區(qū)別的地方。
如果你像下面這樣構(gòu)造一個(gè)聯(lián)合數(shù)組，那么會(huì)引發(fā)PLS-00222異常。
復(fù)制代碼 代碼如下:
-- Define an associative array of strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR)
INDEX BY BINARY_INTEGER;
-- and attempt to construct an associative array.
cards CARD_TABLE := card_table('A','B','C');
BEGIN
NULL;
END;
在前面的介紹中，我們知道對(duì)象的構(gòu)造函數(shù)是完全可以作為一個(gè)函數(shù)使用的。其他集合類型，例如VARRAYS和嵌套表，都是顯式定義構(gòu)造函數(shù)的對(duì)象類型。而聯(lián)合數(shù)組只是一個(gè)結(jié)構(gòu)體，不是一個(gè)對(duì)象類型。因此，它不能顯式地創(chuàng)建構(gòu)造函數(shù)，也無法調(diào)用構(gòu)造函數(shù)。
2、聯(lián)合數(shù)組的初始化
前面已經(jīng)說過，我們可以將數(shù)字或者具有唯一性的變長字符串作為索引，構(gòu)造聯(lián)合數(shù)組。數(shù)字索引比如為整數(shù)，可以為正整數(shù)、負(fù)整數(shù)和0值。唯一性的變長字符串可以是VARCHAR2、STRING或LONG數(shù)據(jù)類型。
1）以數(shù)字作為聯(lián)合數(shù)組索引
下面的例子給出了一個(gè)向以數(shù)字為索引的聯(lián)合數(shù)組中的元素賦值的過程，該示例示范了將VARRAY的內(nèi)容轉(zhuǎn)移到聯(lián)合數(shù)組的過程。
復(fù)制代碼 代碼如下:
-- Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY BINARY_INTEGER;
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Initialize a null associative array element.
calendar(i) := '';
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(i) := month(i);
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;
/
在第一個(gè)FOR-LOOP循環(huán)中，用等于VARRAY類型的month索引的一個(gè)索引值，為聯(lián)合數(shù)組類型的calendar變量賦上一個(gè)空值。這是為聯(lián)合數(shù)組分配空間的唯一方法。
2）以唯一字符串作為聯(lián)合數(shù)組索引
如下例所示：
復(fù)制代碼 代碼如下:
-- Define a varray of twelve variable length strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of variable length strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY VARCHAR2(9 CHAR);
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(month(i)) := ''; --i;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||month(i)||'] is ['||i||']');
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;
運(yùn)行上面這段代碼會(huì)出現(xiàn)錯(cuò)誤。ORA-06502：PL/SQL：numeric or value error：character to number convertion error。在第一個(gè)FOR-LOOP中的初始化是沒有任何問題的。可是在第二個(gè)FOR-LOOP循環(huán)中，程序試圖向計(jì)數(shù)器變量傳遞一個(gè)非數(shù)字的值。在上面的程序中，這個(gè)計(jì)數(shù)器變量是i。計(jì)數(shù)器變量的數(shù)據(jù)類型被定義為PLS_INTEGER類型。所以，就不能將整個(gè)變長字符串的索引值賦給一個(gè)整型變量—因?yàn)樽冮L字符串不是整數(shù)。這樣，自然就引發(fā)了類型轉(zhuǎn)換錯(cuò)誤ORA-06502。該示例之所以會(huì)引發(fā)錯(cuò)誤，是因?yàn)樵诔跏蓟?lián)合數(shù)組成員的時(shí)候，其中的計(jì)數(shù)器變量被轉(zhuǎn)換為VARCHAR2類型，而在讀聯(lián)合數(shù)組的時(shí)候，又將該計(jì)數(shù)器類型轉(zhuǎn)為INTEGER類型。
這其實(shí)給我們提出了一個(gè)新問題。非數(shù)字索引值需要我們明確的知道索引的開始值以及索引的遞增方法。集合API的FIRST何NEXT方法提供了這種工具。
如下例所示：
復(fù)制代碼 代碼如下:
-- Define variables to traverse an associative array that
-- uses variable length strings for index values.
current VARCHAR2(9 CHAR);
element INTEGER;
-- Define a varray of twelve variable length strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of variable length strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY VARCHAR2(9 CHAR);
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(month(i)) := TO_CHAR(i);
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||month(i)||'] is ['||i||']');
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN 1..calendar.COUNT LOOP
-- Check if the first element in the loop.
IF i = 1 THEN
-- Assign the first character index to a variable.
current := calendar.FIRST;
-- Use the derived index to find the next index.
element := calendar(current);
ELSE
-- Check if next index value exists.
IF calendar.NEXT(current) IS NOT NULL THEN
-- Assign the character index to a variable.
current := calendar.NEXT(current);
-- Use the derived index to find the next index.
element := calendar(current);
ELSE
-- Exit loop since last index value is read.
EXIT;
END IF;
END IF;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||current||'] is ['||element||']');
END LOOP;
END IF;
END;
3、與BULK COLLECT和FORALL結(jié)合使用聯(lián)合數(shù)組
使用BULK COLLECT和FORALL胃我們打開了消除行級(jí)處理之門。使用BULK COLLECT可以獲取存儲(chǔ)在聯(lián)合數(shù)組或嵌套表中的記錄集。使用FORALL可以成批的發(fā)送DML語句。FORALL可以插入、更新和刪除數(shù)據(jù)。這些方法減少了PL/SQL引擎和SQL引擎之間來回切換上下文環(huán)境的次數(shù)。如果沒有這些方法，就會(huì)有太多的解析或取值過程。
你應(yīng)該還記得行級(jí)處理實(shí)際上使用的是%ROWTYPE和%TYPE。前者可以直接映射到記錄類型上。BULK COLLECT可以將%ROWTYPE或%TYPE類型的值的一個(gè)集合作為聯(lián)合數(shù)組或嵌套表的一個(gè)集合進(jìn)行賦值。FORALL提供了一種可以將聯(lián)合數(shù)組或嵌套表中的內(nèi)容轉(zhuǎn)移到數(shù)據(jù)庫對(duì)象的方法。
聯(lián)合數(shù)組和嵌套表集合類型可以與BULK COLLECT和FORALL結(jié)合使用。使用嵌套表時(shí)，需要將嵌套表構(gòu)造為空元素的集合。BULK COLLECT會(huì)顯式地分配嵌套表的存儲(chǔ)空間。不需要對(duì)聯(lián)合數(shù)組進(jìn)行構(gòu)造，只要一個(gè)批賦值就可以了。同樣，聯(lián)合數(shù)組和嵌套表都可以作為SQL命令FORALL的源結(jié)構(gòu)。
如下示例所示：
復(fù)制代碼 代碼如下:
-- Create a table for the example.
CREATE TABLE bulk_numbers
(number_id NUMBER NOT NULL
,CONSTRAINT number_id_pk PRIMARY KEY (number_id));
-- Define an associative array of integers.
TYPE number_table IS TABLE OF bulk_numbers.number_id%TYPE
INDEX BY BINARY_INTEGER;
-- Define a variable of the associative array type.
number_list NUMBER_TABLE;
BEGIN
-- Loop from 1 to a million and increment associative array.
FOR i IN 1..10000 LOOP
-- Assign number value.
number_list(i) := i;
END LOOP;
-- Loop through all to do a bulk insert.
FORALL i IN 1..number_list.COUNT
INSERT
INTO bulk_numbers
VALUES (number_list(i));
-- Commit records.
COMMIT;
END;
-- Use a BULK COLLECT to retrieve a table into an
-- associative array.
-- Define an associative array of integers.
TYPE number_table IS TABLE OF bulk_numbers.number_id%TYPE
INDEX BY BINARY_INTEGER;
-- Define a variable of the associative array type.
number_list NUMBER_TABLE;
BEGIN
-- Check if calendar has no elements.
SELECT number_id
BULK COLLECT
INTO number_list
from bulk_numbers;
-- Print a title
DBMS_OUTPUT.PUT_LINE('Bulk Collected:');
DBMS_OUTPUT.PUT_LINE('---------------');
-- Loop through to print elements.
--只打印前兩條和最后兩條記錄
FOR i IN number_list.FIRST..number_list.LAST LOOP
-- Print only the first and last two.
IF i = 2 OR i >= 9999 THEN
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE('Number ['||number_list(i)||']');
END IF;
END LOOP;
END;
在BULK COLLECT子句中使用了ORDER BY，保證得出的結(jié)果是按照數(shù)字升序排列的。如果不對(duì)元素進(jìn)行排序，就會(huì)發(fā)現(xiàn)它們是按照隨機(jī)的順序獲取的，而不是按它們的數(shù)字順序進(jìn)行獲取的。
