PL/SQL Table 集合陣列實作 (Oracle Array-like Programming with PL/SQL Table)

不管習慣如何稱呼它，「集合(Collection)」/「陣列(Array)」，廣義的定義就是一個群組內有著一群相同的類型(Type)的元素(Elements)，而程式可以方便地去存取集合裡的元素。阿布在【使用 Oracle Object Types 實作中介暫存 (Keep Temporary Data via Object Types)】一文中，提及的 Oracle Object Types 也是 PL/SQL 中用來實作集合陣列的方式之一。但是，有的時候也許只是想寫個小小的 PL/SQL 程式來達成短暫的目的，用上 Oracle Object Type 總有殺雞焉用牛刀的感覺，這篇就來介紹輕量級的 Oracle 集合陣列實作－PL/SQL Table。


PL/SQL Table 是用 IS TABLE OF 所宣告出來的，語法像這樣：

TYPE type_name IS TABLE OF datatype INDEX BY BINARY_INTEGER;

其中 datatype 可以是以下幾種類型：

• 純量型別(Scalar Datatype)，像是 NUMBER，VARCHAR2，BOOLEAN，DATE等。
  
• 某個資料表的%ROWTYPE。
  
• 由特定 Cursor 定義的%ROWTYPE。
  
• 自訂的 Record Type。

較為詳細的 PL/SQL Table 的介紹，可以參考這篇【PL/SQL Table Enhancements in PL/SQL Release 2.3】。 以下，直接切入用 PL/SQL Table 來實作 Array-Like 的範例。 純量型別陣列(Scalar Datatype Array) 其實阿布也不知道這種用法有沒有更專業的講法，通常使用於一批 Scalar Datatype 的同型資料，然後要一個一個做處理，比方說當成參數餵給 stored procedure 執行。

DECLARE
-- 宣告 NUMBER 的 Array TYPE 及變數
TYPE t_number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
number_array          t_number_array;

-- 宣告 VARCHAR2 的 Array TYPE 及變數
TYPE t_varchar2_array IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
string_array          t_varchar2_array;
BEGIN
-- 塞值進 Number Array 
number_array(1) := 123;
number_array(2) := 456;
number_array(3) := 789;
-- 迴圈處理
FOR i IN 1..number_array.COUNT LOOP
DBMS_OUTPUT.put_line( 'array(' || TO_CHAR(i) || ') = ' || TO_CHAR(number_array(i)));
END LOOP;
/**
輸出結果為:
array(1) = 123
array(2) = 456
array(3) = 789
*/

-- 塞值進 String Array  
string_array(1) := 'abc';
string_array(2) := 'def';
string_array(3) := 'ghi';
-- 迴圈處理
FOR i IN 1..string_array.COUNT LOOP
DBMS_OUTPUT.put_line( 'array(' || TO_CHAR(i) || ') = ' || TO_CHAR(string_array(i)));
END LOOP;
/**
輸出結果為:
array(1) = abc
array(2) = def
array(3) = ghi
*/
END;

簡單好用～ 記錄型別陣列(Record Type Array) 當要處理的資料，比較複雜，但是它們都有多個共通的資料項目，且成組存在，比方說，一堆手機，它們就都會有各自的廠牌，型號，描述等等，就可以把一支ㄧ支手機資料轉化為一筆一筆 Record 再做處理。

DECLARE
-- 宣告 Record, Type 及變數 
TYPE r_handset IS RECORD ( brand          VARCHAR2(10),
model_name     VARCHAR2(20),
price          NUMBER);
TYPE t_handset IS TABLE OF r_handset INDEX BY PLS_INTEGER;

handsets              t_handset;
BEGIN
-- 塞值進 Record Array
handsets(1).brand      := 'HTC';
handsets(1).model_name := 'Tattoo';
handsets(1).price      := 6000;
handsets(2).brand      := 'Apple';
handsets(2).model_name := 'iPhone';
handsets(2).price      := 27000;
handsets(3).brand      := 'Nokia';
handsets(3).model_name := 'N82';
handsets(3).price      := 15000;

FOR i IN 1..handsets.COUNT LOOP
DBMS_OUTPUT.put_line('第 ' || TO_CHAR(i) || ' 筆 - ');
DBMS_OUTPUT.put_line('廠牌 : ' || handsets(i).brand);
DBMS_OUTPUT.put_line('名稱 : ' || handsets(i).model_name);
DBMS_OUTPUT.put_line('價格 : ' || TO_CHAR(handsets(i).price));
DBMS_OUTPUT.put_line(' ');
END LOOP;

/**
輸出結果為:
第 1 筆 - 
廠牌 : HTC
名稱 : Tattoo
價格 : 6000

第 2 筆 - 
廠牌 : Apple
名稱 : iPhone
價格 : 27000

第 3 筆 - 
廠牌 : Nokia
名稱 : N82
價格 : 15000
*/
END;

基本上，這兩種都屬於比較傳統的用法，但是十分直觀，且至少有 Array-Like 的味道，很夠用了。雖然資料庫本身就很有集合的味道，可是如何在 PL/SQL 中像其他程式語言般使用集合，使用陣列，似乎又不那麼直接。不過，隨著時間的演進，在 PL/SQL 中，集合陣列也有了一些實作的方式，像是 Nested Tables，Varrays，以及 Associative Arrays。有興趣，以下參考資料可以去看看：

• Oracle PL/SQL User's Guide and Reference 9.2: PL/SQL Collections and Records
  
• Oracle PL/SQL User's Guide and Reference 10.1: PL/SQL Collections and Records
  
• Oracle Programming with PL/SQL Collections