如何在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 次查看

开启您的职业生涯

通过完成课程获得认证

开始