【開發篇plsql】plsql資料型別（一） 集合型別

yellowlee發表於2010-06-01
SQL資料型別

1,資料型別

集合(collection)

集合是一組相同型別的有序元素。經典的程式設計原理裡面的佇列，陣列等就屬於集合型別，對於oracle來說有：

 

巢狀表(nested table)

擁有任意數量的元素，以連續的數字作為下標。可以定義等價的sql型別，允許巢狀表儲存在資料庫表，

並通過sql來操縱它們，這裡的巢狀表是作為物理儲存的，本節不做敘述，實際使用較少，在基礎篇內的

資料庫表一節中簡述。本節只講述其作為plsql型別使用的情況。

如果理解一維陣列和多維陣列的原理的話，巢狀表也容易理解，巢狀表可以看做是沒有上界的一維陣列，

其中的元素可以刪除，可以使用next來遍歷。也可以將巢狀表內的元素也使用巢狀表來實現多維。

具體的語法如下：

type type_name is table of element_type [NOT NULL]

看一個例子：

SQL> set serveroutput on;

SQL> declare

  2    type tp_nested_table_1 is table of number;

  3    v_nested_table_1 tp_nested_table_1 := tp_nested_table_1(1, 2);

  4  begin

  5    for i in v_nested_table_1.first .. v_nested_table_1.last loop

  6      dbms_output.put_line(i);

  7    end loop;

  8  end;

  9  /

 

1

2

 

PL/SQL procedure successfully completed

上述程式碼中定義了一個number型別的table，並初始化為tp_nested_table_1(1, 2)，也即包含了2個元素，分別為1,2,，下面來修改一下上述程式碼，在使用時初始化：

SQL> declare

  2    type tp_nested_table_1 is table of number;

  3    v_nested_table_1 tp_nested_table_1 := tp_nested_table_1();

  4  begin

  5    v_nested_table_1 := tp_nested_table_1(1,2);

  6    for i in v_nested_table_1.first .. v_nested_table_1.last loop

  7      dbms_output.put_line(i);

  8    end loop;

  9  end;

 10  /

 

1

2

 

PL/SQL procedure successfully completed

或者：

SQL> declare

  2    type tp_nested_table_1 is table of number;

  3    v_nested_table_1 tp_nested_table_1 := tp_nested_table_1(1,2,3);

  4  begin

  5    v_nested_table_1 := tp_nested_table_1(1,2);

  6    v_nested_table_1 := tp_nested_table_1(1,2,3,4,5);

  7    for i in v_nested_table_1.first .. v_nested_table_1.last loop

  8      dbms_output.put_line(i);

  9    end loop;

 10  end;

 11  /

 

1

2

3

4

5

 

PL/SQL procedure successfully completed

可以看出前面介紹的巢狀表的一個特質，即無上限。

另外，使用了巢狀表的first和last屬性，來看看nested table的幾個屬性：

SQL> declare

  2    type tp_nested_table_1 is table of number;

  3    v_nested_table_1 tp_nested_table_1 := tp_nested_table_1(1,2,3);

  4  begin

  5    v_nested_table_1 := tp_nested_table_1(1,2,3,4,5,6);

  6    dbms_output.put_line('v_nested_table_1.first :'||v_nested_table_1.first);

  7    dbms_output.put_line('v_nested_table_1.next(1) :'||v_nested_table_1.next(1));

  8    dbms_output.put_line('v_nested_table_1.next(3) :'||v_nested_table_1.next(3));

  9    dbms_output.put_line('v_nested_table_1.next(1) :'||v_nested_table_1.next(1));

 10    dbms_output.put_line('v_nested_table_1.last :'||v_nested_table_1.last);

 11  end;

 12  /

 

v_nested_table_1.first :1

v_nested_table_1.next(1) :2

v_nested_table_1.next(3) :4

v_nested_table_1.next(1) :2

v_nested_table_1.last :6

 

PL/SQL procedure successfully completed

SQL> declare

  2    type tp_nested_table_1 is table of number;

  3    v_nested_table_1 tp_nested_table_1 := tp_nested_table_1(1,2,3);

  4  begin

  5    v_nested_table_1 := tp_nested_table_1(1,2,3,4,5,6);

  6    dbms_output.put_line('v_nested_table_1.count :'||v_nested_table_1.count);

  7    v_nested_table_1.delete(2);

  8    v_nested_table_1.extend(2);

  9    dbms_output.put_line('v_nested_table_1.count :'||v_nested_table_1.count);

 10    dbms_output.put_line('v_nested_table_1.first :'||v_nested_table_1.first);

 11    dbms_output.put_line('v_nested_table_1.next(1) :'||v_nested_table_1.next(1));

 12    dbms_output.put_line('v_nested_table_1.next(3) :'||v_nested_table_1.next(3));

 13    dbms_output.put_line('v_nested_table_1.next(1) :'||v_nested_table_1.next(1));

 14    dbms_output.put_line('v_nested_table_1.last :'||v_nested_table_1.last);

 15  end;

 16  /

 

v_nested_table_1.count :6

v_nested_table_1.count :7

v_nested_table_1.first :1

v_nested_table_1.next(1) :3

v_nested_table_1.next(3) :4

v_nested_table_1.next(1) :3

v_nested_table_1.last :8

 

PL/SQL procedure successfully completed

上面2個例子很清楚的看出了first,next,last的意義，

另外，可以使用delete(x)來刪除指定的元素Delete()刪除全部元素，

delete(x,y)刪除下標從x到y的元素，

可以使用extend(x)來擴充套件指定數量的元素，而extend是在末尾新增一個元素，extend(x,n)是新增x元素的n個副本，注意新擴充套件的元素預設值是null。

next(x)是緊跟著當前元素x個位置後的元素，

count是當前的元素個數,注意新增或者刪除元素後count值變化了，

 

 

 

可變長度陣列(varray:variable-size arrays)

擁有固定數量的元素（雖然可以在執行時改變元素的數量），使用連續的數字作為下標，和nested table

一樣可以定義等價的sql型別，允許varray被儲存在資料庫表中，也可以通過sql來操縱，但是靈活性較

巢狀表差一些。

Varray擁有一個最大長度，需要顯示的定義。它由從1開始的下標開始，可以在執行時擴充套件上界。

具體語法如下：

type type_name is [varray ¦varying array](max_size) of element_type[NOT NULL]

看一個例子：

SQL> declare

  2    type tp_varray1 is varray(6) of varchar2(10);

  3    v_varray tp_varray1;

  4  begin

  5    v_varray := tp_varray1('1', 'a');

  6    for i in v_varray.first .. v_varray.last loop

  7      dbms_output.put_line('v_varray(i):' || v_varray(i));

  8    end loop;

  9    --注意雖然定義了varray(6)但上述事實上只有2個元素，下面的賦值為非法

 10    --v_varray(3) := null;

 11    --可以擴充套件一個元素，然後賦值，預設值為null

 12    v_varray.extend;

 13    v_varray(3) := null;

 14    dbms_output.put_line('v_varray.count:' || v_varray.count);

 15    dbms_output.put_line('v_varray.next(1):' || v_varray.next(1));

 16    --注意varray不能刪除元素，但可以清除其的值：v_varray(x) := null;

 17    --dbms_output.put_line('v_varray.delete(1):'||v_varray.delete(1));

 18  end;

 19  /

 

v_varray(i):1

v_varray(i):a

v_varray.count:3

v_varray.next(1):2

 

PL/SQL procedure successfully completed

 

再來看看多維的情況：

SQL> declare

  2    type tp_varray1 is varray(6) of varchar2(10);

  3    type tp_varray2 is varray(6) of tp_varray1;

  4    v_varray1 tp_varray1;

  5    v_varray2 tp_varray2;

  6    v_varray3 tp_varray2;

  7  begin

  8    v_varray1 := tp_varray1('1', 'a');

  9 

 10    for i in v_varray1.first .. v_varray1.last loop

 11      dbms_output.put_line('v_varray1(i):' || v_varray1(i));

 12      v_varray2 := tp_varray2(v_varray1);

 13      for j in v_varray2.first .. v_varray2.last loop

 14        dbms_output.put_line('v_varray2(1)(j):' || v_varray2(j) (i));

 15      end loop;

 16    end loop;

 17 

 18    v_varray3 := tp_varray2(v_varray1, tp_varray1('a', 'd'));

 19 

 20    for n in v_varray3.first .. v_varray3.last loop

 21      for m in v_varray3(n).first .. v_varray3(n).last loop

 22        dbms_output.put_line('v_varray3(n) (m):' || v_varray3(n) (m));

 23      end loop;

 24    end loop;

 25 

 26    dbms_output.put_line('v_varray3.count:' || v_varray3.count);

 27    v_varray3.extend;

 28    dbms_output.put_line('v_varray3.count:' || v_varray3.count);

 29 

 30    dbms_output.put_line('v_varray3(1).count:' || v_varray3(1).count);

 31    v_varray3(1) .extend;

 32    dbms_output.put_line('v_varray3(1).count:' || v_varray3(1).count);

 33 

 34  end;

 35  /

 

v_varray1(i):1

v_varray2(1)(j):1

v_varray1(i):a

v_varray2(1)(j):a

v_varray3(n) (m):1

v_varray3(n) (m):a

v_varray3(n) (m):a

v_varray3(n) (m):d

v_varray3.count:2

v_varray3.count:3

v_varray3(1).count:2

v_varray3(1).count:3

 

PL/SQL procedure successfully completed

 

 

 

關聯陣列(associative arrays)

或者叫做索引表(index by tables)，可以使用任意數字或字串來作為下標，

和其他語言（如java）裡的hash table有點相似。

可以在包或者儲存過程或者函式中定義集合型別，同時也可以用來作為過程或函式的引數。

和巢狀表與varray不同，索引表不能被儲存在資料庫表中。

type type_name is table of element_type [NOT NULL] index by {binary_integer|varchar2}

同樣也用簡單的例子來示意索引表的使用：

SQL> declare

  2    type tp_indexby_table1 is table of number(7, 2) index by pls_integer;

  3    v_indexby_table1 tp_indexby_table1;

  4  begin

  5    select sal

  6      into v_indexby_table1(1)

  7      from scott.emp a

  8     where a.empno = 7369;

  9 

 10    dbms_output.put_line('v_indexby_table1(1):' || v_indexby_table1(1));

 11 

 12    select a.sal bulk collect

 13      into v_indexby_table1

 14      from scott.emp a

 15     where rownum < 5;

 16 

 17    for i in v_indexby_table1.first .. v_indexby_table1.last loop

 18      dbms_output.put_line('v_indexby_table1(i):' || v_indexby_table1(i));

 19    end loop;

 20    dbms_output.put_line(v_indexby_table1.count);

 21    dbms_output.put_line(v_indexby_table1.next(1));

 22    v_indexby_table1.delete(1);

 23    dbms_output.put_line(v_indexby_table1.count);

 24  end;

 25  /

 

v_indexby_table1(1):800

v_indexby_table1(i):123

v_indexby_table1(i):800

v_indexby_table1(i):1600

v_indexby_table1(i):1250

4

2

3

 

PL/SQL procedure successfully completed

 

注意不能對index by table 使用extend。當然table的型別也可以是ROWTYPE。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-664195/，如需轉載，請註明出處，否則將追究法律責任。

相關文章