当前位置:数据库 > Oracle >>

Oracle分批提交DML

Oracle分批提交DML
 
Sql代码  
/*  
参考于TOM编程艺术 8.5章在循环中提交  
  
1.采用分批操作并不能提高执行速度,执行效率不如单条DML语句。  
2.分批插入可以减少对undo空间的占用,但频繁的提交,可能会导致前面提交的undo空间被其他事务占用而可能导致ORA-0155错误。  
3.若分批操作中间半截失败掉,会将你数据库置于一种未知的状态。(DELETE操作不会出现这种情况)  
  
*/  
  
--分批 update  
DROP TABLE T2;  
  
CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS;  
SELECT * FROM T2;  
SELECT COUNT(*) FROM T2;  
--is table of 创建一个xx类型的数组  
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;  
   
--分批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;  
/  
  
--分批insert  
  
DROP TABLE T4;  
DROP TABLE T5;  
CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;  
CREATE TABLE T5 AS SELECT * FROM T4 WHERE 1=0;  
DECLARE    
   CURSOR MYCURSOR IS SELECT  ROWID FROM T4  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  
        INSERT INTO T5 SELECT * FROM T4 WHERE ROWID=V_ROWID(I);  
     COMMIT;  
   END LOOP;  
   CLOSE MYCURSOR;  
END;  
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,