求助编写PLSQL存储过程 解决Sequence ID重复确保同ID数据为最新

原有业务逻辑依赖UPDATE操作更新目标表时，受逻辑漏判、行锁竞争等问题影响，出现同Sequence ID字段下的重复历史数据残留。需要通过PLSQL存储过程实现稳定的写入逻辑，保证任意Sequence ID对应的记录始终为最新版本，无重复数据残留。

• 废弃原有“先查再UPDATE/INSERT”的易冲突逻辑，采用「旧记录全清理 + 新记录单插入」的同事务写入模式，从流程上消除重复产生的可能
• 对待清理的目标范围加即时行锁，避免多会话并发写入同Sequence ID时出现锁等待、数据错乱的问题
• 加入严格的入参校验，从源头拦截空Sequence ID等无效入参，防止误操作引发的全表数据损坏
• 清理操作与插入操作绑定在同一事务块，任意步骤报错则整体回滚，避免出现“旧数据已删、新数据未插入”的数据空窗问题

CREATE OR REPLACE PROCEDURE P_WRITE_LATEST_SEQ_DATA(
    -- 入参：传入需要写入的Sequence ID与对应业务字段值
    p_seq_id        IN  NUMBER,
    p_biz_field1   IN  VARCHAR2(200),
    p_biz_field2   IN  DATE,
    p_biz_field3   IN  CLOB,
    -- 出参：返回操作执行结果
    p_op_result     OUT VARCHAR2
)
IS
    e_null_seq_id EXCEPTION; -- 自定义异常：空Sequence ID拦截
BEGIN
    -- 第一步：入参合法性校验
    IF p_seq_id IS NULL THEN
        RAISE e_null_seq_id;
    END IF;

    -- 第二步：同事务清除当前Sequence ID对应的所有历史残留行
    -- 加FOR UPDATE NOWAIT：命中行立即加锁，不等待其他会话锁释放，避免并发冲突
    DELETE FROM T_YOUR_BUSINESS_TABLE -- 替换为实际的业务表名
    WHERE SEQ_ID = p_seq_id -- 替换为实际的Sequence ID字段名
    FOR UPDATE NOWAIT;

    -- 第三步：插入当前最新版本的数据
    INSERT INTO T_YOUR_BUSINESS_TABLE(
        SEQ_ID,
        BIZ_FIELD1,
        BIZ_FIELD2,
        BIZ_FIELD3,
        WRITE_TIME
    ) VALUES (
        p_seq_id,
        p_biz_field1,
        p_biz_field2,
        p_biz_field3,
        SYSDATE
    );

    -- 操作完成统一提交
    COMMIT;
    p_op_result := 'Success: Latest data for seq_id '||p_seq_id||' synced';

EXCEPTION
    WHEN e_null_seq_id THEN
        ROLLBACK;
        p_op_result := 'Reject: Sequence ID cannot be empty, operation aborted';
    WHEN OTHERS THEN
        ROLLBACK;
        p_op_result := 'Failed: '||SQLERRM;
END;
/

• 可在业务表的SEQ_ID字段上创建唯一索引，从数据库约束层面做双重兜底，即使存储过程出现逻辑漏写，也无法插入同Sequence ID的重复行
• 若有历史版本追溯需求，可在删除旧记录前，将匹配行插入独立的历史归档表，在不影响主表“单Seq ID单最新行”规则的前提下，保留全量操作日志
• 若需要支持批量写入场景，可将单值入参调整为PLSQL关联数组类型，通过循环逐行执行「清理+插入」逻辑，相比逐次调用单条存储过程，可减少大量网络交互开销，提升写入性能

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