ORACLE批量绑定FORALL与BULK COLLECT

前端之家收集整理的这篇文章主要介绍了ORACLE批量绑定FORALL与BULK COLLECT前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
FORALL与BULK COLLECT的使用方法:
1.使用FORALL比FOR效率高,因为前者只切换一次上下文,而后者将是在循环次数一样多个上下文间切换。

2.使用BLUK COLLECT一次取出一个数据集合,比用游标条取数据效率高,尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
例子:
  1. 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);
  • --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
  • --在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 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相关文章