PL/SQL collection（1）— 概述 - ORACLE PROGRAMING

Oracle 提供了三种集合类型：

联合数组：无边界；可以是稀疏（sparse）的，也可以是紧密（dense）的（跟使用方式相关）；不能用作列的定义类型。

嵌套表：无边界；开始紧密，通过删除使得稀疏；可以用作列的定义类型。

数组：有边界；紧密；可以用作列的定义类型。

这三种类型都是一维的，如果需要多维的数组类型，可以嵌套定义。

1. 联合数组（associative array）

SQL> set serveroutput on

SQL> declare

2 type type_names is table of varchar2(20) index by pls_integer;

3 t_names type_names;

4 pls_rows pls_integer;

5 begin

6 t_names(100000) := 'yuechaotian';

7 t_names(302944003) := 'guoguo';

8 t_names(-4903) := 'oratea';

9 t_names(0) := 'hot_dog';

10 pls_rows := t_names.first;

11 while (pls_rows is not null) loop

12 dbms_output.put_line( t_names(pls_rows) );

13 pls_rows := t_names.next(pls_rows);

14 end loop;

15 end;

16 /

oratea

hot_dog

yuechaotian

guoguo

PL/SQL 过程已成功完成。

SQL>

这里只能使用 WHILE 循环，因为定义的变量 t_names 是稀疏的。如果使用 FOR 循环，会抛出 NO_DATA_FOUND 异常。但我们也可以以紧密的方式来使用它：

SQL> declare

2    type type_names is table of varchar2(20) index by pls_integer;

3    t_names type_names;

4 begin

5    t_names(1) := 'yuechaotian';

6    t_names(2) := 'guoguo';

7   t_names(3) := 'oratea';

8    t_names(4) := 'hot_dog';

9    for n_pointer in t_names.first..t_names.last loop

10      dbms_output.put_line( t_names(n_pointer) );

11    end loop;

12 end;

13 /

yuechaotian

guoguo

oratea

hot_dog

PL/SQL 过程已成功完成。

有时候，使用稀疏数组很方便。比如，你可以使用表中某列作为稀疏数组的下标，来存储数据。

2. 内嵌表（nested table）

从 Oracle10g Release 1 开始，内嵌表支持 MULTISET EXCEPT，功能与 SQL 中的 MINUS 类似：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> set serveroutput on

SQL> declare

2    type type_names is table of varchar2(20);

3    t_names_parent type_names := type_names();

4    t_names_children type_names := type_names();

5    t_names_family type_names := type_names();

6 begin

7    t_names_family.extend(5);

8    t_names_family(1) := 'my father';

9    t_names_family(2) := 'my mother';

10    t_names_family(3) := 'my sister';

11    t_names_family(4) := 'my brother';

12    t_names_family(5) := 'yuechaotian';

13    t_names_children.extend;

14    t_names_children(1) := 'my sister';

15    t_names_children.extend;

16    t_names_children(2) := 'my brother';

17    t_names_children.extend;

18    t_names_children(3) := 'yuechaotian';

19    t_names_parent := t_names_family multiset except t_names_children;

20    for n_pointer in t_names_parent.first..t_names_parent.last loop

21      dbms_output.put_line( t_names_parent(n_pointer) );

22   end loop;

23 end;

24 /

my father

my mother

PL/SQL 过程已成功完成。

SQL>

因为上面定义的内嵌表是紧密的，所以可以使用 FOR 循环。使用内嵌表存储数据前，必须初始化。也必须手工分配存储空间。

初始的内嵌表是紧密的，但可以通过删除操作使得它变得稀疏：

SQL> set serveroutput on

SQL> declare

2    type type_names is table of varchar2(20);

3    t_names_mine type_names := type_names();

4 begin

5    t_names_mine.extend(3);

6    t_names_mine(1) := 'yuechaotian';

7    t_names_mine(2) := 'yuechaotiao';

8    t_names_mine(3) := 'tianyc';

9    t_names_mine.delete(2);

10    for n_pointer in t_names_mine.first..t_names_mine.last loop

11      if t_names_mine.exists( n_pointer ) then

12        dbms_output.put_line( n_pointer || ' : ' || t_names_mine(n_pointer) );

13      else

14        dbms_output.put_line( n_pointer || ' : no data found' );

15      end if;

16    end loop;

17 end;

18 /

1 : yuechaotian

2 : no data found

3 : tianyc

PL/SQL 过程已成功完成。

SQL>

3. VARRAY

我们看一个使用 VARRAY 作为列的定义类型的例子：

SQL> create type type_parent is varray(2) of varchar2(20);

2 /

类型已创建。

SQL> create type type_children is varray(3) of varchar2(20);

2 /

类型已创建。

SQL> create table my_family(

2    province varchar2(20),

3    parent type_parent,

4    children type_children

5 );

表已创建。

SQL> declare

2    t_parent type_parent := type_parent();

3    t_children type_children := type_children();

4 begin

5    t_parent.extend(2);

6    t_parent(1) := 'my father';

7    t_parent(2) := 'my mother';

8    t_children.extend(3);

9    t_children(1) := 'my sister';

10    t_children(2) := 'my brother';

11    t_children(3) := 'yuechaotian';

12    insert into my_family

13      values( 'Hebei', t_parent, t_children );

14    commit;

15 end;

16 /

PL/SQL 过程已成功完成。

SQL> select * from my_family;

PROVINCE

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

PARENT

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

CHILDREN

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

Hebei

TYPE_PARENT('my father', 'my mother')

TYPE_CHILDREN('my sister', 'my brother', 'yuechaotian')

SQL>

既然 VARRAY 可以做为列的定义类型，当然可以使用变量锚定了。比如上面的1-4行代码可以改为：

declare

t_parent my_family.parent%type := type_parent();

t_children my_family.children%type := type_children();

begin

VARRAY 有边界。定义时必须指定最大长度。同嵌套表一样，使用 VARRAY 前必须首先初始化。