天天看點
傳回

集合

集合是相同資料類型元素的組合，類似于程式設計語言中的數組。它包含如下三種類型：關聯數組Associative array（索引表 pl/sql table）、嵌套表（Nested Table）、變長數組（VARRAY）

下面我們來看看官方對于三者的比較

集合

一、 集合方法

exists(index) 索引處的元素是否存在

count 目前集合中的元素總個數

limit 集合元素索引的最大值，索引表和嵌套表是不限個數的，是以傳回null，變長數組傳回定義時的最大索引

first 傳回集合第一個元素索引

last 傳回集合最後一個元素索引

prior 目前元素的前一個元素的索引

next 目前元素的後一個元素的索引

extend 擴充集合的容量，增加元素 隻是用于嵌套表和varry類型

     x.extend 增加一個null元素

     x.extend(n) 增加n個null元素

     x.extend(n,i) 增加n個元素，元素值與第i個元素相同

trim 從集合的尾部删除元素 隻用于NEST TABLE和VARRY類型

     trim 從集合尾部删除一個元素

     trim(n) 從集合尾部删除n個元素

delete 按索引删除集合元素

      delete 删除所有

      delete(index) 删除第index個

      delete(a,b) 删除a--b之間的所有元素

注意：用DELETE删除元素時，PL/SQL會儲存所删除元素的占位符。而用TRIM删除時，卻不會儲存所删除元素的占位符。

二、關聯數組Associative array

關聯數組具有以下特征：

1> 下标無限制，可以為負數。

2> 元素個數無限制。

建立關聯數組的文法如下所示：

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY index_type;

table_name TYPE_NAME;

其中，type_name是使用者自定義資料類型的名字，element_type是關聯數組中元素類型，index_type是元素下标的資料類型(BINARY_INTEGER,PLS_INTEGER,VARCHAR2);

例一：

DECLARE
  CURSOR c_ename IS SELECT * FROM EMP;

  TYPE t_ename IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  ename_tab t_ename; 

  v_counter INTEGER :=0;
BEGIN
  FOR i IN c_ename LOOP
     v_counter := v_counter+1;
     ename_tab(v_counter) := i.ename;
     DBMS_OUTPUT.PUT_LINE(v_counter||': '||ename_tab(v_counter));
  END LOOP;
END;
/

結果輸出為：

1: SMITH

2: ALLEN

3: WARD

4: JONES

5: MARTIN

6: BLAKE

7: CLARK

8: SCOTT

9: KING

10: TURNER

11: ADAMS

12: JAMES

13: FORD

14: MILLER

上述範例元素下标的類型為BINARY_INTEGER。

下面另舉一例index_type為string的。

例二：

DECLARE
  TYPE t_dept IS TABLE OF NUMBER      -- Associative array type
    INDEX BY VARCHAR2(64);            --  indexed by string
  dept_no  t_dept;                    -- Associative array variable
  i  VARCHAR2(64);                    -- Scalar variable
BEGIN
  -- Add elements (key-value pairs) to associative array:
  dept_no('Ca')  := 10;
  dept_no('Ba')  := 30;
  dept_no('Bb')  := 20;

  -- Print associative array:
  i := dept_no.FIRST;  -- Get first element of array
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('Dept No. for ' || i || ' is ' || dept_no(i));
    i := dept_no.NEXT(i);  -- Get next element of array
  END LOOP;
END;
/

輸出結果為：

Dept No. for Ba is 30

Dept No. for Bb is 20

Dept No. for Ca is 10

由此可見，輸出結果是經過排序的，該排序依據的是元素下标的sort order，而不是元素的creation order。

三、 嵌套表Nested table

嵌套表具有以下特征：

1> 下标從1開始，元素個數沒有限制

2> 使用時必須先初始化，用extend屬性可以擴充元素個數

3> 可以作為表定義資料類型，但是前提是要先create創造嵌套表類型

4> 和索引表的差別也就是看看有無index by語句，嵌套表的索引固定是int型的

建立文法：

TYPE type_name IS TABLE OF element_type;

例一，修改上述索引表的例一

DECLARE
  CURSOR c_ename IS SELECT * FROM EMP;

  TYPE t_ename IS TABLE OF emp.ename%TYPE;
  ename_tab t_ename := t_ename();     -- 初始化

  v_counter INTEGER :=0;
BEGIN
  FOR i IN c_ename LOOP
     v_counter := v_counter+1;
     ename_tab.EXTEND;                -- 增加集合大小
     ename_tab(v_counter) := i.ename;
     DBMS_OUTPUT.PUT_LINE(v_counter||': '||ename_tab(v_counter));
  END LOOP;
END;
/

如官方對于集合三種類型的比較，當聲明索引表時，索引表被自動設定為空，而當聲明嵌套表和變長數組時，它們被自動設定為NULL，是以必須對其進行初始化。

該例中還包含一個集合方法：EXTEND，這個方法允許你增加集合的大小。注意，EXTEND不能和索引表一起使用。

例二，在表列中使用嵌套表，嵌套表類型的列是單獨一個表存儲，先建立一個這樣的類型才能使用。

1> 建立嵌套表類型

SQL> create type nest_tab_type is table of varchar2(30);

2> 建立表

SQL> create table test (

         id int,

         vals nest_tab_type  --嵌套表類型

         ) nested table vals store as nest_tab;   --vals字段用嵌套表存儲，表名為nest_tab

3> 插入資料

SQL> insert into test values(1,nest_tab_type('one','two','three','four')); 

4> 查詢資料

SQL> select * from test;

ID VALS

---------- ------------------------------------------------------------

1 NEST_TAB_TYPE('one', 'two', 'three', 'four')

declare
   v_id int;
   v_tab nest_tab_type;
begin
   select * into v_id,v_tab from test where id=1;
   dbms_output.put_line(v_id);
   for i in 1..v_tab.count loop
   dbms_output.put_line(v_tab(i));
   end loop;
end;

1

one

two

three

four

PL/SQL procedure successfully completed.

四、 可變數組Varray

變長數組具有以下特征：

1> 下标從1開始，元素個數有最大限制。

2> 類似于嵌套表，當變長數組被聲明時，自動設定為NULL。必須在引用某個元素之前，初始化

3> 不能對變長數組使用DELETE方法來删除元素。

4> 可以作為表列類型

建立文法如下：

TYPE type_name IS VARRAY(size_limit) OF element_type;

例一，

DECLARE
  TYPE varray_type IS VARRAY(10) OF NUMBER;
  varray varray_type := varray_type(10,20,30,40,50,60);
BEGIN
  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);

  varray.EXTEND(2,4);
  DBMS_OUTPUT.PUT_LINE('varray.LAST = '||varray.LAST);
  DBMS_OUTPUT.PUT_LINE('varray('||varray.LAST||')='||varray(varray.LAST));

  --Tirm last two elements
  varray.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('varray.LAST = '||varray.LAST);
END;

varray.COUNT = 6

varray.LIMIT = 10

varray.FIRST = 1

varray.LAST = 6

varray.LAST = 8

varray(8)=40

例二，可變數組作為表列類型

可變數組是存儲在表内部的，不同于嵌套表

1> 建立可變數組類型

SQL> create type varr_type is varray(10) of varchar2(30);

create table test_varray(    

    id int,    

    varr varr_type  --varr使用可變數組類型    

    );  

SQL> insert into test_varray values(1,varr_type('a','b','c'));

declare
    v_varr varr_type;
begin
    select varr into v_varr from test_varray where id=1;
    for i in 1..v_varr.count loop
    dbms_output.put_line(v_varr(i));
    end loop;
end;

五、多元集合（Multidimensional Collections）

從Oracle 9i開始，PL/SQL允許建立元素類型為集合類型的集合，這種集合稱為多元集合。

試舉一例：

DECLARE
  TYPE varray_type1 IS VARRAY(4) OF INTEGER;
  TYPE varray_type2 IS VARRAy(3) OF varray_type1;
  varray1 varray_type1 := varray_type1(2,4,6,8);
  varray2 varray_type2 := varray_type2(varray1);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Varray of integers');
  FOR i IN 1..4 LOOP
     DBMS_OUTPUT.PUT_LINE('varray('||i||'): '||varray1(i));
  END LOOP;
  
  varray2.EXTEND;
  varray2(2) := varray_type1(1,3,5,7);
  
  DBMS_OUTPUT.PUT_LINE(chr(10)||'Varray of varrays of integers');
  FOR i IN 1..2 LOOP
     FOR j IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE
          ('varray2('||i||')('||j||'): '||varray2(i)(j));
     END LOOP;
  END LOOP;
END;

SQL> /

Varray of integers

varray(1): 2

varray(2): 4

varray(3): 6

varray(4): 8

Varray of varrays of integers

varray2(1)(1): 2

varray2(1)(2): 4

varray2(1)(3): 6

varray2(1)(4): 8

varray2(2)(1): 1

varray2(2)(2): 3

varray2(2)(3): 5

varray2(2)(4): 7

總結： 通常來說，對集合類型的第一選擇應該是Associative array，因為它不需要初始化或者EXTEND操作，并且是迄今為止最高效的集合類型。唯一不足的一點是它隻能用于PL/SQL而不能直接用于資料庫。

PL/SQL
上一篇: 修改NLS_DATE_FORMAT的四種方式
下一篇: PL/SQL異常處理

繼續閱讀