Oracle存储过程出参不使用DBMS_OUTPUT如何通过SELECT查询输出结果

以下方案均不需要修改现有存储过程，也不依赖DBMS_OUTPUT，返回结果符合JDBC ResultSet读取要求，支持AS自定义列别名： #

方案1：WITH FUNCTION临时封装（推荐，Oracle 12c及以上版本适用） #

这是目前最适配需求的方案，不需要创建任何持久化数据库对象，仅通过单条SELECT语句即可实现，和普通查询的输出格式完全一致。

标量出参场景示例： #

WITH
  FUNCTION get_job_name(p_job_id NUMBER) RETURN VARCHAR2
  IS
    v_job_name TIDAL.JOBMST.JOBMST_NAME%TYPE;
  BEGIN
    -- 调用原有存储过程，获取出参
    TIDAL.GETJOBNAMEFORID(p_job_id, v_job_name);
    RETURN v_job_name;
  END;
-- 普通SELECT查询，支持自定义别名，JDBC可直接读取结果集
SELECT get_job_name(1) AS Name FROM DUAL;

REF CURSOR/多行结果场景示例： #

如果存储过程出参为SYS_REFCURSOR类型的多行结果，可使用如下写法直接读取所有行：

WITH
  FUNCTION get_job_list RETURN SYS_REFCURSOR
  IS
    v_result_cursor SYS_REFCURSOR;
  BEGIN
    -- 调用原有存储过程，获取游标类型出参
    TIDAL.你的存储过程名称(入参1, 入参2, v_result_cursor);
    RETURN v_result_cursor;
  END;
SELECT * FROM TABLE(get_job_list());

方案2：低版本Oracle兼容方案（Oracle 11g及以下适用） #

如果你使用的是不支持WITH FUNCTION的低版本Oracle，需要拥有创建临时对象的权限，可通过管道表函数实现：

1. 先创建匹配出参结构的类型：

CREATE OR REPLACE TYPE job_name_rec AS OBJECT (
  Name VARCHAR2(256)
);
/
CREATE OR REPLACE TYPE job_name_tab AS TABLE OF job_name_rec;
/

2. 封装管道函数调用存储过程：

CREATE OR REPLACE FUNCTION f_get_job_name(p_job_id NUMBER) RETURN job_name_tab PIPELINED
IS
  v_job_name TIDAL.JOBMST.JOBMST_NAME%TYPE;
BEGIN
  TIDAL.GETJOBNAMEFORID(p_job_id, v_job_name);
  PIPE ROW(job_name_rec(v_job_name));
  RETURN;
END;
/

3. 直接SELECT查询结果：

SELECT * FROM TABLE(f_get_job_name(1));

如果你没有创建数据库对象的权限，低版本Oracle无法通过纯SELECT语句实现该需求，可选择以下替代方案：

• 协调运维在JDBC连接配置中开启DBMS_OUTPUT捕获逻辑，读取输出后转换为结果集
• 调整上层程序逻辑，支持通过CallableStatement注册存储过程出参读取结果

常见问题说明 #

你之前尝试的SELECT :NAME FROM DUAL写法不生效的原因是：匿名块中定义的PL/SQL变量和SQL执行层的变量是隔离的，冒号开头的绑定变量需要提前在JDBC层注册赋值，无法直接引用PL/SQL块内的变量。SQL*Plus专属的var/exec/print属于客户端命令，不属于标准Oracle SQL语法，因此无法在JDBC场景下使用。 #

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