oracle批量回复delete数据,大批量数据的INSERT/UPDATE/DELETE方法

直接进行INSERT,UPDATE,DELETE很容易把服务器临时表空间全部消耗光，带来服务器DOWN机。采用如下方法可以快速进行DML操作，而且安全可靠！

步骤1、

alter table t_premium2 nologging;

步骤2、

DECLARE

CNT NUMBER(10):=0;

I NUMBER(10);

BEGIN

SELECT COUNT(*) INTO CNT FROM t_commission_charge WHERE

BUSINESS_TAX_AMOUNT IS NULL;

FOR I IN 1..TRUNC(CNT/20000)+1 LOOP

UPDATE t_commission_charge p SET

BUSINESS_TAX_AMOUNT = '0' WHERE p.BUSINESS_TAX_AMOUNT IS NULL

AND ROWNUM<=20000;

COMMIT;

END LOOP;

END;

步骤3、

UPDATE t_commission_charge SET

BUSINESS_TAX_AMOUNT = 0 WHERE BUSINESS_TAX_AMOUNT IS NULL;

UPDATE t_commission_charge SET

PERSONAL_INCOME_TAX_AMOUNT = 0 WHERE PERSONAL_INCOME_TAX_AMOUNT IS

NULL;

UPDATE t_commission_charge SET

BUSINESS_TAX_PROCESSED_FLAG = '0' WHERE BUSINESS_TAX_PROCESSED_FLAG

IS NULL;

UPDATE t_commission_charge SET

BUSINESS_TAX_PAID_FLAG = '0 'WHERE BUSINESS_TAX_PAID_FLAG IS

NULL;