ORACLE批量绑定FORALL与BULK COLLECT
前端之家收集整理的这篇文章主要介绍了
ORACLE批量绑定FORALL与BULK COLLECT，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
例子:
- createtabletest_forall(user_idnumber(10),user_namevarchar2(20));
select into 中使用bulk collect
DECLARE
TYPEtable_forallISTABLEOFtest_forall%ROWTYPE;
v_tabletable_forall;
BEGIN
SELECTmub.user_id,mub.user_name
BULKCOLLECTINTOv_table
FROMmag_user_basicmub
WHEREmub.user_idBETWEEN10000AND10100;
FORALLidxIN1..v_table.COUNT
INSERTINTOtest_forallVALUESv_table(idx);
--在PL/sql中，BULKIn-BIND与RECORD，%ROWTYPE是不能在一块使用的，
--也就是说，BULKIn-BIND只能与简单类型的数组一块使用
COMMIT;
EXCEPTION
WHENOTHERSTHEN
ROLLBACK;
END;
fetch into 中使用bulk collect
CURSORc1IS
OPENc1;
--在fetchinto中使用bulkcollect
FETCHc1BULKCOLLECTINTOv_table;
在returning into中使用bulk collect
CREATETABLEtest_forall2ASSELECT*FROMtest_forall;
----在returninginto中使用bulkcollect
TYPEIdListOFtest_forall.User_Id%TYPE;
enumsIdList;
TYPENameListOFtest_forall.user_name%TYPE;
namesNameList;
DELETEFROMtest_forall2WHEREuser_id=10100
RETURNINGuser_id,user_nameBULKCOLLECTINTOenums,names;
dbms_output.put_line('Deleted'||sql%ROWCOUNT||'rows:');
FORiINenums.FIRST..enums.LAST
LOOP
dbms_output.put_line('User#'||enums(i)||':'||names(i));
ENDLOOP;
批量更新中，将for改成forall
TYPENumListISVARRAY(20)OFNUMBER;
deptsNumList:=NumList(10,30,70,...);
--departmentnumbers
...
FORiINdepts.FIRST..depts.--UPDATEstatementissenttothesqlengine
--witheachiterationoftheFORloop!
UPDATEempSETsal=sal*1.10WHEREdeptno=depts(i);
ENDLOOP:
--UPDATEstatementissenttothesqlenginejustonce,withtheentirenestedtable
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 ser
IoUs 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.
