Oracle PL/SQL存储过程如何返回输出结果至MSG变量

你的代码有几处语法和逻辑错误，无法实现返回执行消息的需求：

• Oracle 存储过程（PROCEDURE）不支持用RETURN子句定义返回值类型，只有自定义函数（FUNCTION）支持该语法
• 定义的程序单元入参共2个，调用时仅传入1个参数，参数个数不匹配
• 异常块结构写错了，正常执行路径的返回逻辑放在了异常块作用域外后方，永远不会触发
• 逻辑错误：把查询到的姓名赋值给了入参MY_NAME，INSERT语句直接写FIRSTNAME字段名，取不到查询到的值
• 带有INSERT这类DML操作的程序单元，不能直接放在SELECT ... FROM DUAL里调用

用OUT类型参数返回消息是存储过程的标准写法，不需要改成函数。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE CHECKFILED
(
    P_MY_ID   EMP.ID%TYPE, -- 仅需要传入ID作为入参
    P_MSG OUT VARCHAR2     -- OUT类型参数专门用来返回执行消息
)
IS 
     V_MY_NAME EMP.FIRSTNAME%TYPE; -- 内部变量存储查询到的姓名
BEGIN
     -- 根据传入ID查询对应姓名
     SELECT FIRSTNAME INTO V_MY_NAME FROM EMP WHERE ID = P_MY_ID;

     IF V_MY_NAME IS NOT NULL THEN 
          -- 插入查询到的数据
          INSERT INTO CUSTOMER (ID, FIRSTNAME) VALUES (UID, V_MY_NAME);
          P_MSG := 'FIELD IS NOT EMPTY';
     ELSE
          RAISE_APPLICATION_ERROR(-20000,'FIELD IS EMPTY'); 
     END IF; 

     COMMIT; -- 执行成功提交事务，需要手动控制事务可以把这行移到调用侧
EXCEPTION 
    WHEN OTHERS THEN
        ROLLBACK; -- 异常时回滚事务
        P_MSG := SQLCODE || '-' || SQLERRM;  
END; 
/

如果需要在调用侧统一控制事务提交/回滚，可以删掉存储过程里的COMMIT和ROLLBACK语句。

调用代码 #

DECLARE 
     MSG VARCHAR2(4000 CHAR);
BEGIN  
     -- 传入ID，MSG变量接收OUT参数返回的消息
     CHECKFILED(P_MY_ID => '10A', P_MSG => MSG);
     -- 这里直接用MSG变量即可，示例为输出结果
     DBMS_OUTPUT.PUT_LINE('执行结果：' || MSG);
END;
/

执行后MSG变量会自动存储对应结果：执行成功时存入FIELD IS NOT EMPTY，执行失败时存入错误码+错误描述。

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