【Oracle】分批删除大量数据

• 分段删除USER_INFO 表 状态是 0和1 的记录

--分段删除USER_INFO 表 状态是 0和1 的记录
DECLARE
  	CURSOR MYCURSOR IS
    	SELECT A.ID, A.CERT_ID 
    	FROM USER_INFO A 
    	WHERE A.STATUS IN ('0', '1');
  	TYPE T_DATA IS TABLE OF MYCURSOR%ROWTYPE;
  	V_DATA T_DATA;
BEGIN
  	OPEN MYCURSOR;
	  	LOOP
	    	FETCH MYCURSOR BULK COLLECT INTO V_DATA LIMIT 20000; --20000行提交一次 
	    	EXIT WHEN V_DATA.COUNT = 0;
	    	FORALL I IN V_DATA.FIRST .. V_DATA.LAST
	      	DELETE FROM USER_INFO
	       	WHERE ID = V_DATA(I).ID
	         AND CERT_ID = V_DATA(I).CERT_ID;
	    	COMMIT;
	  	END LOOP;
  	CLOSE MYCURSOR;
END;
/

• 分段删除同一个证件号，状态为0和1的重复的记录，只保留其中一条

-- 分段删除同一个证件号，状态为0和1的重复的记录，只保留其中一条
DECLARE
  	CURSOR MYCURSOR IS
    	SELECT *
      		FROM (SELECT COUNT(1) CNT, MIN(ID) ID, A.CERT_ID
              	FROM UNION_LOAN_USER_INFO A
             	WHERE A.CHANNEL_CODE = '10001'
               	AND A.STATUS IN ('0', '1')
             	GROUP BY A.CERT_ID)
     	WHERE CNT > 1;
  	TYPE T_DATA IS TABLE OF MYCURSOR%ROWTYPE;
  	V_DATA T_DATA;
BEGIN
  	OPEN MYCURSOR;
	  	LOOP
	    	FETCH MYCURSOR BULK COLLECT INTO V_DATA LIMIT 20000; --20000行提交一次 
	    	EXIT WHEN V_DATA.COUNT = 0;
	    	FORALL I IN V_DATA.FIRST .. V_DATA.LAST
	      	DELETE FROM USER_INFO
	       	WHERE ID = V_DATA(I).ID
	         AND CERT_ID = V_DATA(I).CERT_ID;
	    	COMMIT;
	  	END LOOP;
  	CLOSE MYCURSOR;
END;
/