Oracle存储过程开发：设置9000-9999区间RESULT_CODE及错误处理

需求回顾 #

先明确你的表结构和核心需求：

表结构 #

CREATE TABLE LOG_PUSH_READOUTS_HEADERS (
    ID NUMBER NOT NULL,
    PUSH_DATE DATE NOT NULL,
    SOURCE_SERIAL VARCHAR2(100) NOT NULL,
    SOURCE_START_DATE DATE NOT NULL,
    SOURCE_END_DATE DATE NOT NULL,
    SOURCE_RUS_TYPE_ID NUMBER,
    OUTPUT_SERIAL VARCHAR2(100) NOT NULL,
    FILTERS_RUS VARCHAR2(100),
    FILTERS_INDICATORS VARCHAR2(100),
    CONSTRAINT id_pk PRIMARY KEY (ID)
);

存储过程要求 #

• 输入参数为除ID外的所有列，ID通过序列自动生成
• 输出两个参数：插入后的主键ID，以及RESULT_CODE：
  • 插入成功时RESULT_CODE为0
  • 错误时返回9000-9999区间的自定义码（比如9123对应FILTER_RUS cannot be null）

现有代码问题 #

你写的初始版本有两个关键问题：

1. INSERT语句里的列名错误地附带了参数类型（比如PUSH_DATE IN DATE），这是语法错误，列名不需要加类型声明
2. 完全没有错误处理逻辑：既没有参数合法性校验，也没有捕获执行异常，无法设置对应的错误码

修正后的完整存储过程 #

下面是完善后的代码，包含参数校验、异常捕获和自定义错误码逻辑：

CREATE OR REPLACE PROCEDURE INSERT_HEADER (
    PUSH_DATE IN DATE,
    SOURCE_SERIAL IN VARCHAR2,
    SOURCE_START_DATE IN DATE,
    SOURCE_END_DATE IN DATE,
    SOURCE_RUS_TYPE_ID IN NUMBER,
    OUTPUT_SERIAL IN VARCHAR2,
    FILTERS_RUS IN VARCHAR2,
    FILTERS_INDICATORS IN VARCHAR2,
    ID OUT NUMBER,
    RESULT_CODE OUT NUMBER
) IS
    hd_seq NUMBER;
BEGIN
    -- 初始化结果码为成功状态
    RESULT_CODE := 0;
    
    -- 1. 参数合法性校验，绑定自定义错误码
    IF PUSH_DATE IS NULL THEN
        RESULT_CODE := 9001;
        RAISE_APPLICATION_ERROR(-20001, 'PUSH_DATE cannot be null.');
    ELSIF SOURCE_SERIAL IS NULL OR SOURCE_SERIAL = '' THEN
        RESULT_CODE := 9002;
        RAISE_APPLICATION_ERROR(-20002, 'SOURCE_SERIAL cannot be null or empty.');
    ELSIF SOURCE_START_DATE IS NULL THEN
        RESULT_CODE := 9003;
        RAISE_APPLICATION_ERROR(-20003, 'SOURCE_START_DATE cannot be null.');
    ELSIF SOURCE_END_DATE IS NULL THEN
        RESULT_CODE := 9004;
        RAISE_APPLICATION_ERROR(-20004, 'SOURCE_END_DATE cannot be null.');
    ELSIF OUTPUT_SERIAL IS NULL OR OUTPUT_SERIAL = '' THEN
        RESULT_CODE := 9005;
        RAISE_APPLICATION_ERROR(-20005, 'OUTPUT_SERIAL cannot be null or empty.');
    -- 如果你需要强制校验FILTERS_RUS非空，取消下面的注释
    -- ELSIF FILTERS_RUS IS NULL OR FILTERS_RUS = '' THEN
    --     RESULT_CODE := 9123;
    --     RAISE_APPLICATION_ERROR(-20123, 'FILTERS_RUS cannot be null.');
    END IF;
    
    -- 2. 生成序列主键值
    SELECT AMM_MDM.Header_Seq.NEXTVAL INTO hd_seq FROM DUAL;
    ID := hd_seq;
    
    -- 3. 执行插入操作（修正列名语法错误）
    INSERT INTO AMM_MDM.LOG_PUSH_READOUTS_HEADERS (
        ID,
        PUSH_DATE,
        SOURCE_SERIAL,
        SOURCE_START_DATE,
        SOURCE_END_DATE,
        SOURCE_RUS_TYPE_ID,
        OUTPUT_SERIAL,
        FILTERS_RUS,
        FILTERS_INDICATORS
    ) VALUES (
        hd_seq,
        PUSH_DATE,
        SOURCE_SERIAL,
        SOURCE_START_DATE,
        SOURCE_END_DATE,
        SOURCE_RUS_TYPE_ID,
        OUTPUT_SERIAL,
        FILTERS_RUS,
        FILTERS_INDICATORS
    );
    
    COMMIT; -- 可根据业务场景调整是否自动提交

EXCEPTION
    -- 捕获所有异常，统一处理错误码
    WHEN OTHERS THEN
        -- 如果是参数校验阶段已经设置的自定义错误码，保持不变；否则设置通用错误码
        IF RESULT_CODE = 0 THEN
            RESULT_CODE := 9999;
        END IF;
        -- 可选：这里可以添加错误日志记录逻辑
        ROLLBACK; -- 发生错误时回滚事务，保证数据一致性
END INSERT_HEADER;

代码逻辑说明 #

• 参数校验：在插入前先检查必填参数的合法性，每个不合法场景对应一个9000-9999区间的错误码，通过RAISE_APPLICATION_ERROR抛出异常触发后续处理
• 序列生成：正确获取序列的下一个值并赋值给输出参数ID
• 插入语句：修正了列名的语法错误，明确指定ID列并传入序列值
• 异常处理：
  • 捕获所有异常，避免直接抛出系统错误给调用方
  • 保留参数校验阶段的自定义错误码，未预见的错误（比如主键冲突、数据类型不匹配）则使用通用错误码9999
  • 错误发生时回滚事务，确保数据一致性
• 成功状态：所有逻辑执行完成后，RESULT_CODE保持初始值0，表示插入成功

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