PL-SQL – FOR Loop batch operation

By Ref – http://www.oracle.com/technetwork/issue-archive/2008/08-nov/o68plsql-088608.html
Code Listing 4: Using the LIMIT clause

We modify the procedure and customized it for better use case

CREATE OR REPLACE PROCEDURE bulk_with_limit
IS
  limit_in PLS_INTEGER DEFAULT 100;
  CURSOR mytable_cur
  IS
    SELECT ID FROM MYTABLE WHERE DTTIME < CURRENT_TIMESTAMP;
    TYPE mytable_tt
    IS
    TABLE OF MYTABLE.BODID%TYPE INDEX BY PLS_INTEGER;
  l_mytable mytable_tt;
BEGIN
  OPEN mytable_cur;
  LOOP
    FETCH mytable_cur BULK COLLECT INTO l_mytable LIMIT limit_in;
    FOR indx IN 1 .. l_mytable.COUNT
    LOOP
      DELETE FROM MYTABLE WHERE ID = l_mytable(indx);
      DBMS_OUTPUT.PUT_LINE('Deletting : '||l_mytable(indx));
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Commit');
    EXIT
  WHEN mytable_cur%NOTFOUND;
  END LOOP;
  CLOSE mytable_cur;
END bulk_with_limit;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s