ITPub博客

Oracle分批提交DML

原创 Oracle 作者:lhrbest 时间:2017-06-12 14:39:19 0 删除 编辑

Oracle分批提交DML 




1.采用分批操作并不能提高执行速度,执行效率不如单条DML语句。

2.分批插入可以减少对undo空间的占用,但频繁的提交,可能会导致前面提交的undo空间被其他事务占用而可能导致ORA-0155错误。

3.若分批操作中间半截失败掉,会将你数据库置于一种未知的状态。(DELETE操作不会出现这种情况)

   
1、分批UPDATE
DROP TABLE T2;
CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS;
SELECT * FROM T2; 
SELECT COUNT(*) FROM T2;
DECLARE
    TYPE RIDARRAY IS TABLE OF ROWID;
    TYPE VCARRAY IS TABLE OF T2.OBJECT_NAME%TYPE;
    L_RIDS  RIDARRAY;
    L_NAMES VCARRAY;
    CURSOR C IS
        SELECT ROWID,
               OBJECT_NAME
        FROM   T2;
BEGIN
    OPEN C;
    LOOP
        FETCH C BULK COLLECT
            INTO L_RIDS,
                 L_NAMES LIMIT 10;
        FORALL I IN 1 .. L_RIDS.COUNT
            UPDATE T2
            SET    OBJECT_NAME = LOWER(L_NAMES(I))
            WHERE  ROWID = L_RIDS(I);
        COMMIT;
        EXIT WHEN C%NOTFOUND;
    END LOOP;
    CLOSE C;
END;
/
2、分批DELETE
DROP TABLE T3; 
CREATE TABLE T3 AS SELECT * FROM DBA_OBJECTS; 
DECLARE
    CURSOR MYCURSOR IS
        SELECT ROWID FROM T3 ORDER BY ROWID; --按ROWID排序的CURSOR,删除条件是XXX=XXXX,根据实际情况修改 
    TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER;
    V_ROWID ROWID_TABLE_TYPE;
BEGIN
    OPEN MYCURSOR;
    LOOP
        FETCH MYCURSOR BULK COLLECT
            INTO V_ROWID LIMIT 5000; --每次处理5000行,也就是每5000行一提交 
        EXIT WHEN V_ROWID.COUNT = 0;
        FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST
            DELETE FROM T3 WHERE ROWID = V_ROWID(I);
        COMMIT;
    END LOOP;
    CLOSE MYCURSOR;
END;
/
3、分批INSERT
将T_20160401的数据全部插入T_20160401_01表。
DECLARE
  CURSOR MYCURSOR IS
    SELECT ROWID FROM T_20160401 ORDER BY ROWID;
  TYPE ROWID_TABLE_TYPE IS TABLE OF  ROWID INDEX BY PLS_INTEGER ;
  V_ROWID ROWID_TABLE_TYPE;

  V_COUNT NUMBER := 0;
  V_START DATE;
  V_END   DATE;
BEGIN
  SELECT SYSDATE INTO V_START FROM DUAL;
  OPEN MYCURSOR;
  LOOP
    FETCH MYCURSOR BULK COLLECT
      INTO V_ROWID LIMIT 50000;
    EXIT WHEN V_ROWID.COUNT = 0;
   
FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST
      INSERT INTO T_20160401_01
SELECT * FROM T_20160401 T WHERE T.ROWID=V_ROWID(I);
V_COUNT:=V_COUNT+TO_CHAR(SQL%ROWCOUNT);
    COMMIT;
  END LOOP;
  CLOSE MYCURSOR;
SELECT SYSDATE INTO V_END FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('START: ' || V_START);
  DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_COUNT);
  DBMS_OUTPUT.PUT_LINE('END: ' || V_END);
  COMMIT;
END;
/


DECLARE
  CURSOR MYCURSOR IS
    SELECT * FROM T_20160401 ORDER BY ROWID;
  TYPE ROWID_TABLE_TYPE IS TABLE OF T_20160401%ROWTYPE;
  V_ROWID ROWID_TABLE_TYPE;
BEGIN
  OPEN MYCURSOR;
  LOOP
    FETCH MYCURSOR BULK COLLECT
      INTO V_ROWID LIMIT 50000;
    EXIT WHEN V_ROWID.COUNT = 0;
  
    FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST
      INSERT INTO T_20160401_01 VALUES V_ROWID(I);
    COMMIT;
  END LOOP;
  CLOSE MYCURSOR;
END;
/




About Me

...............................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。


DBA笔试面试讲解
欢迎与我联系


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2140626/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1138
  • 访问量
    7059619