2010-04-27

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

阿布洛格 Oracle不管習慣如何稱呼它,「集合(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。有興趣,以下參考資料可以去看看:

0 回應 :

張貼留言

讓阿布知道你對這篇文章的想法吧!