PL/SQL collection（1）— 概述

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 前必须首先初始化。