如何编写通用过程以查找和删除Oracle中任何表和列中的重复项？

问题陈述：
您想编写一个通用过程来查找和删除Oracle中任何表和列中的重复项。

解决方案：
我们可以使用Oracle的内部ROWID值与OLAP函数row_number with partition子句一起唯一标识表中的行。实现此目的的示例语法如下所示。

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;

输出结果