Oracle PLSQL的集合類型

轉載：http://www.cnblogs.com/gkl0818/archive/2009/02/25/1397769.html
                               Oracle collections
一、Types of Collections
    1、Associative arrays 數組
      它是同種類型的一維、無邊界的稀疏集合，只能用於 PL/SQL。
      DECLARE
        TYPE t_name IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER; --聲明Collection類型
          i_name t_name;      --聲明instance
          l_row PLS_INTEGER;
      BEGIN
              i_name(202020):='aaa';
              i_name(-125):='bbb';
              i_name(88):='ccc';              --賦值，row number 可以為任何整數，且可以跳躍（稀疏的），也不用按照順序賦值，其內部最終按照 row number 排序
              l_row := i_name.FIRST;          --返回第一個 row number
              l_row := i_name.NEXT (l_row);   --返回 l_row 之後的一個 row number，會自動跳過為空白的行
              DBMS_OUTPUT.put_line (i_name (l_row)); --返回指定行的值
      END;
 
    2、Nested tables 巢狀表格
      也是同種類型的一維無邊界集合。起初它是密集的，經過刪除操作會變成稀疏。它可以在 PL/SQL 和 Database（把某一列定義為一個巢狀表格）中使用。它是 multisets 的，也就是說 nested table 中的元素沒有內在的順序.
      DECLARE
              TYPE t_name IS TABLE OF varchar2(10);
              --建立 Collection，和 Associative arrays 相差一個 INDEX BY
              i_n1 t_name := t_name(); --建立 instance，需要使用建構函式
              i_n2 t_name := t_name();
              i_n3 t_name := t_name();
      BEGIN
              i_n1.EXTEND(2); --賦值前要分配空間
              i_n1(1):='aaa'; --賦值，row number 最小為 1，最大為 EXTEND 分配的空間數，可以跳過某個行不分配則為 null
              i_n1(2):='bbb';
              i_n2.EXTEND;     --分配一個空間
              i_n2(1):='bbb';
              i_n3 := i_n1 MULTISET EXCEPT i_n2;
              --10g 開始提供的功能，將 i_n1 中除去 i_n2 的值，賦值給 i_n3
              FOR l_row IN i_n1.FIRST .. i_n1.LAST --從行首到行尾，依次輸出
              LOOP
                 DBMS_OUTPUT.put_line (i_n1(l_row));
              END LOOP;
      END;

    3、VARRAYs 變數數組
      VARRAYs (variable-sized arrays)，也是同種類型的一維集合。但它是有界且不稀疏的。在定義 VARRAYs 時要指定它的最大範圍。VARRAYs 也可以在 PL/SQL 和 Database 中被定義，但它其中的元素是有順序的。
      DECLARE TYPE t_name IS VARRAY (2) OF VARCHAR2 (10); --建立 Collection，需要指定最大範圍
              i_n1 t_name := t_name(); --建立 instance，需要使用建構函式
      BEGIN
              i_n1.EXTEND(2); --賦值前要分配空間，不能超過最大範圍
              i_n1(1):='aaa'; --賦值，row number 最小為 1，最大為 EXTEND 分配的空間數
              i_n1(2):='bbb';
      END;
   
二、Where You Can Use Collections
    1、作為 record 的成員.
    2、作為程式的參數
      由於 Oracle 沒有預定的集合類型，當作為參數使用前，需要定義集合類型：
        1.用 CREATE TYPE 定義 schema-level 類型
        2.在 package specification 中聲明
        3.在外層範圍定義
    3、作為函數的傳回值
      1.返回集合直接賦值給 collection variable，這時這個 collection variable 不需要初始化
      2.將返回集合中的一個元素賦值給一個類型相容的變數
          variable_of_element_type := function() (subscript);
          如果函數返回空值，在賦值時會產生 COLLECTION_IS_NULL 異常，應該捕獲並適當處理該異常
    4、作為資料庫表的列
      當使用 nested table datatype 作為列時，必須指定 store table 的名字：
        CREATE TABLE personality_inventory (
           person_id NUMBER,
           favorite_colors Color_tab_t,
           date_tested DATE,
           test_results BLOB)
        NESTED TABLE favorite_colors STORE AS favorite_colors_st;
      不能對 store table 進行維護或者試圖直接查詢、儲存資料，只能通過 outer table 來擷取它的屬性。也不能指定它的 storage parameters，它繼承與 outermost table。
      nested tables 和 VARRAYs 的區別在於，VARRAY 和其它資料一起存於表內，而 nested table 存於表外，VARRAY 適合 "small" arrays，nested table 適合 "large" arrays。
    5、作為 object type 的屬性
 
三、Choosing a Collection Type
    1.如果要用稀疏 array，那麼只能使用 associative array，雖然可以先分配 nested table 在刪除其中的項目，但效率很低
    2.如果在 PL/SQL 中要用負數的下標，只能用 associative array
    3.如果使用 10g，希望使用 set 層面的操作，那麼選擇 nested tables + MULTISET EXCEPT 的方法
    4.如果要限制儲存的行數，使用 VARRAYs
    5.如果要在 column 中儲存大資料量集合，那麼使用 nested table，Oracle可以使用單獨的表來儲存它
    6.如果你想將存在 collection column 中的資料保持原有順序，並且資料量很小，可以使用 VARRAY，小的概念可以按照 BLOCK 的大小來判斷，如果資料量超過一個 BLOCK，將會產生行串連
    7.還有些情況適合使用 VARRAY：you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.

Collection Methods (Built-Ins)
Collection methods 只能用於 PL/SQL，不能在 SQL 中使用
1、The COUNT Method
    用於計算 associative array, nested table, or VARRAY 中元素的個數，使用 DELETEd or TRIMmed 將減少 COUNT
    定義：
      FUNCTION COUNT RETURN PLS_INTEGER;
    對於初始化了，但不包含元素的 collection 返回 0，對於空的 associative array 也返回 0
    如果對未初始化的 nested table or a VARRAY 使用 COUNT 將返回 COLLECTION_IS_NULL 異常，associative arrays 無需初始化，故不會返回異常

2、The DELETE Method
    DELETE 用於刪除 collection 中的一個、多個或者全部元素：
      1.不加參數，刪除所有元素，對於 VARRAYs 只能使用這種方法，因為 VARRAYs 不能使稀疏的，要刪除一行，只能用 TRIM 刪除最後一行
      2.DELETE(i) 刪除第 i 行
      3.DELETE(i,j) 刪除第 i~j 行
    對於使用參數的 DELETE，其實使用一個預留位置來代替刪除的元素，以後還可以為它們再賦值，賦值之後在 COUNT 中又開始計數。從物理角度來考慮，PL/SQL 只有在刪除足夠多數量的元素，以至於可以釋放 entire page of memory 時才釋放記憶體，但不加參數的 DELETE 是立即釋放所有記憶體的
    定義：
      PROCEDURE DELETE;
      PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)]);
      PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)],
                        j [BINARY_INTEGER | VARCHAR2(size_limit)]);
    如果 i,j 超出範圍，不會出現異常，只是刪除在範圍內的，超出部分忽略
    如果對未初始化的 nested table or a VARRAY 使用 DELETE 將返回 COLLECTION_IS_NULL 異常
3、The EXISTS Method
    判斷 collection 中的指定行是否存在，如果以前存在，後來被 DELETE 了，那也返回 false
    定義：
      FUNCTION EXISTS (i IN [BINARY_INTEGER | VARCHAR2(size_limit)]) RETURN BOOLEAN;   
4、The EXTEND Method
    用於為 nested table or VARRAY 分配空間
      1.不加參數，分配一個 null element
      2.EXTEND(n)，分配 n 個 null element
      3.EXTEND(n,i)，分配 n 個和第 i 值相同的 element，這對於有 NOT NULL 限制的 element 是必要的
    定義：
      PROCEDURE EXTEND (n PLS_INTEGER:=1);
      PROCEDURE EXTEND (n PLS_INTEGER, i PLS_INTEGER);
    如果用 DELETEd or TRIMmed 刪除了 collection 的最後一個元素，再 EXTEND 時，會跳過這個，在後面分配空間
    如果對未初始化的 nested table or a VARRAY 使用 DELETE 將返回 COLLECTION_IS_NULL 異常，如果 EXTEND 超過 VARRAY 的最大限制，將返回 SUBSCRIPT_BEYOND_LIMIT 異常

5、The FIRST and LAST Methods
    分別返回 COLLECTION 中可用的最低和最高下標，被 DELETE 的不會顯示
    定義：
      FUNCTION FIRST RETURN PLS_INTEGER;
      FUNCTION LAST RETURN PLS_INTEGER;
    對於初始化了，但沒有元素的 COLLECTION，都返回 null；對於至少有一個 element 的 VARRAYs，FIRST 總為 1，LAST 總等於 COUNT
    如果對未初始化的 nested table or a VARRAY 使用 FIRST and LAST 將返回 COLLECTION_IS_NULL 異常

6、The LIMIT Method
    返回 VARRAY 的最大數目限制，如果用於 nested tables or to associative arrays 將返回 NULL
    定義：
      FUNCTION LIMIT RETURN PLS_INTEGER;
    如果對未初始化的 nested table or a VARRAY 使用 LIMIT 將返回 COLLECTION_IS_NULL 異常
7、The PRIOR and NEXT Methods
    用於遍曆 COLLECTION 中的內容
    定義：
      FUNCTION PRIOR (i [BINARY_INTEGER | VARCHAR2(size_limit)])
          RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
      FUNCTION NEXT (i [BINARY_INTEGER | VARCHAR2(size_limit)])
          RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
    如果應用於初始化了但為空白的 COLLECTION 時，返回 null；如果 i 大於等於 COUNT，則 NEXT 返回 null；如果 i 小於等於 FIRST，則 PRIOR 返回 null
    就目前來說，如果 i 大於 COUNT，則 PRIOR 返回 LIMIT；如果 i 小於 FIRST，則 NEXT 返回 LIMIT，但以後的版本不知道是不是這樣了
    如果對未初始化的 nested table or a VARRAY 使用 PRIOR and NEXT 將返回 COLLECTION_IS_NULL 異常

8、The TRIM Method
    由於刪除 nested table or VARRAY 末尾的 n 行，如果不加參數，刪除最後一行，如果用於 associative array 將產生編譯時間錯誤
    DELETE and TRIM 聯合使用將產生重複刪除的問題，比如 DELETE 刪除最後一行之後，再用 TRIM 刪除最後 n 行時，其實 TRIM 重複操作了被 DELETE 刪除的那行（因為刪除之後變成了預留位置），使得實際上少刪除一行，因此 Oracle 建議兩者不要混合使用
    定義：
      PROCEDURE TRIM (n PLS_INTEGER:=1);
    如果 n 為 null，TRIM 不做任何事情
    如果對未初始化的 nested table or a VARRAY 使用 TRIM 將返回 COLLECTION_IS_NULL 異常
    如果 TRIM 的 n 大於實際存在的元素個數，那麼返回 SUBSCRIPT_BEYOND_COUNT 異常  
Working with Collections
一、Declaring Collection Types
    兩種聲明方法：
      1.在 PL/SQL 中使用 TYPE 語句，如果定義在 package 中，對於使用該類型的使用者要將 package 的 EXECUTE 許可權授予該使用者
      2.對於 nested table type or VARRAY type 在 schema-level 使用 CREATE TYPE 命令，並將該類型的 EXECUTE 許可權授予需要使用的使用者   
    Declaring an associative array collection type
      TYPE table_type_name IS TABLE OF datatype [ NOT NULL ]
         INDEX BY index_type;
      datatype 包括：
        1.Scalar datatype：VARCHAR2, CLOB, POSITIVE, DATE, or BOOLEAN 等等
        2.Anchored datatype：使用 %TYPE、%ROWTYPE
        3.Complex datatype：9i R2 開始可以使用 object types and collection types
      index_type 包括：
        INDEX BY BINARY_INTEGER;（9i R2 之前只能使用該類型）
        INDEX BY PLS_INTEGER;
        INDEX BY POSITIVE;
        INDEX BY NATURAL;
        INDEX BY SIGNTYPE;
        INDEX BY VARCHAR2(32767);
        INDEX BY table.column%TYPE;
        INDEX BY cursor.column%TYPE;
        INDEX BY package.variable%TYPE;
        INDEX BY package.subtype;
     
    Declaring a nested table or VARRAY
      CREATE [ OR REPLACE ] TYPE type_name AS | IS
         TABLE OF element_datatype [ NOT NULL ];
      CREATE [ OR REPLACE ] TYPE type_name AS | IS
         VARRAY (max_elements) OF element_datatype [ NOT NULL ];
      DROP TYPE type_name [ FORCE ];
      element_datatype 包括：大部分 scalar datatypes, an object type, or a REF object type，如果是 object type，那麼它的屬性不能是 collection 類型；在 PL/SQL 中，如果是 RECORD，那麼它只能包含 scalars or objects 類型
      FORCE：即使該 collection type 被其它類型引用，也 drop 它   
    Changing nested table or VARRAY characteristics
      1.改變 VARRAY 元素最大個數：
          ALTER TYPE list_t MODIFY LIMIT 100 INVALIDATE | CASCADE
      2.改變元素類型為 variable character, RAW, or numeric 的類型長度或精度
          CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);
          ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) INVALIDATE | CASCADE
      INVALIDATE：所有使用該類型的對象失效
      CASCADE：將改變傳遞給所有使用該類型的對象
   
二、Declaring and Initializing Collection Variables

    collection_name collection_type [:= collection_type (...)];
    對於 nested table or VARRAY 需要用建構函式初始化，建構函式可以可以接受用逗號分割的初始值作為參數，如果不初始化就使用會得到錯誤：ORA-06531: Reference to uninitialized collection
    1、Initializing implicitly during direct assignment
      同一 collection type （必須同一個類型，哪怕元素類型相同的不同 collection type 也不行）的不同變數，可以通過直接賦值進行隱式初始化
        DECLARE
           earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');
           wedding_colors Color_tab_t; --必須同一類型，都是 Color_tab_t，這裡沒有初始化
        BEGIN
           wedding_colors := earth_colors; --賦值時同時初始化
           wedding_colors(3) := 'CANVAS';
        END;    
    2、Initializing implicitly via FETCH
      如果表中的列是 Collection type，那麼可以像一般類型一樣通過 select ... into、FETCH 直接將值傳遞給相同類型的變數，並可以自動給該變數初始化
 
三、Populating Collections with Data
    Using the assignment operator
      1.單個賦值：
          countdown_test_list (43) := 'Internal pressure';
      2.整個 record （結構要一致）賦值：
          DECLARE
             TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
             l_emps emp_copy_t := emp_copy_t( );
             l_emprec emp%ROWTYPE;
          BEGIN
             l_emprec.ename := 'Steven';
             l_emprec.salary := 10000;
             l_emps.EXTEND
             l_emps (l_emps.LAST) := l_emprec;
          END;
        相同的還可以使用 select ... into、cursor FOR loop、select ... BULK COLLECT into（9i R2 之後，可將整個表賦值給 Collection，不需要初始化） 進行賦值
      3.相同類型的 Collection 直接賦值（不需要初始化）
   
四、Accessing Data Inside a Collection
    需要注意幾點：
      1.對於稀疏 Collection，當訪問不存在的元素時會產生 NO_DATA_FOUND 異常
      2.訪問超出 EXTEND 分配空間的元素時產生 ORA-06533: Subscript beyond count 異常
      3.對於 VARRAY，訪問大於最大元素個數的元素時產生 ORA-06532: Subscript outside of limit 異常
   
五、Collections of Complex Datatypes
    Collections of records
      可以定義元素類型為 record 的 collection，使用 %ROWTYPE or a programmer-defined record type。適用於 PL/SQL，如果是定義在表的列上，不能使用 %ROWTYPE  
    Multilevel collections
      9i R2 開始可以定義 collection 的元素類型也為 collection，既 collection 嵌套，被稱為 multilevel collections，通過這種方法，可以構造多維 collection。
   
六、Using String-Indexed Collections
    9i R2 開始可以使用 strings 作為 index type，FIRST, LAST, PRIOR, and NEXT 方法返回的也是 strings
 
七、Working with Collections in SQL
    Oracle8 開始提供 collection pseudo-functions，它們可以使資料表和 collection 的資料互相轉換，只能用於 SQL 陳述式中

    1.The CAST pseudo-function
        在 SQL 中，用於將 built-in datatype or collection type 轉換為另一種 built-in datatype or collection type
          CREATE TYPE Color_nt AS TABLE OF VARCHAR2(30);
          CREATE TYPE Color_vat AS VARRAY(16) OF VARCHAR2(30);
          CREATE TABLE color_models (
             model_type VARCHAR2(12),
             colors Color_vat);
          SELECT COLUMN_VALUE FROM
             TABLE (SELECT CAST(colors AS Color_nt)
                   FROM color_models
                   WHERE model_type = 'FGB');                
    2.The MULTISET pseudo-function
        MULTISET 必須和 CASTs 一起使用，MULTISET 將資料集轉換為 collection，SQL MULTISET function 和操作 nested tables 的 PL/SQL MULTISET 是完全不同的
          SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
          FROM DUAL;
        As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.     
    3.The TABLE pseudo-function
        TABLE 將 collection 轉換為可以 SELECT from 的結果集，既將 collection 中的內容像資料表一樣 select 出來
        例如，表中含有 collection 欄位，現在要找出 collection 中包含某個內容的所有資料：
          SELECT *
            FROM color_models c
           WHERE 'RED' IN
                (SELECT COLUMN_VALUE FROM TABLE(c.colors));
             
          MODEL_TYPE   COLORS
          ------------ ------------------------------------------------------
          RGB          COLOR_TAB_T('RED', 'GREEN', 'BLUE')
       
        COLUMN_VALUE 是 TABLE operator 建立的 system-defined 列名
Nested Table Multiset Operations
10g 之後針對 nested tables 提供了 high-level set operations，將其作為 multisets 對待
Operation             Return value    Description
=                     BOOLEAN         比較兩個 nested tables，如果 named type、cardinality、elements 相等，返回 TRUE，如果 nested table 中包含 null 則必不相等。10g 之前要比較，只能通過一個個元素來比較
<> or !=              BOOLEAN         比較兩個 nested tables 是否不同
[NOT] IN ( )          BOOLEAN         判斷左邊的 nested table 是否在右邊括弧中的 nested tables list 中
x MULTISET EXCEPT     NESTED TABLE    返回一個 NESTED TABLE，它的元素在 x 中，但不在 y 中，所有的 NESTED TABLE 都必須是相同類型的。DISTINCT 表示返回的 nested table 中除去重複元素
    [DISTINCT] y
x MULTISET INTERSECT NESTED TABLE    返回一個 NESTED TABLE，它的元素是 x 和 y 中元素的交集
    [DISTINCT] y
x MULTISET UNION      NESTED TABLE    返回一個 NESTED TABLE，它的元素是 x 和 y 中元素的並集
    [DISTINCT] y
SET(x)                NESTED TABLE    返回一個 NESTED TABLE，包含 x 中不重複的所有元素
x IS [NOT] A SET      BOOLEAN         判斷 x 中的元素是否有重複值
x IS [NOT] EMPTY      BOOLEAN         判斷 x 是否為空白
e [NOT] MEMBER        BOOLEAN         判斷運算式 e 是否包含在 x 的元素中
    [OF] x
y [NOT] SUBMULTISET   BOOLEAN         判斷 y 的元素是否全都包含在 x 的元素中
    [OF] x
 
 
Maintaining Schema-Level Collections

一、Necessary Privileges
    如果建立 Schema-Level 的 nested tables and VARRAYS，其它 Schema 如果需要使用該類型必須有該類型的 EXECUTE 許可權：
      GRANT EXECUTE on Color_tab_t TO JOE;
    對於包含 Schema-Level Collections 的 packages, procedures, functions 只要有這些過程的 EXECUTE 許可權就可以使用這些類型了，但對於 PL/SQL anonymous blocks 中使用這些類型，只能直接將這些類型的 EXECUTE 許可權授予執行的使用者
    如果某表的一列為 Schema-Level Collection 類型，對該表要有 SELECT, INSERT, UDPATE, and DELETE 許可權才能做相應的操作，其次在 INSERT or UPDATE 時還需要有該 Collection 類型的 EXECUTE 許可權

二、Collections and the Data Dictionary
    1.USER_TYPES
        所有建立的類型
        SELECT type_name FROM user_types
        WHERE typecode ='COLLECTION';
    2.USER_SOURCE
        類型的定義
        SELECT text FROM user_source
        WHERE name = 'FOO_T' AND type = 'TYPE'
        ORDER BY line;
    3.USER_DEPENDENCIES
        依賴建立的類型的對象
        SELECT name, type FROM user_dependencies
        WHERE referenced_name='FOO_T';