人大金仓数据库KingbaseES PLSQL中的批量处理—FORALL介绍

金仓数据库KingbaseES PLSQL中的批量处理—FORALL介绍

关键字：

KingbaseES、批量处理、FORALL、人大金仓、KingbaseES

在信息化时代的背景下，数据库在我们的生活和工作中扮演着非常重要的角色。无论是个人数据管理还是企业信息管理，都可能会涉及到对数据的存储和管理（包括增、删、改、查）。因此，一个最让开发人员关注的话题就是：怎样能够更加快速、高效的处理这些数据？在这里，我们将为您介绍PL/SQL中一个十分重要的优化特性： FORALL子句。

使用批量处理的原因

在PL/SQL代码块中，不可避免的会包含一条或多条SQL语句，这些语句将发送到SQL引擎进行处理。这一系列操作会涉及到PL/SQL引擎和SQL引擎之间的交互（又称上下文切换），而频繁的切换将影响执行的速度，导致响应时间过长。为了提升性能，我们应该避免这种频繁的切换。

KingbaseES为PL/SQL提供了批量处理多行SQL语句的功能，而无需多次往返于两个引擎之间，节省了时间和资源。PL/SQL中的批量处理主要提供了BULK COLLECT和FORALL两个功能子句，BULK COLLECT用于对查询进行加速，而FORALL用于对数据操作（DML）进行加速。本文将主要介绍FORALL子句的应用。

FORALL的使用说明书

FORALL是PL/SQL中批量处理多行SQL的一个重要特性，使用FORALL语句时，PL/SQL会先将一个或多个集合的所有元素与一条DML语句进行绑定，然后再将该语句发送到SQL引擎中。FORALL的基本语法如下：

FORALL index IN

bound_clause

[ SAVE EXCEPTIONS ]

sql_statement;

其中，index被定义为集合的索引值；

bound_clause指明了要迭代的总次数，一共包括三种形式：

• lower_bound ... upper_bound -----与for循环类似，用于指明操作开始和结束的索引值，必须是一个有效的连续索引值范围。

• INDICES OF indexing_collection -----引用特定集合元素的下标。

• VALUES OF indexing_collection -----引用特定集合元素的值。

SAVE EXCEPTION是一个可选项，它用于保存执行sql_satement部分发生的所有异常，并保证在发生异常时语句能够正常执行。

sql_satement是FORALL的主体部分，有且只能包含一条单独的DML语句，例如插入、更新、删除或合并。

使用FORALL加速DML执行

从FORALL的基本语法中可以看到，FORALL语句有一个迭代的过程，即通过索引值来迭代集合中的元素。虽然FORALL与FOR循环都涉及迭代，但它们在本质上是完全不同的。首先，从语法层面来看，FORALL不包含LOOP和END LOOP子句。但除了语法上的不同，它们还有很大的区别。接下来，让我们通过一个例子来展开说说它们的不同吧！

CREATE TABLE student (id INTEGER, name VARCHAR2(15));

CREATE TABLE student_1 (id INTEGER, name VARCHAR2(15));

\set SQLTERM /

DECLARE

TYPE stu_ids IS TABLE OF student_temp.id%TYPE INDEX BY PLS_INTEGER;

TYPE stu_names IS TABLE OF student_temp.name%TYPE INDEX BY PLS_INTEGER;

id stu_ids;

name stu_names;

c CONSTANT NUMBER := 10000;

t1 TIMESTAMP;

t2 TIMESTAMP;

t3 TIMESTAMP;

BEGIN

--通过FOR循环，向集合id和name中插入10000条数据

FOR i IN 1..c LOOP -- name collections

id(i) := i;

name(i) := 'student. ' || TO_CHAR(i);

END LOOP;

t1 := clock_timestamp(); --获取当前的日期和时间

--通过FOR循环，将10000个插入语句从PL/SQL逐条发送到SQL

FOR i IN 1..c LOOP

INSERT INTO student VALUES (id(i), name(i));

END LOOP;

t2 := clock_timestamp(); --获取当前的日期和时间

--通过FORALL语句，将集合中的元素与一条SQL语句绑定，再一并发送到SQL

FORALL i IN 1..c

INSERT INTO student_1 VALUES (id(i), name(i));

t3 := clock_timestamp(); --获取当前的日期和时间

raise notice 'Execution Time';

raise notice '--------------------------';

raise notice 'Time Spent in FOR LOOP: %', t2 - t1; --计算使用FOR循环花费的时间

raise notice 'Time Spent in FORALL : %', t3 - t2; --计算使用FORALL花费的时间

END;

/

输出结果如下：

在这个例子中，我们使用计时器分别记录了使用FOR循环和FORALL子句，向student表中插入10000条数据所需要的时间。可以看到，虽然执行的都是相同的操作，但FORALL的执行速度明显优于FOR循环，这是因为使用FORALL语句只涉及了一次PL/SQL与SQL的交互，从而最小化了上下文切换的开销。

此外，我们还可以使用INDICES OF和VALUES OF子句指定现有索引值，例如：

1. 通过i引用集合std_tab中元素的下标（除了以字符串为索引的关联数组，该集合可以是任何集合（包括稀疏集合）

FORALL i IN INDICES OF std -- std为一个特定的集合

INSERT INTO student_tmp VALUES (std (i), teach (i));

1. 通过i引用集合adm中的元素（该集合的索引和元素都必须是PLS_INTEGER 类型的，同样包括此类的稀疏集合）

FORALL i IN VALUES OF adm --adm为一个特定的集合

INSERT INTO administration VALUES (std (i), teach (i));

其中，std和teach为两个现有的数据集合

FORALL语句中RETURNING子句的使用

在FORALL语句中，DML语句可以包含一个RETURNING子句，用于提取每次DML操作的相关信息，但是该子句必须结合BULK COLLECT INTO一起使用（即RETURNING BULK COLLECT INTO），这里可以参考BULK COLLECT语句的用法。加上这个子句后，对于FORALL语句的每次迭代，都将此次DML操作的返回值按顺序存储在指定的集合中，例如：

--集合stu的元素为student表中要删除的行所对应的score值

FORALL i IN stu.FIRST..stu.LAST DELETE FROM student

WHERE score = tag(i)

RETURNING id, score --提取每个delete语句的信息，分别存储到ids和scores集合中

BULK COLLECT INTO ids, scores;

FORALL语句中的异常处理

通过迭代，FORALL会按顺序访问一个或多个集合索引对应的每一行，然后一并发送给SQL。那么，在这些操作中，如果有一次DML操作运行失败了，会有怎样的后果呢？

答案是：引起回滚。当有一个DML抛出异常时，PL/SQL将停止执行FORALL语句，并回滚前面的DML语句所做的所有更改。显然，这种不对异常做任何处理而导致的回滚会影响执行的效率。因此，在这里KingbaseES就为您提供了两种处理异常的方法：

1、异常立即处理：首先我们需要设置打开单语句回滚功能，然后通过EXCEPTION子句对FORALL中DML语句引发的异常进行立即处理，并结合COMMIT语句提交前面成功执行的DML操作。例如：

set ora_statement_level_rollback to on; --开启单语句回滚

DECLARE

err_msg VARCHAR(50);

BEGIN

-- 使用FORALL将DML操作批量发送到SQL

EXCEPTION -- 立即处理异常 --rollback

WHEN OTHERS THEN

err_msg := SQLERRM;

RAISE NOTICE ‘ERROR: %’, error_message;

COMMIT; --提交成功更新的结果

END;

/

通过这种方式立即处理一个DML语句抛出的异常，虽然PL/SQL仍然会回滚当前操作所做的更改，但前面DML操作所做的更改仍然会被提交。

2、暂时跳过异常：使用SAVE EXCEPTION跳过异常，并保存异常的相关信息，然后继续执行下面的DML语句，待FORALL执行完成之后，再处理所有的异常。SAVE EXCEPTION的开启方法，见第2章的FORALL语法部分。

当我们在执行完ROLALL语句之后，可以通过使用隐式游标属性，获得在FORALL中执行的DML操作的相关信息。除了常见的隐式游标属性，如果FORALL语句带有SAVE EXCEPTIONS子句，KingbaseES还提供了以下隐式游标属性，用于异常处理程序。

属性名	功能
SQL%BULK_EXCEPTIONS	将获取FORALL语句所抛出的所有异常信息
SQL%BULK_EXCEPTIONS.COUNT	抛出异常的 DML 语句数
SQL%BULK_EXCEPTIONS(i). ERROR_INDEX	第i个触发异常的DML语句的编号（即在FORALL中是第几条DML语句）
SQL%BULK_EXCEPTIONS(i). ERROR_CODE	第i个触发异常的DML语句，在KingbaseES数据库中对应的错误代码

在下面这个示例中，我们开启了SAVE EXCEPTION，并使用一些隐式游标属性来获取在FORALL中执行的DML操作的相关信息。

CREATE TABLE num_table (num_no NUMBER(2));

\set SQLTERM /

CREATE OR REPLACE PROCEDURE p1 AS

TYPE NumList IS TABLE OF PLS_INTEGER;

num_nos NumList := NumList(11,22,101,33,103,55);

BEGIN

--带有SAVE EXCEPTION子句的FORALL语句，用于暂时跳过异常

FORALL i IN num_nos.FIRST..num_nos.LAST SAVE EXCEPTIONS

INSERT INTO num_table VALUES (num_nos(i));

EXCEPTION

WHEN OTHERS THEN

-- 获取FORALL语句所抛出的所有异常信息

RAISE NOTICE 'sql%%bulk_exceptions: %', sql%bulk_exceptions;

-- 获取抛出异常的 DML 语句数

RAISE NOTICE 'sql%%bulk_exceptions.count: %', sql%bulk_exceptions.count();

-- 查看触发异常的DML语句，所对应的编号和错误代码

FOR i IN 1..sql%bulk_exceptions.count LOOP

RAISE NOTICE 'sql%%bulk_exceptions(%).error_index: %', i, sql%bulk_exceptions(i).error_index;

RAISE NOTICE 'sql%%bulk_exceptions(%).error_code: %', i, sql%bulk_exceptions(i).error_code;

END LOOP;

END;

/

此时，num_table还是一个没有数据的空表

随后，我们调用存储过程p1，向num_table中插入数据。

CALL p1();

打印结果如下：

可以看到，由于我们限制了num_no的最大值为99，因此第三条和第五条数据将无法成功插入。随后，我们通过通过隐式游标属性，获取到了抛出异常的语句数等相关信息。

现在，我们再查看一下num_table表，可以看到，使用SAVE EXCEPTION跳过了异常，并成功向num_table中面插入了4条数据！

使用FORALL的局限性

然而， FORALL在带来高效的同时，也存在一些弊端，例如：一个FOR 循环中可以包含多条DML语句，这些语句可以针对数据集合中的各个元素执行不同的操作。而FORALL语句限制了只能包含一条DML语句，并通过迭代执行多次相同的DML操作，每次操作的不同之处仅在于使用的数据。因此，在实际编程中，是否能够使用FORALL对DML语句进行加速，还是取决于您的具体需求。

总结

使用FORALL能够将执行的DML操作以及每次操作所需的数据，一次性从PL/SQL引擎运载到SQL引擎，这大大减少了PL/SQL交互的次数。基于PL/SQL与SQL交互带来的性能开销，以及DML操作在SQL中的重要地位， FORAL可以说是非常重要的性能优化工具之一。通过批量处理DML语句，FORALL减少了PL/SQL与SQL引擎之间的交互，显著的提高了执行多条相同DML语句的性能和效率。因此，在面对大量相同的DML操作时，你可以选择“FORALL”作为您的加速器！

参考资料

《KingbaseES_PLSQL过程语言参考手册》