Oracle存储过程重复添加相同store_code未触发提示问题

问题根源 #

1. 语法错误导致存储过程无法正常执行：UPDATE语句中CITY = P_CITY,末尾多了逗号，这会引发编译错误，存储过程根本无法正常运行，自然不会触发后续的重复提示逻辑。
2. 插入逻辑的参数覆盖问题：插入时先执行RETURNING RRSOC_ID INTO TBLDATA;，随后又给TBLDATA赋值成功提示，虽不影响重复提示，但逻辑存在冲突，丢失了返回的ID值。
3. 事务处理不规范：分支内直接commit不利于调用方统一管理事务，异常处理仅做rollback，未返回错误信息，排查问题困难。

修复后的完整代码 #

PROCEDURE INSERT_INTO_RRSOC_MST
(    
    P_STORE_CODE IN NVARCHAR2,
    P_STATE IN NVARCHAR2,
    P_CITY IN NVARCHAR2,
    P_Indication IN NUMBER,
    TBLDATA OUT NVARCHAR2
) 
AS
    V_RRSOC_COUNT NUMBER:=0;
BEGIN
    -- 用COUNT(*)统计更通用，避免主键字段的特殊情况
    SELECT COUNT(*) INTO V_RRSOC_COUNT 
    FROM TBL_RRSOC_STORE_INFO 
    WHERE STORE_CODE = P_STORE_CODE AND isactive = 'Y';

    IF V_RRSOC_COUNT > 0 AND P_Indication = 1 THEN
        -- 修复UPDATE语句的多余逗号语法错误
        UPDATE TBL_RRSOC_STORE_INFO 
        SET                                
            STATE = P_STATE,
            CITY = P_CITY
        WHERE STORE_CODE = P_STORE_CODE;   
        -- 建议由调用方控制事务，移除分支内的commit
        TBLDATA := 'Record Updated Successfully';
    ELSE
        IF V_RRSOC_COUNT = 0 AND P_Indication = 0 THEN
            INSERT INTO TBL_RRSOC_STORE_INFO      
            (
                STORE_CODE,
                STATE,     
                CITY                                       
            )
            VALUES
            (            
                P_STORE_CODE,
                P_STATE,
                P_CITY
            ) 
            -- 用临时变量接收返回的ID，避免覆盖提示信息
            RETURNING RRSOC_ID INTO V_RRSOC_COUNT;
            TBLDATA := 'Record Saved Successfully. ID: ' || V_RRSOC_COUNT;
        ELSE
            TBLDATA := 'Record already exist';
        END IF;
    END IF;

EXCEPTION 
    WHEN OTHERS THEN
        ROLLBACK;
        -- 添加错误信息返回，方便调试
        TBLDATA := 'Error: ' || SQLERRM;
END INSERT_INTO_RRSOC_MST;

额外说明 #

• 如果你的需求是不管isactive状态，只要store_code重复就不允许插入，需要删除WHERE条件中的AND isactive = 'Y'，确保所有相同store_code的记录都会被检测到。
• 移除分支内的commit是为了让调用方统一管理事务，避免嵌套事务带来的问题，若必须在存储过程内提交，可根据业务场景调整。

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