如何创建Oracle存储过程提取重复数据中的唯一记录并插入新行

需求说明 #

现有temp表结构及数据如下：

需实现：针对指定key（示例为123）的重复记录，提取唯一的address记录，插入到表末尾，且将ver_id设为-1。执行后期望新增记录：

原尝试代码 #

仅能查询指定key的记录，未实现插入逻辑：

create or replace PROCEDURE demo (
    key1 IN VARCHAR2
) AS
    CURSOR c_temp IS
    SELECT
        *
    FROM
        temp
    WHERE
        key = key1;
 r_temp c_temp%ROWTYPE;
BEGIN
    OPEN c_temp;
    LOOP
        FETCH c_temp INTO r_temp;
        EXIT WHEN c_temp%notfound;
        dbms_output.put_line('id: '
                             || r_temp.id
                             || ' address: '
                             || r_temp.address);

    END LOOP;

    CLOSE c_temp ;
END;

修改后的实现代码 #

create or replace PROCEDURE demo (
    key1 IN VARCHAR2
) AS
    v_new_id NUMBER;
BEGIN
    -- 生成新ID：若表未用自增序列，取最大ID+1；若有自增序列，替换为「序列名.NEXTVAL」
    SELECT COALESCE(MAX(id), 0) + 1 INTO v_new_id FROM temp;
    
    -- 提取指定key下的唯一address记录，插入表中并设置ver_id=-1
    INSERT INTO temp (id, address, key, ver_id)
    SELECT v_new_id, address, key1, -1
    FROM temp
    WHERE key = key1
    -- 按address去重；若需忽略空格差异，替换为GROUP BY REGEXP_REPLACE(address, '\s+', ' ')
    GROUP BY address
    -- 可选：优先取出现次数最多的address版本
    ORDER BY COUNT(*) DESC
    FETCH FIRST 1 ROW ONLY;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('已成功插入唯一记录，新ID为: ' || v_new_id);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('执行失败：' || SQLERRM);
END;

代码说明 #

1. ID生成：用COALESCE(MAX(id),0)+1确保空表也能生成有效ID；若表使用Oracle自增序列（如temp_id_seq），直接替换为temp_id_seq.NEXTVAL即可。
2. 去重逻辑：通过GROUP BY address提取唯一地址；如果需要忽略地址中的空格差异（比如多个空格视为一个），将GROUP BY address改为GROUP BY REGEXP_REPLACE(address, '\s+', ' ')，同时SELECT中的address也替换为该表达式。
3. 事务处理：添加提交/回滚逻辑，保证操作原子性，同时输出执行结果或错误信息。
4. 性能优化：替换原游标循环为INSERT...SELECT批量操作，避免逐行处理的性能损耗。

注意事项 #

• 若temp表的id是Oracle 12c+的IDENTITY自增列，INSERT时可省略id字段，由数据库自动生成。
• 若需提取所有不同的address版本（而非仅一个），去掉ORDER BY COUNT(*) DESC和FETCH FIRST 1 ROW ONLY，同时ID生成需改为每条记录生成唯一值（比如用序列循环生成）。

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