bulk collect 海量数据批量删除实战一

create or replace procedure DeleteMonth_billCoS(YearMonth varchar2) is

   counter number;

   errors varchar2(40);

   counterSum number := 0;

   --Action: bill_month YearMonth <= '201105'.

   CURSOR C1 IS SELECT ROWID,ID FROMMONTH_BILLS

                WHEREto_char(BILL_MONTH,'YYYYMM') <= YearMonth

                  AND((BILL_STATE = 2 AND BILL_ERROR_CODE != 91) OR BILL_STATE IS NULL);

   type v_rowid_Type is table of varchar2(20);

        cRowidv_rowid_Type;

   typev_id_Type is table of varchar2(20);

        cIDv_id_Type;

begin

   INSERT INTODELETEMONTH_BILLS_LOGS VALUES(TO_CHAR(SYSDATE,'YYYY-MM-DD hh24:mi:ss'),

                'Starting execute DeleteMonth_bills then bill_month < ' || YearMonth);

   COMMIT;

   counter := 0;

   SET TRANSACTION USE ROLLBACK SEGMENTRBS_BIG;

    openC1;

  loop

    --bulk collect比较消耗内存,对于数据量大的采用限制limit控制每次批量执行多少条,数据量大最好不要采用

    fetch C1 bulk collect intocRowid,cID limit 256;

        for iin 1..cRowid.count loop

        --Commintevery 10000 records processed.10240=256*40

          IF(counter = 0 ) OR(counter >= 40)

            THEN

             COMMIT;

             SET TRANSACTION USE ROLLBACK SEGMENTRBS_MEDIUM;

                counter := 0;

          ELSE

                counter := + 1;

          END IF;

          DELETEMONTH_BILLS WHERE ROWID = cRowid(i);

          --Sum total delete rows,When you gotta exception orsomething,just around 256? lost?

          counterSum := + 256;

        end loop;

        commit;

exit when C1%notfound;

counterSum:= cRowid.count;

closeC1;

  end loop;

     COMMIT;

     INSERT INTODELETEMONTH_BILLS_LOGS VALUES(TO_CHAR(SYSDATE,'YYYY-MM-DD hh24:mi:ss'),

                'Deleted MONTH_BILLS tables '|| counterSum ||'RowSthan BILL_MONTH < ' ||YearMonth || ' was successfully!');

     COMMIT;

     EXCEPTION WHEN OTHERS THEN

      BEGIN

        errors := SQLERRM;

        ROLLBACK;--Whatever you can use or not.Does it work?

    closeC1;

        INSERT INTO DELETEMONTH_BILLS_LOGS VALUES(TO_CHAR(SYSDATE,'YYYY-MM-DDhh24:mi:ss'),

                'DELETE MONTH_BILLS '|| counterSum ||'ROWSTHEN SQLERROR:==>> ' ||errors || ' <<== PLEASE CHECK WHATHAPPEN? AND ROLLBACK ALL!');

        COMMIT;

      END;

end DeleteMonth_billCoS;