Delete duplicated records using procedure in Oracle/PLSQL

Question

As the title, I wanna create a procedure in Oracle/PLSQL to delete rows which share same values in some columns. I know how to implement it using Query, but how to do it using procedure? Do I have to use any loop? I am very new to PLSQL

Please help, thank you a lot!

Answer 1

If you want a simple procedure to delete from a particular table you can use the below piece of code:

CREATE OR REPLACE PROCEDURE DELETE_DUPLICATE AS

BEGIN

  FOR I IN (SELECT TAB.A, TAB.B, MIN(ROWID) RID
              FROM DUPLICATE_TABLE TAB
             GROUP BY TAB.A, TAB.B
            HAVING COUNT(*) > 1) LOOP

    DELETE FROM DUPLICATE_TABLE TAB
     WHERE I.RID <> TAB.ROWID
       AND TAB.A = I.A
       AND TAB.B = I.B;

    COMMIT;

  END LOOP;

END;

Here DUPLICATE_TABLE is the table having duplicate values. We are deleting rows having same values in columns A and B.