如何在Oracle中編寫一個通用的過程來查詢和刪除任何表和列中的重複資料？

問題陳述
您希望編寫一個通用的過程來查詢和刪除Oracle中任何表和列中的重複資料。

解決方案
我們可以使用Oracle的內部ROWID值來唯一標識表中的行，以及帶有分割槽子句的OLAP函式row_number。實現此目的的示例語法如下所示。

delete from table
where rowid in
  (... query here ...)

為了演示其用法，我們將首先建立示例資料。

示例

-- table with tennis player rankings
DROP TABLE atp_stats;

CREATE TABLE atp_stats
( player_rank NUMBER NOT NULL,
  player_name VARCHAR2(100) NOT NULL,
  time_range  TIMESTAMP(6));

-- sample records
INSERT INTO atp_stats VALUES (1,'ROGER FEDERER',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (2,'RAFAEL NADAL',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (3,'NOVAK DJOKOVIC',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (4,'ANDY MURRAY',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (1,'ROGER FEDERER',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (2,'RAFAEL NADAL',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (3,'NOVAK DJOKOVIC',CURRENT_TIMESTAMP);
COMMIT;

看看我們剛剛建立的資料。

示例

SELECT * FROM atp_stats ORDER BY 2;

因此，我們插入了3個重複項，我們希望將其刪除。在我們繼續編寫Delete語句之前，讓我們瞭解一下帶有ROWID的內部查詢。

示例

SELECT rowid
   FROM (
SELECT player_rank,
  player_rank,
  rowid ,
  row_number() over (partition BY player_rank, player_name order by player_rank,player_name) AS rnk
FROM atp_stats
)
WHERE rnk > 1;

我故意將列player_rank和player_name新增到最內部的子查詢中，以使邏輯易於理解。理想情況下，可以不使用它們來編寫最內部的子查詢，效果相同。如果我們僅執行此最內部的查詢（當然，為了清晰起見，選擇了額外的列），我們會看到這些結果。

SQL返回表中所有行的rowid。然後，ROW_NUMBER()函式在由PARTITION BY指令驅動的id和player_name的集合上執行。這意味著對於每個唯一的player_rank和player_name，ROW_NUMBER將啟動我們已將其別名為rnk的行執行計數。當觀察到新的player_rank和player_name組合時，rnk計數器將重置為1。

現在，我們可以應用DELETE運算子來刪除重複值，如下所示。

SQL：刪除重複項

示例

DELETE
  FROM atp_stats
 WHERE rowid IN (
                   SELECT rowid
                     FROM(
                          SELECT player_rank,  player_name,
                            rowid ,
                            row_number() over (partition BY player_rank, player_name order by player_rank,player_name) AS rnk
                          FROM atp_stats
                         )
                     WHERE rnk > 1
                );

輸出

3 rows deleted.

由於刪除重複項是程式設計師執行的最常見任務之一，因此最好建立一個可重用過程。下面的過程將接受要從中刪除重複項的表名以及用於搜尋的列名。

首先，我們將為傳遞要分組的動態數量的列建立一個表型別。然後，我們將建立一個過程來動態刪除資料。

程式碼：刪除重複項的通用過程

示例

CREATE OR REPLACE TYPE tmp_args AS TABLE OF VARCHAR2(30);

CREATE PROCEDURE remove_duplicates
 (p_table IN VARCHAR2,
  p_cols  tmp_args)
 AS
   l_remve_dupl   CLOB ;
   l_columns      VARCHAR2(30);
   l_sql_count    NUMBER;
 
 BEGIN
   
    -- get the columns and combine them as comma seperated value
    SELECT LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP(ORDER BY COLUMN_VALUE) INTO l_columns FROM TABLE(p_cols);
   
    -- generate dynamic delete statement
    SELECT 'DELETE FROM ' || p_table  ||
           ' WHERE rowid IN (
                             SELECT rowid
                                   FROM(
                                        SELECT rowid ,
                                          row_number() OVER (partition BY ' || l_columns || ' ORDER BY ' || l_columns || ') AS rnk
                                        FROM ' || p_table || '
                                       )
                                   WHERE rnk > 1
                ) ' INTO l_remve_dupl  FROM DUAL ;
   
    EXECUTE IMMEDIATE l_remve_dupl;
    l_sql_count := SQL%ROWCOUNT;
    COMMIT;
 END;

用法

BEGIN
   remove_duplicates('ATP_STATS', tmp_args('PLAYER_RANK','PLAYER_NAME'));
END;

輸出

Kiran P

更新於： 2020年12月4日

558 次檢視

開啟您的職業生涯

透過完成課程獲得認證

開始