PL/SQL集合类型

PL/SQL中没有数组的概念，他的集合数据类型和数组是相似的。在7.3以前的版本中只有一种集合，称为PL/SQL表，在这之后又有两种集合数据类型:嵌套表和varray。其中varray集合中的元素是有数量限制的，index_by表和嵌套表是没有这个限制的。index-by表是稀疏的，也就是说下标可以不连续，varray类型的集合则是紧密的，他的下标没有间隔。index_by表不能存储在数据库中，但是嵌套表和varray可以被存储在数据库中。 集合在使用时必须先使用type进行定义方可使用。

 

1.index_by表

type type_name is table of element_type [NOT NULL] index by binary_integer

2.嵌套表

type type_name is table of element_type [NOT NULL]

 

3.varray

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

一，index_by表

TYPE TYPE1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

1.使用的时候需要先赋值后读取，至少也要先初期化一下，否则会出现异常：ORA-01403: no data found。

2.这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。

 

或者叫做索引表(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。

二，嵌套表

TYPE TYPE2 IS TABLE OF VARCHAR2(10);

 

1.必须进行初期化，否则会出现异常：ORA-06531: Reference to uninitialized collection

2.初期化方法：v1 TYPE2 := TYPE2(); --声明时初期化数组为空

 

拥有任意数量的元素，以连续的数字作为下标。可以定义等价的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 /

  

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 /

  

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 /

 

 

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值变化了，

 

3.数组元素的访问：

下标从1开始，不能超过数组所有元素的总和，当下标超出允许范围时，出现异常：ORA-06532: Subscript outside of limit

因为不能访问空数组，所以空数组的场合，必须进行数组扩展。 54ne.com

例：v1.EXTEND;

V1(1):= ‘1’; --访问合法 54com.cn

v1(2):= ‘2’; --访问非法，之前必须再次执行v1.EXTEND; 网管网bitsCN_com

例：v2的下标范围是1～5。

v2(5):= ‘Hello’; --访问合法

DBMS_OUTPUT.put_line(v2(6)); --访问非法

 

 

三，Varray

TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);

由于类型定义时的元素个数限制，所以TYPE3的变量在使用时最大的元素个数不能超过5个。

 

拥有固定数量的元素（虽然可以在运行时改变元素的数量），使用连续的数字作为下标，和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

 

四，集合内建函数

集合还有很多内建函数，这些函数称为方法，调用方法的语法如下：

collection.method

下表中列出oracle中集合的方法

方法 描述 使用限制

COUNT 返回集合中元素的个数 网管网bitsCN.com

DELETE 删除集合中所有元素 54com.cn

DELETE(x) 删除元素下标为x的元素，如果x为null,则集合保持不变 对VARRAY非法

DELETE(x,y) 删除元素下标从X到Y的元素，如果X>Y集合保持不变 对VARRAY非法

EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE

EXTEND 在集合末尾添加一个元素 对Index_by非法

EXTEND(x) 在集合末尾添加x个元素 对Index_by非法

EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法

FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。 feedom.net

LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT。

LIMIT 返回VARRY集合的最大的元素个数，对于嵌套表和Index_by集合无用。

NEXT(x) 返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null。

PRIOR(x) 返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。

TRIM 从集合末端开始删除一个元素 对index_by不合法

TRIM(x) 从集合末端开始删除x个元素 对index_by不合法