- 浏览: 770639 次
- 性别:
- 来自: 广州
文章分类
最新评论
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
有关FORALL语句的用法请参考:批量SQL之 FORALL 语句
一、BULK COLLECT批量绑定的示例
[sql] view plaincopyprint?
--下面的示例中使用了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]
[sql] view plaincopyprint?
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子句来实现批量绑定。
[sql] view plaincopyprint?
--下面示例中从表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的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。
[sql] view plaincopyprint?
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 子句中。
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
有关FORALL语句的用法请参考:批量SQL之 FORALL 语句
一、BULK COLLECT批量绑定的示例
[sql] view plaincopyprint?
--下面的示例中使用了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]
[sql] view plaincopyprint?
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子句来实现批量绑定。
[sql] view plaincopyprint?
--下面示例中从表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的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。
[sql] view plaincopyprint?
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 子句中。
发表评论
-
Oracle 10g 的clusterware 32位 下载地址
2013-04-19 23:03 1157Oracle 10g 的clusterware 32位 下载地 ... -
oracle 分析函数 RANK()
2013-04-11 00:05 1063RANK()既是一个聚合函数,也是一个分析函数 其具体的语法 ... -
oracle 分析函数
2013-04-09 23:25 1133分析函数是用于计算一组中多行的聚合值,与聚合函数的区别在于聚合 ... -
pl/sql集合类型
2013-03-26 10:12 1535--集合类型 /* 单行单列的数据,使用标量变量 单行 ... -
oracle 行链接与行迁移
2013-03-16 01:06 1062表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放 ... -
oracle Health Monitor
2013-01-20 00:02 1572About Health Monitor Beginning ... -
oracle moving window size与 AWR retention period关系
2013-01-19 15:58 8453转自: http://tomszrp.itpub.net/po ... -
Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
2013-01-12 00:20 2829insert提示IGNORE_ROW_ON_DUPKEY_IN ... -
oracle 11g新特性Flashback data archive
2013-01-09 22:52 30361. 什么是flashback data archive F ... -
RMAN List和report 命令
2012-12-25 00:07 2822LIST 命令 使用RMAN LIST 命令显示有关资料档案库 ... -
oracle ASM中ASM_POWER_LIMIT参数
2012-12-24 23:46 6364ASM_POWER_LIMIT 该初始化参数用于指定ASM例程 ... -
oracle I/O 从属进程
2012-12-24 23:24 1368I/O 从属进程 I/O从 ... -
easy connect 之 ORA-12154: TNS: 无法解析指定的连接标识符
2012-12-19 23:43 5588用easy connect连接出现“tns无法解析指定的连接标 ... -
Flashback Database --闪回数据库
2012-12-19 23:38 1331Flashback 技术是以Undo segment中的内容为 ... -
Oracle 11g新特性:Automatic Diagnostic Repository
2012-12-19 22:35 1343Oracle Database 11g的FDI(Fault D ... -
RMAN配置中通道(CHANNEL)相关参数 PARALLELISM 、FILESPERSET的关系
2012-12-19 22:09 2590RMAN配置中通道(CHANNEL)相 ... -
oracle 空间RESUMABLE
2012-12-14 22:05 3003空间RESUMABLE操作 转 Oracle从9i开始 ... -
oracle 创建视图 with check option
2012-12-13 23:14 1462我们来看下面的例子: create or replace vi ... -
flashback transaction闪回事务查询
2012-11-26 22:00 1457闪回事务查询有别于闪回查询的特点有以下3个: (1) ... -
pl/sql连不上oracle数据库
2012-11-21 22:56 3704pl/sql 9.2版本连不上oracle数据库 当系统安装 ...
相关推荐
记录 集合 BULK COLLECT FORALL 执行计划
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
本篇文章小编为大家介绍一下,浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献。有需要的朋友可以参考一下
采用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 FORALL用法的小结
结合一个存储过程的实例,介绍了 bulk collect 的使用
bulk collect函数的使用,可以可以提高数据的访问效率
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例子特殊应用
Oracle 遍历游标的四种方式(for、fetch、while、bulk collect+forall) 2.问题分析 我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据? 3.解决方案 ...
BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer用法笔记。对oracle批量查询 删除 更新使用bulk collect提高效果的相关知识感兴趣的朋友通过本文一起学习吧
abap collect的用法与实例 abap collect的用法与实例
1.FORALL 用法小结 2.如何使用批挷定提高性能 3.FORALL 如何影响回滚 4.用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 ,用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 5.用BULK COLLECT子句取回查询结果至集合中 6....
代码如下: — 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...
在一般的情况下,使用批量fetch的几率并不是很多,但是Oracle提供了这个功能我们最好能熟悉一下,说不定什么时候会用上它。 代码如下:declare cursor c1 is select * from t_depart; v_depart t_depart%...
Collect是用 Visual C++ 语言开发的