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

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

关键字：

KingbaseES、批量处理、BULK COLLECT、人大金仓、KingbaseES

什么是批量处理？

在介绍多行SQL的批处理之前，我们首先引入一个概念——上下文切换：当PL/SQL在运行一块代码时，PL/SQL引擎只处理过程语句（例如赋值语句、条件语句等），而SQL语句将会发送给SQL引擎来处理，等SQL语句处理完之后，再将结果返回给PL/SQL。那么这种在PL/SQL引擎和SQL引擎之间的转移控制，就称为上下文切换。显然，频繁的上下文切换将会增加额外的开销，导致处理速度变慢、效率降低。

为了解决上述问题，最直接的解决方法就是减少上下文切换的次数。为了提高在PL/SQL中执行SQL语句的性能，PL/SQL提供了批量处理这种数据库操纵技术，它允许一次性处理多个SQL语句，进而让PL/SQL更有效的将多个上下文切换压缩成一个切换，极大地较少了上下文切换的次数，并提高了DML的性能。本文将介绍批量处理中的重要功能—BULK COLLECT语句。

BULK COLLECT简介

举个简单的例子，假如我们在餐厅吃饭，我们点了很多菜，服务员可以选择一次给我们上一份菜，也可以选择使用餐车，一次多上几份菜。显然，使用餐车会节省不少时间的和精力！那么在数据库的世界里，BULK COLLECT 就扮演着餐车这个身份，它可以在单次数据库交互中从SQL中返回多条查询结果给PL/SQL。由此可见，BULK COLLECT极大地减少上下文切换次数和提取数据时的额外开销，进而解决了在处理大规模数据查询，使用传统的单行操作可能会面临的性能瓶颈。

如何使用BULK COLLECT加快查询速度？

为了加快查询的速度，我们可以在SELECT INTO语句、FEACH语句和包含RETURE INTO的语句中使用BULK COLLECT子句。

在使用BULK COLLECT之前，我们需要定义一个或多个集合类型来存储从数据库中返回的数据。在使用的BULK COLLECT时，SQL引擎会自动初始化和扩展相关的集合，并从索引1开始对集合进行填充，连续的插入数据。

在下面本小节中，我们将使用student这张表：

CREATE TABLE student(id number, name varchar2(50));

INSERT INTO student VALUES(1, 'Andy');

INSERT INTO student VALUES(2, 'Lucy');

INSERT INTO student VALUES(3, 'Lisa ');

INSERT INTO student VALUES(4, 'Sue');

1) 在SELECT INTO语句中使用BULK COLLECT子句，也称为SELECT BULK COLLECT INTO语句，其功能是将整个查询结果集存储到一个或多个集合变量中，如下为使用BULK COLLECT收集查询结果的简单示例：

示例1-将查询结果批量存储到一个集合中

\set SQLTERM /

DECLARE

TYPE stu_list IS TABLE OF student%ROWTYPE INDEX BY BINARY_INTEGER;

stu_data stu_list;

BEGIN

-- 使用BULK COLLECT收集查询结果

SELECT * bulk COLLECT INTO stu_data FROM student;

-- 判断查询结果是否为空，如果查询结果不为空，则打印查询结果

IF stu_data.COUNT > 0 THEN

FOR i IN 1 .. stu_data.COUNT LOOP

raise notice ' student # id: %, name: %;', stu_data(i).id , stu_data(i).name;

END LOOP;

END IF;

END;

/

\set SQLTERM ;

打印结果如下：

NOTICE: student # id: 1, name: Andy;

NOTICE: student # id: 2, name: Lucy;

NOTICE: student # id: 3, name: Lisa ;

NOTICE: student # id: 2, name: Sue;

ANONYMOUS BLOCK

注意，如果带有BULK COLLECT子句的语句没有返回任何结果时，PL/SQL不会抛出异常，这是我们需要自行对集合的内容进行检查，看其中是否还有数据。

2) 在FEACH语句中使用BULK COLLECT子句，也称为FEACH BULK COLLECT语句，其功能是将游标中的数据提取到一个或多个集合变量中，下面为一个使用BULK COLLECT收集游标数据的示例：

示例2-将收集到的游标数据批量提取到两个集合中

\set SQLTERM /

DECLARE

CURSOR c1 IS SELECT id, name FROM student;

TYPE id_list IS TABLE OF student.id%TYPE

INDEX BY BINARY_INTEGER;

TYPE name_list IS TABLE OF student.name%TYPE

INDEX BY BINARY_INTEGER;

stu_id id_list;

stu_name name_list;

BEGIN

OPEN c1;

LOOP

-- 使用BULK COLLECT收集游标数据

FETCH c1 BULK COLLECT INTO stu_id, stu_name LIMIT 3;

FOR i in 1..stu_id.COUNT LOOP

RAISE NOTICE 'student * id: %, name: %;', stu_id(i) , stu_name(i);

END LOOP;

RAISE NOTICE '------------------------------------------------------';

EXIT WHEN c1%NOTFOUND;

END LOOP;

CLOSE c1;

END;

/

\set SQLTERM ;

打印结果如下：

NOTICE: student * id: 1, name: Andy;

NOTICE: student * id: 2, name: Lucy;

NOTICE: student * id: 3, name: Lisa ;

NOTICE: ------------------------------------------------------

NOTICE: student * id: 4, name: Sue;

NOTICE: ------------------------------------------------------

ANONYMOUS BLOCK

可以看到，在逐条查询数据时，这个结束循环的判断通常是放在FEACH语句的后面，而在这个例子中，我们是在循环语句的最后才通过c1%NOTFOUND来判断是否结束循环。这样做是因为，使用BULK COLLECT后，将一次提取多行数据，当FEACH在提取完最后一部分数据之后，c1%NOTFOUND就会返回TRUE，但此时集合中还剩一部分数据需要进行处理，因此我们应该在循环的最后进行结束循环的判断。

3) 在RETURN INTO语句中使用BULK COLLECT子句，也称为RETURN BULK COLLECT INTO语句，可以出现在INSERT、UPDATE、DELETE 或 EXECUTE IMMEDIATE 语句中，其功能是将DML操作的返回值存储到一个或多个集合变量中，一个使用BULK COLLECT收集游标数据的简单实例示例如下：

示例3-在两个集合中返回已删除的行

\set SQLTERM /

DECLARE

TYPE id_list IS TABLE OF student.id%TYPE;

TYPE name_list IS TABLE OF student.name%TYPE;

ids id_list;

names name_list;

BEGIN

DELETE FROM student WHERE id > 2

RETURNING id, name BULK COLLECT INTO ids,names;

IF ids.COUNT > 0 THEN

raise notice '------Deleted rows: %------', SQL%ROWCOUNT;

FOR i IN ids.FIRST .. ids.LAST

LOOP

raise notice 'Students No. %: %', ids(i), names(i);

END LOOP;

ELSE

raise notice 'No deleted.';

END IF;

END;

/

\set SQLTERM ;打印结果如下：

NOTICE: ------Deleted rows: 2------

NOTICE: Students No. 3: Lisa

NOTICE: Students No. 4: Sue

ANONYMOUS BLOCK

限制BULK COLLECT提取的行数

在示例2中我们使用了一个LIMIT子句，那么它的作用是什么呢？

假如我们要查询的并处理大量的数据时，直接使用BULK COLLECT子句会一次性提取所有的行，然后存储到一个超级大的集合中。这样的操作看起来十分简单，但其实但实际上存在很多潜在的弊端：

• 内存压力：一次性将大量数据加载到内存中，会消耗掉会话的大量的内存，进而引发内存不足的问题。

• 性能下降：如多一次性读取的数据过多，可能会导致查询的响应时间增加，进而影响应用程序的性能。此外，响应时间过长还会影响用户的体验。

为了解决上述问题，KingbaseES为BULK COLLECT提供了LIMIT子句，让我们能够限制在一次从数据库提取的行数，然后分批提取数据，这样可以减轻内存的压力、提高性能、实现稳定和高效的数据处理。LIMIT 子句的简单语法如下：

FETCH cursor_name BULK COLLECT INTO ... [LIMIT rows]

其中，rows表示一次提取限制的行数，其可以时常量、变量或表达式。

除了LIMIT子句，KingbaseES还为SELECT BULK COLLECT INTO语句提供ROWNUM伪列和FETCH FIRST子句，来限制行数和集合的大小，使用语法如下：

--使用ROWNUM限制行数

SELECT colum_name BULK COLLECT INTO record_name FROM table_name

WHERE ROWNUM <= rows;

--使用FETCH FIRST限制行数

SELECT colum_name BULK COLLECT INTO record_name FROM table_name

FETCH FIRST rows ROWS ONLY;

总结

BULK COLLECT是PL/SQL中批量处理的一项重要工具，其通过将多次上下文切换转变为一次、减少了PL/SQL和SQL引擎之间的交互次数，因此在处理多行数据、优化性能、等方面非常有效。在查询中合理的使用BULK COLLECT子句，可以显著提升性能，但在使用的过程中，我们还需要合理控制批处理的大小，避免造成内存不足等问题。希望通过这篇文章，能帮助您更好的了解并掌握如何使用BULK COLLECT进行多行SQL的批处理，实现更高效的数据处理操作。

总之，如果您需要处理多行数据的话， BULK COLLECT将是个不错的选择！

参考资料

《KingbaseES_PLSQL过程语言参考手册》