ORACLE批量绑定FORALL与BULK COLLECT

Oracle 2020-07-05

FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。

2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
例子:

 
  createtabletest_forall(user_idnumber(10),user_namevarchar2(20));
 

select into 中使用bulk collect
DECLARE @H_301_38@TYPEtable_forallISTABLEOFtest_forall%ROWTYPE; @H_301_38@v_tabletable_forall;

BEGIN

SELECTmub.user_id,mub.user_name

@H_301_38@BULKCOLLECTINTOv_table

FROMmag_user_basicmub

WHEREmub.user_idBETWEEN10000AND10100;

@H_301_38@FORALLidxIN1..v_table.COUNT

INSERTINTOtest_forallVALUESv_table(idx);

@H_301_38@--VALUES(v_table(idx).user_id,v_table(idx).user_name);Error @H_301_38@--在PL/sql中，BULKIn-BIND与RECORD，%ROWTYPE是不能在一块使用的， @H_301_38@--也就是说，BULKIn-BIND只能与简单类型的数组一块使用

COMMIT;

@H_301_38@ @H_301_38@EXCEPTION

WHENOTHERSTHEN

ROLLBACK;

END;

fetch into 中使用bulk collect CURSORc1IS

OPENc1;

@H_301_38@--在fetchinto中使用bulkcollect

FETCHc1BULKCOLLECTINTOv_table;

在returning into中使用bulk collect CREATETABLEtest_forall2ASSELECT*FROMtest_forall;

@H_301_38@----在returninginto中使用bulkcollect @H_301_38@TYPEIdListOFtest_forall.User_Id%TYPE; @H_301_38@enumsIdList; @H_301_38@TYPENameListOFtest_forall.user_name%TYPE; @H_301_38@namesNameList;

DELETEFROMtest_forall2WHEREuser_id=10100

@H_301_38@RETURNINGuser_id,user_nameBULKCOLLECTINTOenums,names; @H_301_38@dbms_output.put_line('Deleted'||sql%ROWCOUNT||'rows:');

FORiINenums.FIRST..enums.LAST

@H_301_38@LOOP @H_301_38@dbms_output.put_line('User#'||enums(i)||':'||names(i));

ENDLOOP;

批量更新中，将for改成forall TYPENumListISVARRAY(20)OFNUMBER;

@H_301_38@deptsNumList:=NumList(10,30,70,...); @H_301_38@--departmentnumbers @H_301_38@...

FORiINdepts.FIRST..depts.--UPDATEstatementissenttothesqlengine

@H_301_38@--witheachiterationoftheFORloop!

UPDATEempSETsal=sal*1.10WHEREdeptno=depts(i);

ENDLOOP:

@H_301_38@--UPDATEstatementissenttothesqlenginejustonce,withtheentirenestedtable @H_301_38@FORALLiINdepts.WHEREdeptno=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]记一次由sequence引发的enq sv-contention等待事件

数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库等待会话暴增...

oracle统计信息的锁定与解锁

（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该返回100多万...

【转】RMAN删除过期备份或非过期备份

（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操作时，会在RM...

数据恢复顾问（DRA）

（1）DRA介绍数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能够修复数据文...

RMAN备份与恢复（一）--认识RMAN

RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（restore）和恢复...

RMAN备份与恢复（三）--RMAN备份相关概念

（1）备份对象可以使用RMAN进行的备份对象如下： --整个数据库：备份所有的数据文件和控制文件； --数...