如何编写通用过程以查找和删除Oracle中任何表和列中的重复项？
问题陈述：
您想编写一个通用过程来查找和删除Oracle中任何表和列中的重复项。
解决方案：
我们可以使用Oracle的内部ROWID值与OLAP函数row_numberwithpartition子句一起唯一标识表中的行。实现此目的的示例语法如下所示。
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()函数可以处理由PARTITIONBY指令驱动的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;输出结果