PL/SQL 集合的方法

    PL/SQL中提供了常用的三種集合聯合數組、巢狀表格、變長數組，而對於這幾個集合類型中元素的操作，PL/SQL提供了相應的函數或過程來操
縱數組中的元素或下標。這些函數或過程稱為集合方法。一個集合方法就是一個內建於集合中並且能夠操作集合的函數或過程，可以通過點標誌
來調用。本文主要描述如何操作這些方法。

一、集合類型提供的方法與調用方式

1、集合的方法與調用方式
    EXISTS
        函數EXISTS(n)在第n個元素存在的情況下會返回TRUE，否則返回FALSE。    
        通常使用EXISTS和DELETE來維護巢狀表格。其中EXISTS還可以防止引用不存在的元素，避免發生異常。
        當下標越界時，EXISTS會返回FALSE，而不是拋出SUBSCRIPT_OUTSIDE_LIMIT異常。

    COUNT
        COUNT能夠返回集合所包含的元素個數，對於大小不確定的情形則COUNT非常有用。
        可以在任何可以使用整數運算式的地方使用COUNT函數，如作為for迴圈的上限。
        計算元素個數時，被刪除的元素不會被count所統計。
        對於變長數組來說，COUNT值與LAST值恒等。
        對於巢狀表格來說，正常情況下COUNT值會和LAST值相等。但是，當我們從巢狀表格中間刪除一個元素，COUNT值就會比LAST值小。
            
    LIMIT
        用於檢測集合的最大容量
        由於巢狀表格和關聯陣列都沒有上界限制，所以LIMIT總會返回NULL。
        對於變長數組，LIMIT會返回它所能容納元素的個數最大值，該值是在變長數組聲明時指定的，並可用TRIM和EXTEND方法調整。
                
    FIRST,LAST
        FIRST和LAST會返回集合中第一個和最後一個元素在集合中的下標索引值。
        對於使用VARCHAR2類型作為鍵的關聯陣列來說，會分別返回最低和最高的索引值；索引值的高低順序是基於字串中字元的二進位值。
        但是，如果初始化參數NLS_COMP被設定成ANSI的話，索引值的高低順序就受初始化參數NLS_SORT所影響了。
        空集合的FIRST和LAST方法總是返回NULL。只有一個元素的集合，FIRST和LAST會返回相同的索引值。
        對於變長數組，FIRST恒等於1，LAST恒等於COUNT。
        對於巢狀表格，FIRST通常返回1，如果刪除第一個元素，則FIRST的值大於1，如果刪除中間的一個元素，此時LAST就會比COUNT大。
        在遍曆元素時，FIRST和LAST都會忽略被刪除的元素。

    PRIOR,NEXT,
        PRIOR(n)會返回集合中索引為n的元素的前驅索引值；NEXT(n)會返回集合中索引為n的元素的後繼索引值。
        如果n沒有前驅或後繼，PRIOR(n)或NEXT(n)就會返回NULL。
        對於使用VARCHAR2作為鍵的關聯陣列來說，它們會分別返回最低和最高的索引值；索引值的高低順序是基於字串中字元的二進位值。
        PRIOR和NEXT不會從集合的一端到達集合的另一端，即最末尾元素的的next不會指向集合中的first。
        在遍曆元素時，PRIOR和NEXT都會忽略被刪除的元素，即如果prior(3)之前的2被刪除則指向1，如果1也被刪除則返回null。

    EXTEND
        用於擴大巢狀表格或變長數組的容量，該方法不能用於聯合數組。
        EXTEND有三種形式
            EXTEND 在集合末端添加一個空元素
            EXTEND(n) 在集合末端添加n個空元素
            EXTEND(n,i) 把第i個元素拷貝n份，並添加到集合的末端
        對巢狀表格或變長數組添加了NOT NULL約束之後，不能使用EXTEND的前兩種形式。
        EXTEND操作的是集合內部大小，其中也包括被刪除的元素。所以，在計算元素個數的時候，EXTEND也會把被刪除的元素考慮在內。
        對於使用DELETE方法操作的元素，PL/SQL會保留其預留位置，後續可以重新利用。
                
    TRIM
        從集合的末尾刪除一個(TRIM)或指定數量TRIM(n)的元素，PL/SQL對TRIM掉的元素不再保留預留位置。
        如果n值過大的話，TRIM(n)就會拋出SUBSCRIPT_BEYOND_COUNT異常。
        通常，不要同時使用TRIM和DELETE方法。可把巢狀表格當作定長數組，只使用DELETE方法，或是當作棧，只對它使用TRIM和EXTEND方法。
        
    DELETE
        刪除集合中的所有或指定範圍的元素，通常有下列調用方式。
        DELETE 刪除集合中所有元素 。
        DELETE(n) 從以數字作主鍵的關聯陣列或者巢狀表格中刪除第n個元素。
            如果關聯陣列有一個字串鍵，對應該索引值的元素就會被刪除。如果n為空白，DELETE(n)不會做任何事情。
        DELETE(m,n) 從關聯陣列或巢狀表格中，把索引範圍m到n的所有元素刪除。
            如果m值大於n或是m和n中有一個為空白，那麼DELETE(m,n)就不做任何事。
            PL/SQL會為使用DELETE方式刪除的元素保留一個預留位置，後續可以重新為被刪除的元素賦值。
            注，不能使用delete方式刪除變長數組中的元素。
                        
        調用方式：
            collection_name.method_name[(parameters)]  

2、集合方法注意事項
    集合的方法不能在SQL語句中使用。
    EXTEND和TRIM方法不能用於關聯陣列。
    EXISTS，COUNT，LIMIT，FIRST，LAST，PRIOR和NEXT是函數；EXTEND，TRIM和DELETE是過程。
    EXISTS，PRIOR，NEXT，TRIM，EXTEND和DELETE對應的參數是集合的下標索引，通常是整數，但對於關聯陣列來說也可能是字串。
    只有EXISTS能用於空集合，如果在空集合上調用其它方法，PL/SQL就會拋出異常COLLECTION_IS_NULL。

二、各個方法綜合示範

-->樣本1DECLARE   output           VARCHAR2( 300 );   TYPE index_by_type IS TABLE OF VARCHAR2( 10 )                            INDEX BY BINARY_INTEGER;   index_by_table   index_by_type;   TYPE nested_type IS TABLE OF NUMBER;   nested_table     nested_type                           -->在聲明塊對巢狀表格進行初始化並賦值                       := nested_type( 10,20,30,40 ,50 ,60 ,70,80 ,90,100 );BEGIN   -- Populate index by table   FOR i IN 1 .. 10                                       -->在執行塊對聯合數組賦值   LOOP      index_by_table( i ) := 'Value_' || i;   END LOOP;   DBMS_OUTPUT.    put_line( '--------------------------- Before deleted -----------------------------------------' );   FOR i IN index_by_table.FIRST .. index_by_table.LAST   -->使用了first,last,作迴圈計數器上下標輸出當前聯合數組的所有元素   LOOP      output      := output || NVL( TO_CHAR( index_by_table( i ) ), 'NULL' ) || '  ';   END LOOP;   DBMS_OUTPUT.put_line( 'Element of Index_by_table are: ' || output );   output      := '';   FOR i IN 1 .. nested_table.COUNT                      -->使用了count,作迴圈計數器上下標輸出當前巢狀表格的所有元素   LOOP      output      := output || NVL( TO_CHAR( nested_table( i ) ), 'NULL' ) || '  ';   END LOOP;   DBMS_OUTPUT.put_line( 'Element of nested_table are: ' || output );   IF index_by_table.EXISTS( 3 ) THEN       -->EXISTS函數判斷聯合數組中的第3個元素是否存在      DBMS_OUTPUT.put_line( 'index_by_table(3) exists and the value is ' || index_by_table( 3 ) );   END IF;   -- delete 10th element from a collection   nested_table.delete( 10 );   -- delete elements 1 through 3 from a collection   nested_table.delete( 1, 3 );   index_by_table.delete( 10 );   DBMS_OUTPUT.put_line( 'nested_table.COUNT = ' || nested_table.COUNT );   DBMS_OUTPUT.put_line( 'index_by_table.COUNT = ' || index_by_table.COUNT );   DBMS_OUTPUT.put_line( 'nested_table.FIRST = ' || nested_table.FIRST );   DBMS_OUTPUT.put_line( 'nested_table.LAST = ' || nested_table.LAST );   DBMS_OUTPUT.put_line( 'index_by_table.FIRST = ' || index_by_table.FIRST );   DBMS_OUTPUT.put_line( 'index_by_table.LAST = ' || index_by_table.LAST );   DBMS_OUTPUT.put_line( 'nested_table.PRIOR(2) = ' || nested_table.PRIOR( 2 ) );   DBMS_OUTPUT.put_line( 'nested_table.NEXT(2) = ' || nested_table.NEXT( 2 ) );   DBMS_OUTPUT.put_line( 'index_by_table.PRIOR(2) = ' || index_by_table.PRIOR( 2 ) );   DBMS_OUTPUT.put_line( 'index_by_table.NEXT(2) = ' || index_by_table.NEXT( 2 ) );   -- Trim last two elements   nested_table.TRIM( 2 );   -- Trim last element   nested_table.TRIM;   DBMS_OUTPUT.put_line( 'nested_table.LAST = ' || nested_table.LAST );   DBMS_OUTPUT.put_line( '--------------------------- After deleted -----------------------------------------' ); output:='';   FOR i IN index_by_table.FIRST .. index_by_table.LAST     -->輸出刪除元素後聯合數組的所有剩餘元素   LOOP      output      := output || NVL( TO_CHAR( index_by_table( i ) ), 'NULL' ) || '  ';   END LOOP;   DBMS_OUTPUT.put_line( 'Element of Index_by_table are: ' || output );   output      := ''; output:='';   FOR i IN  nested_table.FIRST .. nested_table.LAST       -->輸出刪除元素後巢狀表格的所有剩餘元素   LOOP      output      := output || NVL( TO_CHAR( nested_table( i ) ), 'NULL' ) || '  ';   END LOOP;   DBMS_OUTPUT.put_line( 'Element of nested_table are: ' || output );END;--------------------------- Before deleted -----------------------------------------Element of Index_by_table are: Value_1  Value_2  Value_3  Value_4  Value_5  Value_6  Value_7  Value_8  Value_9  Value_10Element of nested_table are: 10  20  30  40  50  60  70  80  90  100index_by_table(3) exists and the value is Value_3nested_table.COUNT = 6      -->巢狀表格使用了兩次delete，分別是刪除最後一個元素和刪除第1到第3個元素，因此巢狀表格的count輸出為6index_by_table.COUNT = 9    -->聯合數組中刪除了最後的一個元素，因此聯合數組的count輸出為9nested_table.FIRST = 4      -->巢狀表格刪除了第1到第3個元素，因此其first變成4nested_table.LAST = 9       -->巢狀表格刪除了最後一個元素，因此last變成9index_by_table.FIRST = 1index_by_table.LAST = 9nested_table.PRIOR(2) =     -->巢狀表格的PRIOR(2)，第2個元素的前一個(下標為1)，由於1-3都被刪除，且1之前沒有任何元素，故為NULLnested_table.NEXT(2) = 4    -->巢狀表格2之後元素的下標，原本應該是3，由於3被刪除，因此3被忽略，返回4index_by_table.PRIOR(2) = 1index_by_table.NEXT(2) = 3  nested_table.LAST = 7      -->nested_table.TRIM(2)與nested_table.TRIM總共刪除了3個元素及預留位置，故LAST為7。--------------------------- After deleted -----------------------------------------Element of Index_by_table are: Value_1  Value_2  Value_3  Value_4  Value_5  Value_6  Value_7  Value_8  Value_9Element of nested_table are: 40  50  60  70PL/SQL procedure successfully completed.------------------------------------------------------------------------------------------------------------------------------>樣本2DECLARE   TYPE varray_type IS VARRAY(10) OF NUMBER;   varray varray_type := varray_type(1, 2, 3, 4, 5, 6);   PROCEDURE print_numlist( the_list varray_type ) IS      output   VARCHAR2( 128 );   BEGIN      FOR i IN the_list.FIRST .. the_list.LAST      LOOP         output      := output || NVL( TO_CHAR( the_list( i ) ), 'NULL' ) || ' ';      END LOOP;      DBMS_OUTPUT.put_line( output );   END;BEGIN   print_numlist( varray );   DBMS_OUTPUT.put_line( 'varray.COUNT = ' || varray.COUNT );   DBMS_OUTPUT.put_line( 'varray.LIMIT = ' || varray.LIMIT );   DBMS_OUTPUT.put_line( 'varray.FIRST = ' || varray.FIRST );   DBMS_OUTPUT.put_line( 'varray.LAST = ' || varray.LAST );   DBMS_OUTPUT.put_line( 'The maximum number you can use with ' || 'varray.EXTEND() is ' || ( varray.LIMIT - varray.COUNT ) );   varray.EXTEND( 2, 4 );        -->將第4個元素的值複製2份，追加到集合尾部   DBMS_OUTPUT.put_line( 'varray.LAST = ' || varray.LAST );   DBMS_OUTPUT.put_line( 'varray(' || varray.LAST || ') = ' || varray( varray.LAST ) );   print_numlist( varray );   -- Trim last two elements   varray.TRIM( 2 );   DBMS_OUTPUT.put_line( 'varray.LAST = ' || varray.LAST );END;1 2 3 4 5 6               -->輸出varray中的所有元素varray.COUNT = 6varray.LIMIT = 10         -->limit方法得到變長數組的最大容量varray.FIRST = 1varray.LAST = 6The maximum number you can use with varray.EXTEND() is 4   -->得到可以extend的容量，即還可以儲存4個元素varray.LAST = 8          --> extend之後last的下標值為8varray(8) = 4            -->第8個元素的值則為41 2 3 4 5 6 4 4          -->輸出varray中的所有元素varray.LAST = 6          -->由於使用了varray.TRIM( 2 )，所以last又變成了6PL/SQL procedure successfully completed.-->Author : Robinson Cheng-->Blog   : http://blog.csdn.net/robinson_0612

三、更多參考

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫

啟用 AUTOTRACE 功能

函數使得索引列失效

Oracle 綁定變數窺探

Oracle 自適應共用遊標