本文详细介绍了Oracle数据库中FORALL和BULK COLLECT的使用方法，这两种技术可以显著提高批量数据处理的效率。FORALL在循环插入时减少上下文切换，BULK COLLECT则能一次性加载大量数据，减少了网络传输的开销。通过示例代码展示了如何在INSERT、UPDATE、DELETE操作中应用这些技术，以及在处理异常时的注意事项，以提升数据库性能。
FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
例子:
create table test_forall ( user_id number(10), user_name varchar2(20));
select into 中使用bulk collect
DECLARE
TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
v_table table_forall;
BEGIN
SELECT mub.user_id,mub.user_name
BULK COLLECT INTO v_table
FROM mag_user_basic mub
WHERE mub.user_id BETWEEN 10000 AND 10100;
FORALL idx IN 1..v_table.COUNT
INSERT INTO test_forall VALUES v_table(idx);
--VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
--在PL/SQL中，BULK In-BIND与RECORD，%ROWTYPE是不能在一块使用的，
--也就是说，BULK In-BIND只能与简单类型的数组一块使用
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
fetch into 中使用bulk collect
DECLARE
TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
v_table table_forall;
CURSOR c1 IS
SELECT mub.user_id,mub.user_name
FROM mag_user_basic mub
WHERE mub.user_id BETWEEN 10000 AND 10100;
BEGIN
OPEN c1;
--在fetch into中使用bulk collect
FETCH c1 BULK COLLECT INTO v_table;
FORALL idx IN 1..v_table.COUNT
INSERT INTO test_forall VALUES v_table(idx);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
在returning into中使用bulk collect
CREATE TABLE test_forall2 AS SELECT * FROM test_forall;
----在returning into中使用bulk collect
DECLARE
TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;
enums IdList;
TYPE NameList IS TABLE OF test_forall.user_name%TYPE;
names NameList;
BEGIN
DELETE FROM test_forall2 WHERE user_id = 10100
RETURNING user_id, user_name BULK COLLECT INTO enums, names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
dbms_output.put_line('User #' || enums(i) || ': ' || names(i));
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
批量更新中，将for改成forall
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70, ...);
-- department numbers
BEGIN
...
FOR i IN depts.FIRST..depts.LAST
LOOP
...
--UPDATE statement is sent to the SQL engine
-- with each iteration of the FOR loop!
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
END LOOP:
END;
--UPDATE statement is sent to the SQL engine just once, with the entire nested table
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
To maximize performance, rewrite your programs as follows:
a. If an INSERT, UPDATE, or DELETE statement executes inside a loop and References collection elements, move it into a FORALL statement.
b. If a SELECT INTO, FETCH INTO, or RETURNING INTO clause references a
Collection, incorporate the BULK COLLECT clause.
c. If possible, use host arrays to pass collections back and forth between your Programs and the database server.
d. If the failure of a DML operation on a particular row is not a serious problem,Include the keywords SAVE EXCEPTIONS in the FORALL statement and report Or clean up the errors in a subsequent loop using the %BULK_EXCEPTIONS Attribute.
Oracle数据库之FORALL与BULK COLLECT语句
Oracle数据库之FORALL与BULK COLLECT语句 我们再来看一下PL/SQL块的执行过程:当PL/SQL运行时引擎处理一块代码时,它使用PL/SQL引擎来执行过程化的代码,而将SQL语句 ...
2015.1.15 利用Oracle函数插入表结构 Bulk collect into 不用循环，简洁高效
如果表结构只对应一个字段,可以 select col1 bulk collect into 变量,不用游标循环,简洁高效 create or replace function get_airway_s ...
Oracle批量SQL之 BULK COLLECT 子句
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎.通常可以在SELECT INTO.FETCH INTO以及RETURNING ...
fetch bulk collect into 进行批量、快速提取数据的方式
1.游标的含义 2.oracle 11g 中的三类游标的使用方式 3.oracle 11g中使用 fetch ... bulk collect into 进行批量.快速提取数据的方式 4.根据不同情况 ...
PL/SQL — BULK COLLECT用法
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎.通常可以在SELECT INTO.FETCH INTO以及RETURNING ...
PL/SQL批处理语句(一)BULK COLLECT
我们知道PL/SQL程序中运行SQL语句是存在开销的,因为SQL语句是要提交给SQL引擎处理,这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换,每次却换时,都有额外的开销.然而,FORA ...
Oracle forall bulk collect批量数据更新
对于数据量较大的插入操作可采用此种方法操作,注意: limit减少内存占用,如果数据量较大一次性全部加载到内存中,对PGA来说压力太大,可采用limit的方法一次加载一定数量的数据,建议值通常为100 ...
使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据
用批量绑定(bulk binding)的方式.当循环执行一个绑定变量的sql语句时候,在PL/SQL 和SQL引擎(engines)中,会发生大量的上下文切换(context switc ...
随机推荐
SQL Server 2008 R2——使用数字辅助表(master..spt_values)实现用计数字段对记录进行重复显示
=================================版权声明================================= 版权声明:原创文章 谢绝转载 请通过右侧公告中的“联系邮 ...
js 数组中随机出来N组
var word = []; while (word.length < 7) { var tmp = data[parseInt(Math.random() * data.length)]; v ...
public,private,protected的区别
一,public,private,protected的区别public:权限是最大的,可以内部调用,实例调用等.protected: 受保护类型,用于本类和继承类调用.private: 私有类型,只有 ...
node.js(三)url处理
1.parse函数的基础用法 parse函数的作用是解析url,返回一个json格式的数组,请看如下示例: var url = require('url'); url.parse('http://ww ...
poj2386Lake Counting
题意是这种.给你一个N*M的矩形图.这个图由两个东西组成.'.'和'W', W代表这个地方有水. .代表这个地方没水. 假设一个有水的地方它的上下左右,左上,坐下.右上.右下也有水,那么 就看成它们连 ...
python批量提取eml附件
从批量eml文件中提取附件,使用方式如下 代码如下 import email import os import sys #获取eml附件信息 def Get_Annex_Message(FilePat ...
Android Touch事件相关源码【Android SourceCode 2.3.6】
2018-05-31 17:23:46 Note: 这里的源码来自Android 2.3.6,这个版本的代码比较简单,适合理解Touch事件的传递原理.后续版本源码复杂了很多,但是原理都是类似的. 2 ...
js 持续访问保持session对象不消失
$(function(){ publicBusi(); }) //实时刷新登录用户信息 function publicBusi(){ setTimeout(publicBusi,1000*60*10) ...
【前言】Go语言开坑
很早之前就已经听过Go语言的大名,今天终于要开坑研究Go了,来吧看看. [Go语言学习目录] 1. Go安装 2. Go变量(Variables) 3. Go语言基 ...
2018SDIBT_国庆个人第三场
A - A CodeForces - 1042A There are nn benches in the Berland Central park. It is known that aiai peo ...
553
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
