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