BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
有关FORALL语句的用法请参考:批量SQL之 FORALL 语句
一、BULK COLLECT批量绑定的示例
--下面的示例中使用了BULK COLLECT将得到的结果集绑定到记录变量中
DECLARE
TYPE emp_rec_type IS RECORD --声明记录类型
(
empno emp.empno%TYPE
,ename emp.ename%TYPE
,hiredate emp.hiredate%TYPE
);
TYPE nested_emp_type IS TABLE OF emp_rec_type; --声明记录类型变量
emp_tab nested_emp_type;
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO emp_tab --使用BULK COLLECT 将所得的结果集一次性绑定到记录变量emp_tab中
FROM emp;
FOR i IN emp_tab.FIRST .. emp_tab.LAST
LOOP
DBMS_OUTPUT.put_line('Current record is '||emp_tab(i).empno||chr(9)||emp_tab(i).ename||chr(9)||emp_tab(i).hiredate);
END LOOP;
END;
--上面的例子可以通过FOR 循环和普通的SELECT INTO来实现,那两者之间的差异呢?
--差异是FOR循环的SELECT INTO逐行提取并绑定到记录变量,而BULK COLLECT则一次即可提取所有行并绑定到记录变量。即谓批量绑定。
二、使用LIMIT限制FETCH数据量
在使用BULK COLLECT 子句时,对于集合类型,如嵌套表,联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULK
COLLECT子句操作集合,则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性,如果数据量较大,而集合在此时又自动扩展,为避
免过大的数据集造成性能下降,因此使用limit子句来限制一次提取的数据量。limit子句只允许出现在fetch操作语句的批量中。
用法:
FETCH ... BULK COLLECT INTO ... [LIMIT rows]
DECLARE
CURSOR emp_cur IS
SELECT empno, ename, hiredate FROM emp;
TYPE emp_rec_type IS RECORD
(
empno emp.empno%TYPE
,ename emp.ename%TYPE
,hiredate emp.hiredate%TYPE
);
TYPE nested_emp_type IS TABLE OF emp_rec_type; -->定义了基于记录的嵌套表
emp_tab nested_emp_type; -->定义集合变量,此时未初始化
v_limit PLS_INTEGER := 5; -->定义了一个变量来作为limit的值
v_counter PLS_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur
BULK COLLECT INTO emp_tab -->fetch时使用了BULK COLLECT子句
LIMIT v_limit; -->使用limit子句限制提取数据量
EXIT WHEN emp_tab.COUNT = 0; -->注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound
v_counter := v_counter + 1; -->记录使用LIMIT之后fetch的次数
FOR i IN emp_tab.FIRST .. emp_tab.LAST
LOOP
DBMS_OUTPUT.put_line( 'Current record is '||emp_tab(i).empno||CHR(9)||emp_tab(i).ename||CHR(9)||emp_tab(i).hiredate);
END LOOP;
END LOOP;
CLOSE emp_cur;
DBMS_OUTPUT.put_line( 'The v_counter is ' || v_counter );
END;
三、RETURNING 子句的批量绑定
BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,我们
需要使用RETURNING子句来实现批量绑定。
--下面示例中从表emp中删除所有deptno=20的记录
DECLARE
TYPE emp_rec_type IS RECORD
(
empno emp.empno%TYPE
,ename emp.ename%TYPE
,hiredate emp.hiredate%TYPE
);
TYPE nested_emp_type IS TABLE OF emp_rec_type;
emp_tab nested_emp_type;
-- v_limit PLS_INTEGER := 3;
-- v_counter PLS_INTEGER := 0;
BEGIN
DELETE FROM emp
WHERE deptno = 20
RETURNING empno, ename, hiredate -->使用returning 返回这几个列
BULK COLLECT INTO emp_tab; -->将前面返回的列的数据批量插入到集合变量
DBMS_OUTPUT.put_line( 'Deleted ' || SQL%ROWCOUNT || ' rows.' );
COMMIT;
IF emp_tab.COUNT > 0 THEN -->当集合变量不为空时,输出所有被删除的元素
FOR i IN emp_tab.FIRST .. emp_tab.LAST
LOOP
DBMS_OUTPUT.
put_line(
'Current record '
|| emp_tab( i ).empno
|| CHR( 9 )
|| emp_tab( i ).ename
|| CHR( 9 )
|| emp_tab( i ).hiredate
|| ' has been deleted' );
END LOOP;
END IF;
END;
四、FORALL与BULK COLLECT 综合运用
FORALL与BULK COLLECT是实现批量SQL的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。
DROP TABLE tb_emp;
CREATE TABLE tb_emp AS -->创建表tb_emp
SELECT empno, ename, hiredate
FROM emp
WHERE 1 = 2;
DECLARE
CURSOR emp_cur IS -->声明游标
SELECT empno, ename, hiredate FROM emp;
TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE; -->基于游标的嵌套表类型
emp_tab nested_emp_type; -->声明嵌套变量
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO emp_tab -->BULK COLLECT批量提取数据
FROM emp
WHERE sal > 1000;
FORALL i IN 1 .. emp_tab.COUNT -->使用FORALL语句将变量中的数据插入到表tb_emp
INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)
VALUES emp_tab( i );
COMMIT;
DBMS_OUTPUT.put_line( 'The total ' || emp_tab.COUNT || ' rows has been inserted to tb_emp' );
END;
五、BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
六、更多参考
批量SQL之 FORALL 语句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
分享到:
相关推荐
采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
结合一个存储过程的实例,介绍了 bulk collect 的使用
记录 集合 BULK COLLECT FORALL 执行计划
sal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO vsal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO sal,ename BULK COLLECT ...
bulk collect函数的使用,可以可以提高数据的访问效率
bulk collect bulk collect bulk collect例子特殊应用
1.FORALL 用法小结 ...5.用BULK COLLECT子句取回查询结果至集合中 6.用LIMIT 子句限制批取出操作行 7.用RETURNING INTO子句取回DML结果至集合 8.SQL优化学习笔记 9.给Oracle存储过程传入数组(这是自己的)
但是,FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个,这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降请再看下图: 下面详解这爷俩㈠ 通过BULK COLLECT 加速查询⑴ BULK COLLECT...
BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer用法笔记。 例1: 批量查询项目资金账户号为 “320001054663”的房屋账户信息并把它们打印出来 . DECLARE TYPE acct_...
在一般的情况下,使用批量fetch的几率并不是很多,但是Oracle提供了这个功能我们最好能熟悉一下,说不定什么时候会用上它。 代码如下:declare cursor c1 is select * from t_depart; v_depart t_depart%...
代码如下: — Created on 2010/11/04 by WANGNAN declare — Local variables here i integer; TYPE t_table IS TABLE OF VARCHAR2(10 ) INDEX BY VARCHAR2 (2); v_t_table t_table; TYPE t_pg3 IS TABLE OF asis.PG...
Oracle 遍历游标的四种方式(for、fetch、while、bulk collect+forall) 2.问题分析 我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据? 3.解决方案 ...
Collect是用 Visual C++ 语言开发的
本程序主要实现了备份和还原数据库、附加和分离数据库、提取数据表、导出数据、配置ODBC数据源等功能。