PL/SQL collection（2）— 使用 - ORACLE PROGRAMING

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

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';

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;

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

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;

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 | := <default_value> ]

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: <my father>

mother: <my mother>

sister: <my sister>

brother: <my brother>

10020
CoColi
father: <her father>

mother: <her 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>