CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
Tai Christian
Tai Christian

Reputation: 665

Delete duplicated records using procedure in Oracle/PLSQL

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!

Upvotes: 0

Views: 3155

Answers (5)

William Robertson
William Robertson

Reputation: 16001

Just put your SQL statement in a procedure. There's no rule that says you have to change the approach because it's PL/SQL. For example,

create or replace procedure dedupe_sometable
as
begin
    delete sometable
    where  rowid in
           ( select lag(rowid) over (partition by id order by null)
             from   sometable );
end dedupe_sometable;

Add logging etc as needed.

(Ideally this would be within a package and not a standalone procedure.)

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

Hey. As per your question, although it is not advicable to create procedure for this simpler task which can be easily done via Pure SQL. But if its really imp to make it as a stored procedure then i would suggest to use PURE SQL logic than using any kind of loop as there will be Context Switching which will have a toll on the database. Below is a snippet which i think will be useful also incorporated Analytical function to suffice your issue. Let me know if it helps.

CREATE OR REPLACE PROCEDURE Dup_DELETE
AS
BEGIN
  DELETE
  FROM EMP
  WHERE EMP.ROWID IN
  -- Assuming that i am trying to segregate the duplicate values on Empno and ename
    (SELECT A.ROWID
    FROM
      (SELECT ROW_NUMBER() OVER(PARTITION BY EMPNO,ENAME ORDER BY JOB DESC) RNK,
        empno,
        ename,
        rowid
      FROM EMP
      )A
    WHERE A.RNK <> 1
    );
END;

Upvotes: 1

SubhasisM
SubhasisM

Reputation: 352

It is not recommended to use plsql for something that can be done using plain sql. Whenever you have a combination of sql and plsql, you are switching between sql and plsql engine. So it does not make sense to incur this overhead without proper requirement.

If for some reason there is still a need for doing this, you can atleast implement bulk delete to reduce some overhead. Please refer to the code below to find out how to do that - 

DECLARE
TYPE t_del IS TABLE OF VARCHAR2(100);
l_del t_del;

CURSOR c IS
SELECT MIN(ROWID) RID
  FROM test_tbl TAB
     GROUP BY TAB.age, TAB.gender
     HAVING COUNT(*) > 1;

BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_del;
    EXIT WHEN l_del.COUNT = 0;
    FORALL i IN l_del.FIRST..l_del.last
     DELETE FROM test_tbl WHERE ROWID = l_del(i);
  END LOOP;
END;

Upvotes: 0

Nitish
Nitish

Reputation: 1736

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.

Upvotes: 1

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

If you know how to do it in SQL, better to do it in sql. PL/SQL should be used only when you cannot write specific task in SQL statement or if there is performance issues in the query and can improve by writing the logic in PL/SQL (second scenario is very rare).

If you want to write PL/SQL procedure to parameterize so that any table can be passed to delete the duplicates from it, then it makes sense. You need to dynamically generate delete statement in the procedure and execute using execute immediate.

If your intention is to learn PL/SQL, then it is programming language and you need to spend some time as if you are learning new programming language.

Upvotes: 0

Related Questions