OraclePLSQL开发基础（第十七弹：集合类型）

OraclePLSQL开发基础（第十七弹：集合类型）
集合是PL/SQL提供的用来同时处理多个数据的一种数据结构。

如果说记录是一个单行多列的数据结构，那么集合就是一种单列多行的数据结构。

集合简介
集合类似于高级语言中的列表或一维数组，主要用来存储具有相同类型的元素的有序集合，每一个元素都有唯一的下标来标识当前元素在集合中的位置。

PL/SQL中提供了如下三种类型的集合：
- 索引表：也称为关联数组，这种类型的集合可以通过数据或字符串作为下标来查找其中的元素，类似于其他语言中的哈希表，索引表是一种仅在PL/SQL中使用的数据结构。

- 嵌套表：使用有序数据作为嵌套表的下标，可以容纳任意个数的元素。

嵌套表与索引表最大的区别在于可以定义嵌套表类型，吧嵌套表存储到数据库中，并能通过SQL语句进行操作。

- 变长数组：在定义时保存固定数量的元素，但可以在运行时改变其容量。

变长数据与嵌套表一样，使用有序数字作为下标，也可以保存到数据库中，但是不如嵌套表灵活。

可以看到这3重集合之间最大的区别在于，索引表只能在PL/SQL 中使用，如果需要在内存中保存和维护列表，则优先选择索引表。

如果集合的内容还要存储到数据库中，那么可以在嵌套表和变长数据之间进行选择。

嵌套表是对索引表的扩充，添加了额外的集合方法扩展了索引表的功能，并且嵌套表也可以存储在数据库表中，直接使用SQL进行操作。

在PL/SQL中，嵌套表和索引表统称为PL/SQL表。

索引表
定义索引表
使用索引表需要了解的关键点：
- 索引表不需要进行初始化，没有构造语法，在为其赋值之前不需
要分配初识空间，因此不需要动态地扩展其容量。

- 索引表不仅可以使用数字作为索引下标，而且可以使用变长的字符串来索引其中的元素。

- 当使用数字类型作为索引下标时，索引键可以为正数、负数或0，并且数据可以不连续。

索引表的定义语法如下：
TYPE type_name AS TABLE OF element_type [NOT NULL]
INDEX BY [ PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size)];
•1
•2
下面是一些较常见的索引表定义语法：
-- 雇佣日期索引表集合
TYPE hiredate_idxt IS TABLE OF DATE
INDEX BY PLS_INTEGER;
-- 部门编号集合
TYPE deptno_idxt IS TABLE OF dept.deptno%TYPE NOT NULL INDEX BY PLS_INTEGER;
--记录类型的索引表，这个结构允许在PL/SQL程序中创建一个本地副本
TYPE emp_idxt IS TABLE OF emp%ROWTYPE
INDEX BY NATURAL;
-- 由部门名称标识的部门记录的集合
TYPE deptname_idxt IS TABLE OF dept%ROWTYPE
INDEX BY dept.dname%TYPE;
-- 定义集合的集合
TYPE private_collection_tt IS TABLE OF deptname_idxt
INDEX BY VARCHAR2(100);
•1
•2
•4
•5
•6
•7
•8
•9
•10
•11
•12
•13
•14
•15
操纵索引表
如：
DECLARE
TYPE idx_table IS TABLE OF VARCHAR (12)
INDEX BY PLS_INTEGER; --定义索引表类型
v_emp idx_table; --定义索引表变量
BEGIN
v_emp (1) := '史密斯'; --随机的为索引表赋值v_emp (20) := '克拉克';
v_emp (40) := '史瑞克';
v_emp (-10) := '杰瑞';
END;
•1
•2
•3
•4
•5
•7
•8
•9
•10
可以使用EXISTS语句检查索引表元素是否存在值，如：
if v_emp.EXISTS(8) THEN
DBMS_OUTPUT.PUT_LINE(v_emp(8));
END IF;
•1
•2
•3
还可以使用字符串作为索引表的下标类型，如：
DECLARE
--定义以VARCHAR2作为索引键的索引表
TYPE idx_deptno_table IS TABLE OF NUMBER (2)
INDEX BY VARCHAR2 (20);
--声明记录类型的变量
v_deptno idx_deptno_table;
BEGIN
--为索引表赋值
v_deptno ('财务部') := 10;
v_deptno ('研究部') := 20;
v_deptno ('销售部') := 30;
--引用索引表的内容
DBMS_OUTPUT.put_line ('销售部编号为：' || v_deptno ('销售部'));
END;
•1
•2
•4
•5
•6
•7
•8
•9
•10
•11
•12
•13
•14
也可以在索引表的定义中使用%TYPE或%ROWTYPE，如：DECLARE
--定义记录类型的索引表，以dname作为索引键类型
--dname是VARCHAR2(14)类型
TYPE idx_dept_table IS TABLE OF dept%ROWTYPE INDEX BY dept.dname%TYPE;
--声明记录类型的变量
v_dept idx_dept_table;
--定义一个游标，用来查询dept表
CURSOR dept_cur
IS
SELECT *
FROM dept;
BEGIN
--使用游标FOR循环打开游标，检索数据
FOR deptrow IN dept_cur
LOOP
--为索引表中的元素赋值
v_dept (deptrow.dname) := deptrow;
--输出部门的LOC列信息
DBMS_OUTPUT.put_line (v_dept (deptrow.dname).loc);
END LOOP;
END;
•1
•2
•3
•4
•5
•6
•7
•8
•9
•10
•11
•12
•13
•14
•15
•16
•17
•18
•19
•20
•21
•22
嵌套表
定义嵌套表
嵌套表是对索引表的扩展，与索引表最大的不同在于嵌套表可以
存储到Oracle数据库表中，而索引表仅仅是内存表。

除此之外，使用嵌套表时必须使用其构造语法对嵌套表进行初始化。

嵌套表没有INDEX BY子句，这是与索引表之间最明显的区别，因为嵌套表必须用有序的关键字创建，而且关键字不能为负数。

嵌套表的定义语法如下所示：
TYPE type_name AS TABLE OF element_type [NOT NULL];
•1
常见的嵌套表的定义和嵌套表变量的声明实现：
DECLARE
TYPE dept_table IS TABLE OF dept%ROWTYPE; --部门信息嵌套表
TYPE emp_name_table IS TABLE OF VARCHAR2 (20); --员工名称嵌套表
TYPE deptno_table IS TABLE OF NUMBER (2); --部门编号嵌套表
dept_info dept_table; --声明嵌套表变量--声明并初始化嵌套表变量
emp_name_info emp_name_table := emp_name_table ('张小三', '李斯特');
deptno_info deptno_table := deptno_table (20, 30, 40);
BEGIN
NULL;
END;
•1
•2
•3
•4
•5
•6
•7
•8
•9
•10
•11
操纵嵌套表
由于在嵌套表使用之前必须进行构造，未构造的嵌套表被自动地赋初始值NULL，这与索引表不同，索引表只是一种程序上的结构，而嵌套表是一种对象的类型，尽管它们都是PL/SQL表类型，但是在结构上有明显的区别。

可以通过IS NULL来判断嵌套表是否已经被构造来使用嵌套表。

如下为多种构造并访问嵌套表内容的方法：
DECLARE
TYPE emp_name_table IS TABLE OF VARCHAR2 (20); --员工名称嵌套表
TYPE deptno_table IS TABLE OF NUMBER (2); --部门编号嵌套表
deptno_info deptno_table;
emp_name_info emp_name_table := emp_name_table ('张小三', '李斯特');
BEGIN
DBMS_OUTPUT.put_line ('员工1：' || emp_name_info (1)); --访问嵌套表元素
DBMS_OUTPUT.put_line ('员工2：' || emp_name_info (2));
IF deptno_info IS NULL --判断嵌套表是否被初始化
THEN
deptno_info := deptno_table ();
END IF;
deptno_info.EXTEND(5); --扩充元素的个数FOR i IN 1 .. 5 --循环遍历嵌套表元数个
数
LOOP
deptno_info (i) := i * 10;
END LOOP;
--显示部门个数
DBMS_OUTPUT.put_line ('部门个数：' || deptno_info.COUNT);
END;
•1
•2
•3
•4
•5
•6
•7
•8
•9
•10
•11
•12
•13
•14
•15
•16
•17
•18
•19
•20
为了向deptno_info嵌套表中插入元素，必须首先使用嵌套表的EXTEND方法扩充指定的元素个数，然后才能通过下标进行访问。

也可以在构造语法中使用NULL来代替元素值，因此下面的语法与
EXTEND具有相同的效果：
IF deptno_info IS NULL --判断嵌套表是否被初始化
THEN
deptno_info := deptno_table (NULL,NULL,NULL,NULL,NULL);
END IF;
•1
•2
•3
•4
数据库中的嵌套表
为了让嵌套表类型能在数据表中使用，要求嵌套表类型必须保存到数据字典中，因此需要使用CREATE TYPE语句创建一个持久的嵌套表类型，语法如下：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL];
•1
•2
如：
--1.创建嵌套表类型
CREATE TYPE empname_type IS TABLE OF VARCHAR2(20);
/
--2.创建数据表时指定嵌套表列，同时要使用STORE AS指定嵌套表的存储表
CREATE TABLE dept_nested
(
deptno NUMBER(2), --部门编号
dname VARCHAR2(20), --部门名称
emplist empname_type --部门员工列表
) NESTED TABLE emplist STORE AS empname_table;
•1
•2
•3
•4
•5
•6
•7
•8
•9
•10
数据表定义的末尾要使用NESTED TABLE语句给嵌套表指明一个存储表的名字，用来存储嵌套表里的数据。

表中嵌套表列的内容是但对进行存放的，Oracle将嵌套表列的内容存储到创建表时指定的存储表中。

数据库表中的列实际上是指向对存储表的一个引用，类似于一个REF变量。

存储表里的内容是不能直接访问的，必须通过SQL语句来操纵存储表中的数据。

如：
DECLARE
emp_list empname_type
:= empname_type ('史密斯', '杰克', '马丁', '斯大林', '布什', '小平');
BEGIN
--可以在INSERT语句中传入一个嵌套表实例
INSERT INTO dept_nested
VALUES (10, '国务院', emp_list);
--也可以直接在INSERT语句中实例化嵌套表
INSERT INTO dept_nested
VALUES (20, '财务司', empname_type ('李林', '张杰', '马新', '蔡文'));
--从数据库表中查询出嵌套表实例
SELECT emplist INTO emp_list FROM dept_nested WHERE deptno=10;
--对嵌套表进行更新，然后使用UPDATE语句将嵌套表实例更新回数据库
emp_list (1) := '少校';
emp_list (2) := '大校';
emp_list (3) := '中校';
emp_list (4) := '学校';
emp_list (5) := '无效';
emp_list (6) := '药效';
--使用更改过的emp_list更新嵌套表列
UPDATE dept_nested
SET emplist = emp_list
WHERE deptno = 10;
END;
•1
•2
•3
•4
•5
•6
•7
•8
•9
•10
•11
•12
•13
•14
•15
•16
•17
•18
•19
•20
•21
•22
•23
•24
注意，不能再WHERE子句中使用嵌套表，以及其他一些隐含需要比较的地方，不如ORDER BY、GROUP BY、DISTINCT子句中都不能使用嵌套表。

要删除数据字典中的嵌套表类型，可以：
DROP TYPE empname_type;
•1
如果类型已经被数据表使用，必须先把dept_nested删除才行。

变长数组
定义变长数组
声明语法如下：
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
•1
•2
如：
TYPE projectlist IS VARRAY (50) OF VARCHAR2 (16);
•1
与嵌套表一样，变长数组在未初始化之前，本身为NULL，因此必须使用构造函数进行初始化。

操纵变长数组
如：
DECLARE
TYPE projectlist IS VARRAY (50) OF VARCHAR2 (16); --定义项目列表变长数组
TYPE empno_type IS VARRAY (10) OF NUMBER (4); --定义员工编号变长数组
--声明变长数组类型的变量，并使用构造函数进行初始化
project_list projectlist := projectlist ('网站', 'ERP', 'CRM', 'CMS');
empno_list empno_type; --声明变长数组类型的变量
BEGIN
DBMS_OUTPUT.put_line (project_list (3)); --输出第3个元素的值
project_list.EXTEND; --扩展到第5个元素
project_list (5) := 'WORKFLOW'; --为第5个元素赋值
empno_list := --构造empno_list empno_type (7011, 7012, 7013, 7014, NULL, NULL, NULL, NULL, NULL, NULL);
empno_list (9) := 8011; --为第9个元素赋初值
DBMS_OUTPUT.put_line (empno_list (9)); --输出第9个元素的值
END;
•1
•2
•3
•4
•5
•7
•8
•9
•10
•11
•12
•13
•14
•15
数据库中的变长数组
如：
--创建一个变长数组的类型empname_varray_type，用来存储员工信息
CREATE OR REPLACE TYPE empname_varray_type IS VARRAY (20) OF VARCHAR2 (20);
/
CREATE TABLE dept_varray --创建部门数据表
(
deptno NUMBER(2), --部门编号
dname VARCHAR2(20), --部门名称
emplist empname_varray_type --部门员工列表
);
•1
•2
•3
•4
•5
•6
•7
•9
可以看到，在使用变长数组的列中，并没有指定STORE AS 来指定变长数组的存储表，这是因为变长数组的存储组织与数据库行是完全相同的，数据是作为数据库表数据进行存储的，因而不需要指定一个存储表的名字进行存储。

在表中操纵变长数组数据列，如：
DECLARE --声明并初始化变长数组
emp_list empname_varray_type
:= empname_varray_type ('史密斯', '杰克', '汤姆', '丽沙', '简', '史太龙');
BEGIN
INSERT INTO dept_varray
VALUES (20, '维修组', emp_list); --向表中插入变长数组数据INSERT INTO dept_varray --直接在INSERT语句中初始化变长数组数据
VALUES (30, '机加工',
empname_varray_type ('张三', '刘七', '赵五', '阿四', '阿五', '阿六'));
SELECT emplist
INTO emp_list
FROM dept_varray
WHERE deptno = 20; --使用SELECT语句从表中取出变长数组数据
emp_list (1) := '杰克张'; --更新变长数组数据的内容UPDATE dept_varray
SET emplist = emp_list
WHERE deptno = 20; --使用UPDATE语句更新变长数组数据
DELETE FROM dept_varray
WHERE deptno = 30; --删除记录并同时删除变长数组数据
END;
•1
•2
•3
•4
•5
•6
•7
•8
•9
•10
•11
•12
•13
•14
•15
•16
•17
•18
•19
•20
选择集合类型
这3中集合类型各有其优点，应该如何选择集合类型要根据应用程序的需求和集合的特性来全面考虑。

首先看一下嵌套表与索引表，这两种类型统称为PL/SQL表，相似之处如下：
- 嵌套表自索引表扩展而来，因此嵌套表包含索引表的所有表属性。

- 嵌套表与索引表都是使用下标对集合中的元素进行访问。

- 嵌套表与索引表的数据机构具有相同的结构。

不同点如下：
- 嵌套表可以存储到数据库中，而索引表不能。

- 嵌套表的合法的下标范围是1~214748361，下标不能为负数；而索引表可以为负下标，范围为-2147483647~2147483647。

- 索引表在每次调用语句块或在包初始化时在内存中自动构建，能够保存容量不固定的信息，因为它的长度是大小可变的，其值不能为NULL；而嵌套表是一种对象类型，如果不显式使用构造函数，则其值为NULL，可以用IS NULL进行检查。

- 嵌套表可以使用其他的方法，比如EXTEND和TRIM，而索引表不需要。

- PL/SQL会自动在主机数组和索引表之间进行转换，而嵌套表不能在主机数组之间进行转换。

如果需要将集合类型保存到数据库中，可以在变长数组与嵌套表之间选择，他们的相同之处如下：
- 变长数组与嵌套表都是用下标符号对单个元素进行访问，在使用前都必须使用构造函数进行初始化。

- 都可以存储在数据库表中，都可以应用集合方法。

区别如下：
- 变长数组一旦声明，元素数目就被固定，而嵌套表没有一个明确的大小上限。

- 当存储到数据库中时，变长数组保持了元素的排序和下标值，而嵌套表则不同。

因此基本的结论是：如果是只需要在PL/SQL中使用的集合，且元素个数较少，则优先考虑索引表。

而如果要存储到数据库中，则需要选择嵌套表。

如果数据元素可以固定，则优先考虑使用变长数组。

在实际项目中，使用索引表较多，因为较少将集合结构存储到数据库中。