Oracle定義聯合數組及提示

聯合數組以前被稱為PL/SQL表。在表中不能使用聯合數組，只能將它們用作程式設計的結構體。只能在PL/SQL中訪問聯合數組。

注意到聯合數組帶來的一些關鍵問題是非常重要的。這些問題使我們介紹它們的用法時，需要採取一些特別的方法。這些問題包括：

聯合數組不需要初始化，也沒有建構函式文法。在對它們進行賦值以前，也不需要專門為其分配儲存空間，也就不需要使用集合API的EXTEND方法。

在ORACLE 10G中，以及在ORACLE 10G以前的版本中，都可以使用數字索引聯合數組。另外，在ORACLE 10G中，還可以使用具有唯一性的變長字串作為聯合數組的索引。

可以使用任意的整數作為聯合數組的索引，這就說明聯合數組的索引可以是任意正數、負數或0。
可以顯式地將等價的%ROWTYPE、記錄類型和物件類型的傳回值，轉換成聯合數組的結構體。

聯合數組是使用FORALL語句或BULK COLLECT子句的關鍵，而後者則允許資料庫到程式設計單元的批轉換。
在使用了全球化設定，例如NLS_COMP或NLS_SORT初始化參數的資料庫中，將字串用作聯合數組索引的時候，需要我們進行特殊的處理。

1、定義聯合數組和用作PL/SQL的程式結構體
在PL/SQL語言中定義聯合數組的文法有兩種，一種是：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];
可以將正數、負數或者0值用作聯合數組的索引。ORACLE 10G中的PLS_INTEGER何BINARY_INTEGER類型都是不受限制的資料類型，這兩個資料類型都映射到C/C++、C#和JAVA的調用規範中。
變長字串的最大長度為4000個字元。
另一種定義聯合數組的文法是：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY key_type;
其中的key_type允許我們使用VARCHAR2、STRING或LONG類型。使用VARCHAR2和STRING時，都需要定義大小。使用LONG類型時，則不需要定義大小，因為它是通過定義VARCHAR（32760）進行定義的。
聯合數組不需要進行初始化，也沒有建構函式文法。這是與其他兩種集合類型（VARRAYS和巢狀表格）有著本質區別的地方。
如果你像下面這樣構造一個聯合數組，那麼會引發PLS-00222異常。 複製代碼 代碼如下:-- 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;

在前面的介紹中，我們知道對象的建構函式是完全可以作為一個函數使用的。其他集合類型，例如VARRAYS和巢狀表格，都是顯式定義建構函式的物件類型。而聯合數組只是一個結構體，不是一個物件類型。因此，它不能顯式地建立建構函式，也無法調用建構函式。
2、聯合數組的初始化
前面已經說過，我們可以將數字或者具有唯一性的變長字串作為索引，構造聯合數組。數字索引比如為整數，可以為正整數、負整數和0值。唯一性的變長字串可以是VARCHAR2、STRING或LONG資料類型。
1）以數字作為聯合數組索引
下面的例子給出了一個向以數字為索引的聯合數組中的元素賦值的過程，該樣本示範了將VARRAY的內容轉移到聯合數組的過程。 複製代碼 代碼如下:-- 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;
/

在第一個FOR-LOOP迴圈中，用等於VARRAY類型的month索引的一個索引值，為聯合數群組類型的calendar變數賦上一個空值。這是為聯合數組分配空間的唯一方法。
2）以唯一字串作為聯合數組索引
如下例所示： 複製代碼 代碼如下:-- 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;

運行上面這段代碼會出現錯誤。ORA-06502：PL/SQL：numeric or value error：character to number convertion error。在第一個FOR-LOOP中的初始化是沒有任何問題的。可是在第二個FOR-LOOP迴圈中，程式試圖向計數器變數傳遞一個非數位值。在上面的程式中，這個計數器變數是i。計數器變數的資料類型被定義為PLS_INTEGER類型。所以，就不能將整個變長字串的索引值賦給一個整型變數—因為變長字串不是整數。這樣，自然就引發了類型轉換錯誤ORA-06502。該樣本之所以會引發錯誤，是因為在初始化聯合數群組成員的時候，其中的計數器變數被轉換為VARCHAR2類型，而在讀聯合數組的時候，又將該計數器類型轉為INTEGER類型。
這其實給我們提出了一個新問題。非數字索引值需要我們明確的知道索引的開始值以及索引的遞增方法。集合API的FIRST何NEXT方法提供了這種工具。
如下例所示： 複製代碼 代碼如下:-- 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結合使用聯合數組
使用BULK COLLECT和FORALL胃我們開啟了消除行級處理之門。使用BULK COLLECT可以擷取儲存在聯合數組或巢狀表格中的記錄集。使用FORALL可以成批的發送DML語句。FORALL可以插入、更新和刪除資料。這些方法減少了PL/SQL引擎和SQL引擎之間來回切換上下文環境的次數。如果沒有這些方法，就會有太多的解析或取值過程。
你應該還記得行級處理實際上使用的是%ROWTYPE和%TYPE。前者可以直接映射到記錄類型上。BULK COLLECT可以將%ROWTYPE或%TYPE類型的值的一個集合作為聯合數組或巢狀表格的一個集合進行賦值。FORALL提供了一種可以將聯合數組或巢狀表格中的內容轉移到資料庫物件的方法。
聯合數組和巢狀表格集合類型可以與BULK COLLECT和FORALL結合使用。使用巢狀表格時，需要將巢狀表格構造為空白元素的集合。BULK COLLECT會顯式地分配巢狀表格的儲存空間。不需要對聯合數組進行構造，只要一個批賦值就可以了。同樣，聯合數組和巢狀表格都可以作為SQL命令FORALL的源結構。
如下樣本所示： 複製代碼 代碼如下:-- 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，保證得出的結果是按照數字升序排列的。如果不對元素進行排序，就會發現它們是按照隨機的順序擷取的，而不是按它們的數字順序進行擷取的。