PL/SQL支持集合的概念,在PL/SQL中集合就是一维数组,其可存储一个或多个元素,或者直接为空,通过索引可以访问这些元素,集合里的元素必须是同样的类型。集合类型属于复合类型,除了作为数据结构本身的需要,集合类型的使用有益于提高程序的效率。
#集合类型的类别
PL/SQL共有三类集合类型:关联数组, 嵌套表, 可变长数组。
##关联数组
关联数组是PL/SQL最早提供的集合类型,索引可以数值也可以是字符串,只能用于PL/SQL中,不能用于SQL及表的声明中,关联数字可以是厚密的,也可以是稀疏的。
声明关联数组类型的方式为:
--以字符串为索引
TYPE associative_array_type1 IS TABLE OF NUMBER
INDEX BY VARCHAR2(100);
--以数值为索引
TYPE associative_array_type2 IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
除了自己声明集合类型外,标准包里面也提供了一些预定义的集合类型,比如DBMS_UTILITY.maxname_array
,其是一个字符串类型的关联数组,字符串最大长为32767
。
##嵌套表
嵌套表的索引只可以是数值,可以用于PL/SQL中,SQL及表的声明中,嵌套表可以是厚密的,也可以是稀疏的,但一般都是稠密的。
声明嵌套表类型的方式为:
TYPE nested_table_type IS TABLE OF NUMBER;
嵌套表支持用MULTISET
操作符执行集合操作以及相等比较。
比如求collection1
与collection2
的差集:
collection3 := collection1 MULTISET EXCEPT collection2;
##可变长数组
同嵌套表一样,可变长数组的索引只可以是数值,可以用于PL/SQL中,SQL及表的声明中,但可变长数组只能是稠密的,在定义可变长数组的类型时,需要指定其允许存储的最大元素个数。
声明可变长数组类型的方式为:
TYPE varray_type IS VARRAY(10)OF NUMBER ;
#集合的初始化
一旦有了集合类型的声明,就可以声明集合变量,方式和其他类型变量一致。但是嵌套表和可变长数组类型的集合变量,使用前需要先初始化。
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nested_table1 nested_table_type;
nested_table2 nested_table_type;
BEGIN
nested_table1 := nested_table_type();
nested_table2 := nested_table_type(1, 2, 3);
END;
初始化通过构造函数完成,构造函数与类型同名,可以不带参数的执行构造一个空的集合,也可以带若干个对应元素类型的参数,这些参数将会直接被写入到集合中。
嵌套表和可变长数组类型的集合如果使用前未初始化,将会有异常抛出,但是关联数组没这个问题,可以声明后直接使用。
#基本的集合操作
##赋值
集合支持通过索引在单个元素上赋值,也支持整个集合之间赋值。
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
TYPE associative_array_type IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
nested_table nested_table_type;
associative_array1 associative_array_type;
associative_array2 associative_array_type;
BEGIN
nested_table := nested_table_type();
nested_table.extend();
nested_table(1) = 100;
associative_array1(2) := 100;
associative_array2 := associative_array1;
END;
关联数组类型的集合索引不需要从1
开始,也不需要执行EXTEND
扩充集合。嵌套表和可变长数组需要在对单个元素赋值前调用EXTEND
。
同类型的集合支持互相赋值。
##删除元素
删除所有元素,支持所有集合类型
collection.delete;
通过索引删除单个元素,仅支持关联数组和嵌套表
collection.delete(index);
通过索引删除范围内的元素,仅支持关联数组和嵌套表
collection.delete(begin_index, end_index);
删除集合末端一个或多个元素,仅支持嵌套表和可变长数组
collection.trim(number_of_element);
##迭代
根据集合是稠密还是稀疏的特点,集合适用于不同的迭代方法。
FOR
适合迭代稠密的集合
FOR index IN collection.FIRST .. collection.LAST
LOOP
DBMS_OUTPUT.put_line (collection (indx));
END LOOP;
如果collection
是稀疏的,则由于访问了不存在的索引,抛出异常NO_DATA_FOUND
。
WHILE
适合迭代稀疏的集合
index := collection.FIRST;
WHILE (index IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (collection (index));
index := collection.NEXT (index);
END LOOP;
如果需要倒序迭代集合,只需要把FIRST
和NEXT
改为LAST
和PRIOR
即可。
#集合与性能
合理的使用集合,可以提升程序的数据提取,修改性能。PL/SQL程序在执行过程中,PL/SQL执行引擎和SQL执行引擎之间的过于频繁的上下文切换会显著的降低程序的性能,通过集合的使用,可以降低上下文的切换的次数。
##BUlK COLLECT
BUlK COLLECT
可以让SELECT
实现在一次数据提取动作中提取多条数据,增加数据提取的速度。
BUlK COLLECT
可以使用于所有类型的集合,可以提取数据到多个集合,此时每个select
的列对应于一个集合,也可以提取到单个记录集合,生成的集合总是稠密的,索引也总是从1
开始,如果没有数据,则集合为空。
提取所有符合条件的数据到集合:
SELECT *
BULK COLLECT INTO collection_name
FROM table_name;
为了防止太多的数据被一次性提取到内存中,可以结合通过光标的方式结合limit
使用
OPEN cursor_name;
LOOP
FETCH cursor_name
BULK COLLECT INTO collection_name
LIMIT number_of_limit;
EXIT WHEN collection_name.COUNT = 0;
END LOOP
#FORALL
FORALL
可以通过集合快速的修改多条数据,数据的批量修改仅需要一次上下文的切换。凡是在循环中使用DML语句的情况,基本都可以通过FORALL
重写,FORALL
并不是循环,而是语句,每个FORALL
语句中只能有一条DML语句。
与FOR
循环类似,FORALL IN
自动声明迭代器变量index
,要求集合collection_name
本身是稠密的。
FORALL index IN 1 .. collection_name.COUNT
UPDATE table_name
SET column_name = value
WHERE condition_column_name = collection_name(index);
如果集合本身是稀疏的,则需要使用INDICES OF
:
FORALL index IN INDICES OF collection_name
UPDATE table_name
SET column_name = value
WHERE condition_column_name = collection_name(index);
类似的语法结构还有VALUES OF
,也可以实现对稀疏集合的访问。
##DML错误的处理
如果FORALL
执行中,如果DML在执行中途发生了错误,则相应错误的异常会被抛出,之后的DML将不会再被执行,比如修改1000条数据,在修改第500条时发生了错误,剩下的500条修改将不会再被执行。
如果想要在遇到错误时继续,则可使用SAVE EXCEPTIONS
子句,此时错误信息将会被存储到SQL%BULK_EXCEPTIONS
中,之后的DML将继续得到执行,直到所有的DML都被执行后,ORA-24381
异常被抛出。
可以通过在异常处理区域捕获异常的方式,获取错误信息
BEGIN
FORALL index IN 1 .. collection_name.COUNT SAVE EXCEPTIONS
UPDATE table_name
SET column_name = value
WHERE condition_column_name = collection_name(index);
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -24381
THEN
FOR index IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line (
SQL%BULK_EXCEPTIONS (index ).ERROR_INDEX
|| ‘: ‘
|| SQL%BULK_EXCEPTIONS (index ).ERROR_CODE);
END LOOP;
ELSE
RAISE;
END IF;
END;