title: how to delete record recursively using cursor
body: <pre><code>CREATE OR REPLACE PROCEDURE  WRK.PURGE_ACH_BATCH_TAB_1()
  LANGUAGE SQL
  
  BEGIN
      -- Declare cursor
      DECLARE v_CNT_BLOCK INTEGER;
      DECLARE Ac_no BIGINT;
      DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; 
      DECLARE mycur cursor for 
          SELECT t.TNX_Ac_no
          FROM WRK.BATCH_TAB1 c
              INNER JOIN WRK.BATCH_TAB t
                  ON c.BATCH_ID = t.BATCH_ID
                      AND c.PROCESS_ID = t.process_ID
          WHERE c.BATCH_START_TS &lt; (CURRENT DATE -7 days); 
  
      set v_CNT_BLOCK = 0;
      open mycur;
  
      FETCH FROM mycur INTO Ac_no;
      WHILE(SQLSTATE = '00000') DO
          DELETE FROM WRK.BATCH_TAB1 b WHERE b.TNX_Ac_no = Ac_no;
          SET v_CNT_BLOCK=v_CNT_BLOCK+1;
          if v_CNT_BLOCK &gt;= 5 then
              set v_CNT_BLOCK = 0;
              commit;
          end if;
  
          FETCH FROM mycur INTO Ac_no;  
  
      END WHILE; 
  
      CLOSE mycur; 
  
      commit; 
  
  END
  @ 
  </code></pre>
  
  <p>I have millions of record but am fetching all records one at time..  later am fetching one by one for deletion. Here my question is does Selecting all millions of records at once will cause any performance issue. How to select record recursivEly so that I can take 50k records first and delete all.. Latar next 50k and so on..</p>