PL/SQL collection（2）— 使用

集合可以在如下情况下使用：

 

1. 作为记录类型的元素定义

 

SQL> set serveroutput on

SQL> declare

  2    type type_parent is varray(2) of varchar2(20);

  3    type record_student is record(

  4      id number(10),

  5      name varchar2(20),

  6      parent type_parent

  7    );

  8    rec_student record_student;

  9  begin

 10    rec_student.id := 1000000001;

 11    rec_student.name := 'yuechaotian';

 12    rec_student.parent := type_parent();

 13    rec_student.parent.extend(2);

 14    rec_student.parent(1) := 'my father';

 15    rec_student.parent(2) := 'my mother';

 16

 17    dbms_output.put_line( 'id: ' || rec_student.id );

 18    dbms_output.put_line( 'name: ' || rec_student.name );

 19    for n_pointer in rec_student.parent.first..rec_student.parent.last loop

 20      dbms_output.put_line( 'parent of ' || n_pointer || ' is: ' || rec_student.parent(n_pointer) );

 21    end loop;

 22  end;

 23  /

id: 1000000001

name: yuechaotian

parent of 1 is: my father

parent of 2 is: my mother

 

PL/SQL 过程已成功完成。

 

SQL>

 

也可以使用锚定来定义记录，跟 Oracle 自定义的类型没有任何区别：

 

SQL> desc my_family

 名称                            是否为空? 类型

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

 PROVINCE                     VARCHAR2(20)

 PARENT                       TYPE_PARENT

 CHILDREN                     TYPE_CHILDREN

 

SQL> declare

  2    rec_family my_family%rowtype;

  3  begin

  4    select *

  5      into rec_family

  6      from my_family

  7     where rownum = 1;

  8

  9    dbms_output.put_line( 'PROVINCE: ' || rec_family.PROVINCE );

 10

 11    for n_pointer in rec_family.PARENT.first..rec_family.PARENT.last loop

 12      dbms_output.put_line( 'PARENT of ' || n_pointer || ' is: ' || rec_family.PARENT(n_pointer) );

 13    end loop;

 14

 15    for n_pointer in rec_family.CHILDREN.first..rec_family.CHILDREN.last loop

 16      dbms_output.put_line( 'CHILDREN of ' || n_pointer || ' is: ' || rec_family.CHILDREN(n_pointer) );

 17    end loop;

 18  end;

 19  /

PROVINCE: Hebei

PARENT of 1 is: my father

PARENT of 2 is: my mother

CHILDREN of 1 is: my sister

CHILDREN of 2 is: my brother

CHILDREN of 3 is: yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL>

 

2. 作为函数或过程的参数

 

格式如下：

 

parameter_name [ IN | IN OUT | OUT ] parameter_type

       [ DEFAULT | :=  ]

 

PL/SQL 并没有给我们提供预定义的集合类型，所以要想使用集合参数，我们就必须得自己提前定义好需要的集合类型。你可以：

（1）通过 CREATE TYPE 定义对象级的集合类型；

（2）在包中定义集合类型；

（3）在外部定义集合类型。

 

3. 作为函数返回值的类型

 

可以将返回值直接赋予一个对应的集合类型变量，这是很简单的，都不需要进行初始化：

 

SQL> set serveroutput on

SQL> declare

  2    t_myParent my_family.parent%type;

  3

  4    function fun_getParent( prm_province in my_family.province%type ) return type_parent

  5    is

  6      t_parent my_family.parent%type;

  7    begin

  8      select parent

  9        into t_parent

 10        from my_family

 11       where province = prm_province;

 12      return t_parent;

 13    exception

 14      when no_data_found then

 15        return null;

 16    end;

 17  begin

 18    t_myParent := fun_getParent( 'Hebei' );

 19    for n_pointer in t_myParent.first..t_myParent.last loop

 20      dbms_output.put_line( 'my parent ' || n_pointer || ': ' || t_myParent(n_pointer) );

 21    end loop;

 22  end;

 23  /

my parent 1: my father

my parent 2: my mother

 

PL/SQL 过程已成功完成。

 

SQL>

 

除此以外，还可以单独获取函数返回值中的指定元素：

 

SQL> declare

  2    myParent_1 varchar2(20);

  3    myParent_2 varchar2(20);

  4

  5    function fun_getParent( prm_province in my_family.province%type ) return type_parent

  6    is

  7      t_parent my_family.parent%type;

  8    begin

  9      select parent

 10        into t_parent

 11        from my_family

 12       where province = prm_province;

 13      return t_parent;

 14    exception

 15      when no_data_found then

 16        return null;

 17    end;

 18  begin

 19    myParent_1 := fun_getParent( 'Hebei' )(1);

 20    myParent_2 := fun_getParent( 'Hebei' )(2);

 21    dbms_output.put_line( 'my parent 1: ' || myParent_1 );

 22    dbms_output.put_line( 'my parent 2: ' || myParent_2 );

 23  end;

 24  /

my parent 1: my father

my parent 2: my mother

 

PL/SQL 过程已成功完成。

 

SQL>

 

若集合的指定下标中没有元素，则会抛出 COLLECTION_IS_NULL 异常。为了使你的程序更加健壮，记得捕获该异常。

 

4. 作为表中列的类型

 

使用内嵌表或 VARRAY 数组，你可以在表的某列上存储非原子的数据。比如你会如何存储下面的数据：

学号 (NUMBER)	姓名 (VARCHAR2)	家庭成员 (type_family)
10010	yuechaotian	father:
		mother:
		sister:
		brother:
10020	CoColi	father:
		mother:

 

当然，你可以建很多的家庭成员的列：father, mother, sister, brother, uncle, aunt, son, daught, grandfather, grandmother, ... etc. 虽然这样可能浪费很多存储空间。也可以只使用一列：

 

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

  2  /

 

类型已创建。

 

SQL> create table myStudents(id number(5), name varchar2(20), family type_family);

 

表已创建。

 

SQL> insert into myStudents values(10010, 'yuechaotian',

  2    type_family('father: my father', 'mother: my mother', 'sister: my sister', 'brother: my brother'));

 

已创建 1 行。

 

SQL> insert into myStudents values(10020, 'CoColi',

  2    type_family('father: her father', 'mother: her mother'));

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> set linesize 1000

SQL> select * from myStudents;

 

        ID NAME

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

FAMILY

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

 

     10010 yuechaotian

TYPE_FAMILY('father: my father', 'mother: my mother', 'sister: my sister', 'brother: my brother')

 

     10020 CoColi

TYPE_FAMILY('father: her father', 'mother: her mother')

 

 

SQL>

 

注意插入数据时需要使用默认的构造函数。

 

我们可以看到，VARRAY的使用方法很简单，但使用前就已经指定了该集合的最大长度。有时集合的长度是不固定的，此时就使用内嵌表会更好一点：

 

SQL> create type type_family_nested is table of varchar2(20);

  2  /

 

类型已创建。

 

SQL> create table myStudents_nested(id number(5), name varchar2(20), family type_family_nested)

  2    nested table family store as family_store;

 

表已创建。

 

SQL> insert into myStudents_nested values(10010, 'yuechaotian',

  2    type_family_nested('father: my father', 'mother: my mother', 'sister: my sister', 'brother: my brother'));

 

已创建 1 行。

 

SQL> insert into myStudents_nested values(10020, 'CoColi',

  2    type_family_nested('father: her father', 'mother: her mother'));

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from myStudents_nested;

 

        ID NAME

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

FAMILY

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

 

     10020 CoColi

TYPE_FAMILY_NESTED('father: her father', 'mother: her mother')

 

     10010 yuechaotian

TYPE_FAMILY_NESTED('father: my father', 'mother: my mother', 'sister: my sister', 'brother: my brother')

 

 

SQL>

 

VARRAY 数组和内嵌表的一个主要的不同就在做为列类型的时候。使用 VARRAY 作为列类型和内嵌表差不多，只是 VARRAY 在使用前必须指定最大长度，并且它里面的数据是存储在表内的（与其他列的数据存储在一起）；而内嵌表的数据是存储在表外的。Oracle 公司介绍说，VARRAY 列是用来存储“小”数组的，而内嵌表列是用来存储“大”数组的。

 

5. 作为对象类型（object type）的属性

 

比如我们想创建一个汽车对象，那么汽车的颜色有很多种，可以将该属性建为一个内嵌表：

 

SQL> create type type_colors is table of varchar2(20);

  2  /

 

类型已创建。

 

SQL> create type type_automobile is object(

  2    model varchar2(30),

  3    colors type_colors

  4  );

  5  /

 

类型已创建。

 

SQL>

 

此时因为还没有存储数据，所以不需要指定内嵌表的存储。当我想建立一个对象表（object table）时，就需要指定该列的存储了：

 

SQL> create table automobile of type_automobile

  2    nested table colors store as colors_store;

 

表已创建。

 

SQL> insert into automobile values( type_automobile('benz', type_colors('white', 'black')) );

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from automobile;

 

MODEL

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

COLORS

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

 

benz

TYPE_COLORS('white', 'black')

 

 

SQL>