Oracle集合类型

所谓集合是一种类似于列表或者一维数组的数据结构。PL/SQL提供了三种集合类型：关联数据组（索引表），嵌套表和VARRAY（可变长数组）。

1.集合类型

1.1关联数组

关联数组（也称为索引表）是一组键值对。每个密钥都是唯一的，并且被用于定位相应的值。键可以是整数或字符串。只能用于PL/SQL环境。

1.2嵌套表

从概念上讲，嵌套表像一个元素数量任意的一维数组。

在数据库中，嵌套表是存储一组值的列类型，数据库存储嵌套表的行是没有特定顺序的。当你从数据中提取嵌套表到PL/SQL变量时，该行给出连续从1开始的下标。通过这些类似数组下标访问独立的行。

嵌套表不同于数组的重要方面：

• 数组需要声明元素的个数，而嵌套表不需要。嵌套表的大小可以动态增加。
• 数组总是密集的，嵌套表刚开始是密集的，但是后面有可能会变成稀疏的。因为你会从嵌套表中删除元素。

1.3 可变长的数组

可变长的数组是一个VARRAY数据类型的集合。当你声明VARRAY类型的时候，就必须指定同时指定它能够包含的最大元素个数。VARRAY可以包含可变数据的元素，从零到最大值。VARRAY索引有一个固定定的下限1和一个可扩展的上限。和嵌套表类型一样的是，它们都可以用于PL/SQL和数据库。但是和嵌套表不一样的是，在向VARRAY中保存数据或者提取数据时，它的元素是有序的。

2.声明集合类型

在使用一个集合之前，我们必须先声明它。有两种方法可以申明一个集合类型：

• 通过TYPE语句在一个PL/SQL程序中声明集合类型。
• 通过CREATE TYPE语句在数据中定义一个嵌套表或者VARRAY类型，这个类型就是一个模式级别的对象。这种类型就可以用作数据库表的列的数据类型，可以用作对象类型的属性，也可以用于声明PL/SQL变量。

2.1声明关联数组

关联数组的TYPE语句的语法如下：

TYPE table_type_name IS TABLE OF datatype [ NOT NULL] INDEX BY index_type;

其中：

table_type_name是你所创建的集合类型的名字，datatype是集合中唯一一列的数据类型，index_type是用来组织集合内容的索引的数据类型。而集合唯一以列的数据类型可以是下面这些：

• 标量数据类型：任何被PL/SQL支持的标量数据类型，比如VARCHAR2，CLOB，POSITIVE，DATE，或者BOOLEAN。
• 锚定数据类型：这种数据类型是从一个数据库表的列，之前已经定义的变量或者带有%TYPE属性的游标表达式推导出来的数据类型。我们也可以定义用%ROWTYPE声明或者根据一个用户定义的记录类型来定义一个记录的集合。
• 复杂的数据类型：从Oracle 9i数据库R2版本开始，你也可以把对象类型和集合类型作为集合的数据类型。

集合语法中的index_type定义索引下标的数据类型。在Oracle 9i数据库版本R2之前，只能是INDEX BY PLS_INTEGER。从Oracle 9i数据库版本R2开始，INDEX BY的数据类型可以是BINARY_INTEGER、及它的子类型、VARCHAR2(N)或者VARCHAR2列或变量的%TYPE锚定类型。

2.2声明嵌套表

可以数据库内或者PL/SQL代码块中声明嵌套表类型。

在数据库内创建一个嵌套表类型：

CREATE [OR REPLACE] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];

删除数据库内的嵌套表类型：

DROP TYPE type_name [FORCE];

在PL/SQL中声明一个嵌套表类型：

TYPE type_name IS TABLE OF element_datatype [ NOT NULL ];

其中：

• OR REPLACE：允许我们重创建一个已经存在的类型。通过在语法中加上REPLACE的方式来重建类型，而不是先删除后再重新创建，可以把所有已经授予的权限都完整的保留下来。
• type_name：一个合法的SQL或者PL/SQL标志符。这也是我们以后在声明变量或者列时会用到的标识符。
• element_datatype:这是集合元素的数据类型。集合内所有元素都是一种类型的，可以是大部分标量数据类型、对象类型、或者REF对象类型。如果集合的元素是对象，对象类型本身不能再带有一个集合属性。如果你创建了一个其元素是RECORD类型的集合，记录的字段只能是标量或者是独享。明确的不可用于集合的数据类型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL数据类型)。
• NOT NULL：表明这种类型的变量不能有任何空元素。不过，集合本身可可以是原子级的空（未初始化）。
• FORCE：这个关键字告诉数据库的是，当要删除这个类型时，就算是其他类型中还有对这个类型的引用，也要强行删除这个类型。比如，如果在一个对象类型的定义中用到了某个特殊的集合类型，你可以使用FORCE关键字来强行删除这个集合类型。

2.3声明VARRAY

和嵌套表类型的声明一样，可以数据库内或者PL/SQL代码块中声明VARRAY类型。

在数据库内创建一个VARRAY类型：

CREATE [OR REPLACE] TYPE type_name AS | ISVARRAY(max_elements)OF element_datatype [ NOT NULL ];

删除数据库内的VARRAY类型：

DROP TYPE type_name [FORCE];

在PL/SQL中声明一个VARRAY类型：

TYPE type_name ISVARRAY(max_elements)OF element_datatype [ NOT NULL ];

其中：

• OR REPLACE：允许我们重创建一个已经存在的类型。通过在语法中加上REPLACE的方式来重建类型，而不是先删除后再重新创建，可以把所有已经授予的权限都完整的保留下来。
• type_name：一个合法的SQL或者PL/SQL标志符。这也是我们以后在声明变量或者列时会用到的标识符。
• element_datatype:这是集合元素的数据类型。集合内所有元素都是一种类型的，可以是大部分标量数据类型、对象类型、或者REF对象类型。如果集合的元素是对象，对象类型本身不能再带有一个集合属性。如果你创建了一个其元素是RECORD类型的集合，记录的字段只能是标量或者是独享。明确的不可用于集合的数据类型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL数据类型)。
• NOT NULL：表明这种类型的变量不能有任何空元素。不过，集合本身可可以是原子级的空（未初始化）。
• max_elements:VARRAY中元素的最大数量，这个值一旦声明就不能更改。
• FORCE：这个关键字告诉数据库的是，当要删除这个类型时，就算是其他类型中还有对这个类型的引用，也要强行删除这个类型。比如，如果在一个对象类型的定义中用到了某个特殊的集合类型，你可以使用FORCE关键字来强行删除这个集合类型。

3.集合变量的声明和初始化

3.1集合变量的声明

一旦我们创建好了集合类型，我们就可以根据这个集合类型声明该类型的变量。一个集合变量声明格式如下：

collection_name collection_type [:=collection_type(...)];

其中，collection_name是集合变量的名字，collection_type具有两层含义，它即代表着一个先前已经声明的集合类型的名字，同时也代表着（如果是嵌套表或者VARRAY的话）和该类型同名的构造函数。

构造函数的名字和类型的名字是相同的，并且接收一个用逗号分隔的元素列表作为参数。如果我们声明的是一个嵌套表或者VARRAY变量，我们在使用之前必须要先对这个变量进行初始化。

3.2集合变量的初始化

对于嵌套表类型集合变量和VARRAY类型集合变量，在使用集合变量之前必须要进行初始化，而对于关联数组类型不需要初始化。下面主要讨论嵌套表和VARRY的初始化。

3.2.1通过构造函数的显示初始化

通过构造函数显示地给集合变量初始。例如：

declare

vnt_employee nt_employee :=nt_employee(); --不带参数的构造函数初始化

vnt_employee nt_employee :=nt_employee('张三','李四','王五'); --带参数的构造函数初始化

begin

null;

end;

3.2.2直接赋值时的隐式初始化

如果两个集合实例是基于同一集合类型，我们可以把其中一个实例的全部内容拷贝给另一个，这就相当于进行了初始化。例如：

declare

vnt_employee nt_employee :=nt_employee('James','Lucy','Jordan');

vnt_foregin_employee nt_employee;

begin

vnt_foregin_employee := vnt_employee；

end;

3.2.3通过FETCH操作的隐式初始化

在使用FETCH或者 SELECT INTO语句从数据库提取提取一个集合并保存到一个集合变量时，集合变量会自动初始化，就像直接赋值一样。

declare

vnt_colors nt_color;

begin

select colors into vnt_colors from color_models; --表color_models的color列是嵌套表类型。

end;

3.2.4通过BULK COLLECT语句的隐式初始

使用BULK COLLECT INTO语句批量提取数据并保存到一个集合变量，集合变量会自动初始化，就像直接赋值一样。

declare

vnt_employee nt_employee; --未初始化

begin

selecte.ename bulk collect intovnt_employeefrom emp e;

end;

declare

cursor cur_employee is select e.ename from emp e;

vnt_employee nt_employee; --未初始化

begin

open cur_employee;

fetch cur_employee bulk collect into vnt_employee;

close cur_employee;

end;

4.集合方法

Oracle提供了提供许多内置的函数和过程可以用于获取集合的信息或者修改集合的内容，这些方法也叫做集合方法。下面给出这些方法的完整列表：

方法（函数或者过程）	说 明
COUNT函数	返回集合中现有元素的数量
DELETE过程	从集合中移除一个或者多个元素。如果不是重复移除，会减少COUNT的值，对于VARRAY，你只能删除集合的所有元素
EXISTS函数	根据某个指定的元素是否已经在集合中，返回TURE或者FALES
EXTEND过程	增加嵌套表或者VARRAY中元素的个数，同时增加COUNT的值
FIRST、LAST函数	返回可用的最小（FIRST）和最大（LAST）集合下标
LIMIT函数	返回VARRAY中允许ude最大元素数量
PRIOD、NEXT函数	返回紧挨着指定的下标之前(PRIOD)或者之后(NEXT)的下标值。你应该总是用PRIOD和NEXT在集合内遍历，尤其在使用稀疏（或者可能是稀疏）集合时更是如此
TRIM过程	从集合的尾部（定义的最大下标）移除集合元素

之所以把这些过程叫做方法，是因为使用这些集合内置程序的语法不同于调用过程和函数的正规语法。

5.集合类型对比

Oracle 集合类型对比

属性	关 联 数 组	嵌 套 表	可变长数组
维度	一维	一维	一维
是否可用于SQL	不可用	可用	可用
是否可作为表中列的数据类型	不可用	可以；数据是在“行外”保存的（一个独立的表）	可以；数据保存在“行内”（在同一个表中）
未初始化时的状态	空（不能是NULL）；元素是为定义	自动就是NULL的；对元素的引用是非法的	自动就是NULL；对元素的引用是非法的
初始化	在声明时自动完成	通过构造函数，或者赋值，或者fetch操作完成	通过构造函数，或者赋值，或者fetch操作完成
在PL/SQL中元素的引用方式	BINARY_INTEGER以及其子类型	VARCHAR2（Oracle 9i数据库R2版本或者更高版本）	1到2 147 483 647间的整数
是否稀疏	是	开始不是；经过删除后就成稀疏了	不是
是否有界	无界	可以扩展	有界
可以随时对任意一个元素赋值	可以	不可以，可以需要用EXTEND进行扩展	不可以；可以用EXTEND进行扩展，而且扩展时不能超出上边界
扩展的方法	给一个新下标指向的元素赋值	使用内置的EXTEND过程（或者TRIM进行压缩），没有预定义的最大值	使用内置EXTEND过程（或者TRIM进行压缩），但是最大只能到声明的最大尺寸
可以比较相等与否	不可以	可以，要求是Oracle 10g或者以后的版本	不可以
是否可以通过集合操作符进行操作	不可以	可以，要求是Oracle 10g或者以后的版本	不可以
存取数据时是否会保留顺序或者下标	N/A	不保留	保留

6.集合示例

6.1关联数组示例

declare
type nt_foregn_employee is table of varchar2(30) index by binary_integer;
vnt_foregn_employees nt_foregn_employee;
v_row number;
begin
vnt_foregn_employees(-230002) := 'SCOTT';
vnt_foregn_employees(-23) := 'JONES';
vnt_foregn_employees(1) := 'ALLEN';
vnt_foregn_employees(5934) := 'CLARK';
vnt_foregn_employees(13342) := 'ADAMS';
vnt_foregn_employees(8234223) := 'KING';

--使用FIRST方法获取集合中的一个行号
v_row := vnt_foregn_employees.first;

while (v_row is not null) loop
dbms_output.put_line(vnt_foregn_employees(v_row));
v_row := vnt_foregn_employees.next(v_row);
end loop;

end;

在稀疏集合中，经常需要使用NEXT方法遍历集合，提取数据。

6.2嵌套表示例

declare
type nt_employee is table of emp%rowtype;
vnt_employees nt_employee := nt_employee(); --构造函数显示初始化
c_big_number number := power(2, 31);
l_start_time pls_integer;

cursor cur_employee is
select * from emp;
vrt_employees cur_employee%rowtype;

begin
open cur_employee;
loop
fetch cur_employee
into vrt_employees;
exit when cur_employee%notfound;
vnt_employees.extend;
vnt_employees(vnt_employees.last) := vrt_employees;
end loop;
close cur_employee;

dbms_output.put_line(vnt_employees.count);

end;

6.3VARRAY实例

create or replace type nt_course is varray(5) of varchar2(100);
create table students( student_name varchar2(20) , cource nt_course);

declare
vnt_nt_courses nt_course := nt_course();
begin
vnt_nt_courses.extend(2);
vnt_nt_courses(1) := 'English';
vnt_nt_courses(2) := 'Chinese';

insert into students
(student_name, cource)
values
('chiclewu', vnt_nt_courses);

end;

可以使用TABLE函数把一个集合映射成数据库表.例如，要获取student表中课程列的记录。

select * from table (select s.cource from students s);

参考：

Oracle PL/SQL程序设计（第五版） Steven Feuersterin & Bill Pribyl著 张晓明译