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 中使用 fetch bulk collect into 批量效率的读取游标数据
采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都...
记录 集合 BULK COLLECT FORALL 执行计划
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer用法笔记。对oracle批量查询 删除 更新使用bulk collect提高效果的相关知识感兴趣的朋友通过本文一起学习吧
bulk collect bulk collect bulk collect例子特殊应用
结合一个存储过程的实例,介绍了 bulk collect 的使用
本篇文章小编为大家介绍一下,浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献。有需要的朋友可以参考一下
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 ...
在一般的情况下,使用批量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.解决方案 ...
关于Oracle FORALL用法的小结
oracle学习--动态数组测试,创建一个数组类型FORALL && BULK COLLECT
4.用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 ,用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 5.用BULK COLLECT子句取回查询结果至集合中 6.用LIMIT 子句限制批取出操作行 7.用RETURNING INTO子句取回DML结果至...