Oracle中删除重复行指定值记录并实现POId筛选存储过程

需求说明 #

• 仅当某行属于重复行（同一POId存在多条记录）时，删除其中ToDelete为'D'的记录；
• 创建存储过程，返回唯一的POId数据：若同一POId同时存在ToDelete为'D'和'N'的记录，仅保留ToDelete为'N'的行；若只有'D'记录则保留该行。

测试表结构与初始化数据 #

首先创建测试表并插入数据（注意替换原语句中的中文引号为英文引号）：

Create Table testpo
( 
    poid Varchar2(10),
    ToDelete Varchar2(10)
);

Insert into testpo values ('PO1', 'D');
Insert into testpo values ('PO1', 'N');
Insert into testpo values ('PO2', 'D');
Insert into testpo values ('PO3', 'D');

一、删除重复行中特定值的记录 #

使用DELETE语句结合子查询，仅删除那些存在对应'N'记录的POId的'D'行：

DELETE FROM testpo t1
WHERE t1.ToDelete = 'D'
AND EXISTS (
    SELECT 1 FROM testpo t2
    WHERE t2.poid = t1.poid
    AND t2.ToDelete = 'N'
);

执行后，表中数据将符合期望：PO1仅保留'N'，PO2、PO3保留'D'。

二、创建存储过程返回目标数据 #

如果不需要修改原表，而是直接返回处理后的结果，可创建如下存储过程，通过游标输出唯一POId数据：

CREATE OR REPLACE PROCEDURE get_unique_poid
IS
    CURSOR c_poid IS
        SELECT poid, ToDelete
        FROM (
            SELECT 
                poid, 
                ToDelete,
                ROW_NUMBER() OVER (PARTITION BY poid ORDER BY CASE ToDelete WHEN 'N' THEN 1 ELSE 2 END) AS rn
            FROM testpo
        )
        WHERE rn = 1;
    v_poid testpo.poid%TYPE;
    v_todelete testpo.ToDelete%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('POId  ToDelete');
    DBMS_OUTPUT.PUT_LINE('-------------');
    OPEN c_poid;
    LOOP
        FETCH c_poid INTO v_poid, v_todelete;
        EXIT WHEN c_poid%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_poid || '  ' || v_todelete);
    END LOOP;
    CLOSE c_poid;
END;
/

调用存储过程 #

执行以下语句开启输出并调用存储过程：

SET SERVEROUTPUT ON;
EXEC get_unique_poid;

调用后将输出：

POId  ToDelete
-------------
PO1  N
PO2  D
PO3  D

验证结果 #

执行查询语句确认处理后的数据：

SELECT poid, ToDelete FROM testpo ORDER BY poid;

将返回期望的结果集：

PO1 N
PO2 D
PO3 D

内容的提问来源于stack exchange，提问作者Melwin r