Tuesday 9 May 2017

How to use BULK COLLECT statement for delete SQL query

CREATE OR REPLACE PROCEDURE SADMIN.DEL_CONTRI_DET_FOR_RECON
IS
   L_LIMIT   PLS_INTEGER := 20000;

   CURSOR C1
   IS
      SELECT row_id
        FROM siebel.s_invoice_item
       WHERE person_id IN
                (SELECT b.row_id
                   FROM SADMIN.disalloc A, SIEBEL.S_CONTACT B
                  WHERE A.PD_NEW_EYEE_ID = B.PERSON_UID
                        AND B.CON_CD = 'Member'
                        AND A.PD_DT_MODIFY BETWEEN '01-JAN-2010'
                                               AND '31-MAY-2010');

   TYPE temp_mem IS TABLE OF C1%ROWTYPE
                       INDEX BY PLS_INTEGER;

   tem_mp    temp_mem;
BEGIN
   OPEN C1;

   LOOP
      FETCH C1
      BULK COLLECT INTO tem_mp
      LIMIT L_LIMIT;

      EXIT WHEN tem_mp.COUNT = 0;

      FORALL indx IN 1 .. tem_mp.COUNT
         DELETE FROM siebel.s_invoice_item
               WHERE row_id = tem_mp (indx).row_id AND row_id LIKE '%-%-%';

      DBMS_OUTPUT.put_line (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
      COMMIT;
   END LOOP;

   CLOSE C1;
END;

No comments:

Post a Comment