SQL优化-批量SQL之 BULK COLLECT 子句
来源:互联网 发布:经典爱情电影 知乎 编辑:程序博客网 时间:2024/05/29 08:35
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,我们
需要使用RETURNING子句来实现批量绑定。
FORALL与BULK COLLECT是实现批量SQL的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。
五、BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
有关FORALL语句的用法请参考:批量SQL之 FORALL 语句
--下面的示例中使用了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 子句中。
0 0
- SQL优化-批量SQL之 BULK COLLECT 子句
- 批量SQL之 BULK COLLECT 子句
- 批量SQL之 BULK COLLECT 子句
- 批量SQL之 BULK COLLECT 子句
- 批量SQL之 BULK COLLECT 子句 .
- 批量SQL之 BULK COLLECT 子句 .
- 批量SQL之 BULK COLLECT 子句
- 批量SQL之BULK COLLECT子句
- 批量SQL之 BULK COLLECT 子句
- PL/SQL BULK COLLECT INTO
- 批量SQL(BULK SQL)
- PL/SQL批处理语句(BULK COLLECT子句和FORALL语句)
- PL/SQL Best Practice----On BULK COLLECT
- oracle pl/sql(bulk collect用法)
- oracle下巧用bulk collect实现cursor批量fetch的sql语句
- Oracle PL/SQL开发基础(第二十弹:批量绑定和BULK COLLECT)
- teradata sql优化之qualify子句优化
- PL/SQL批处理语句:BULK COLLECT 和 FORALL对优化的贡献
- 黑马程序员-交通灯管理系统
- 二、获取笑话
- oracle 查看用户所在的表空间 系统权限 资源
- View Programming Guide for iOS
- 黑马程序员高新技术枚举类
- SQL优化-批量SQL之 BULK COLLECT 子句
- 数据结构上机测试2-1:单链表操作A
- Gson简要使用笔记
- C++集成开发环境 Code::Blocks
- AChartEngine应用之PieChart(饼图)
- Trie 总结
- ogre3D学习基础1 -- 核心对象与脚本技术
- ogre3D学习基础2 -- 顶点程序与片断程序
- unity3D 摄像机跟随