使用CallableStatement调用Oracle存储过程获取输出报错如何解决

错误根因 #

• 存储过程定义缺陷：当前NETSALARY存储过程仅定义了1个IN类型入参EMP_ID，计算得到的员工ID、姓名、净薪资均为过程内部局部变量，仅通过DBMS_OUTPUT.PUT_LINE输出到Oracle服务端缓冲区，没有定义任何OUT类型返回参数，也没有返回游标结果集，JDBC客户端无法直接通过参数索引获取这些值。
• JDBC调用逻辑错误：代码中调用cst.getInt(1)时，索引1对应的是绑定的入参员工ID，并非可读取的输出参数或结果集列，直接触发无效列索引异常；尝试转换ResultSet读取无结果，是因为该存储过程执行时不会向客户端返回任何结果集对象。

解决方案 #

二选一即可，优先推荐方案1，数据读取更规范。

方案1：调整存储过程增加OUT参数，适配JDBC结构化读取 #

修改存储过程定义，新增3个OUT参数承载返回值，修改后代码如下：

CREATE OR REPLACE PROCEDURE NETSALARY 
(
  EMP_ID IN NUMBER,
  OUT_EMP_ID OUT employees.employee_id%TYPE,
  OUT_EMP_NAME OUT employees.first_name%TYPE,
  OUT_NET_SAL OUT number(9,2)
) AS 
BEGIN
  select employee_id, first_name, 
  case
      when (commission_pct is null) 
          then 0.9*salary
      when (commission_pct*salary)<500 
          then 0.85*(salary + commission_pct*salary)
      else 
          0.8*(salary + commission_pct*salary)
  end net_salary
  into
  OUT_EMP_ID, OUT_EMP_NAME, OUT_NET_SAL
  from employees
  where employee_id = emp_id;
  -- 若无服务端日志需求可注释下行打印逻辑
  dbms_output.put_line(OUT_EMP_ID||' , '||OUT_EMP_NAME||' , '||OUT_NET_SAL);
END NETSALARY;

对应修改JDBC调用代码，注册OUT参数后再取值：

import java.sql.*;

public class UCST {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "user";
        String pwd = "password";
        // 用try-with-resources语法自动释放资源，无需手动调用close
        try (
            Connection conn = DriverManager.getConnection(url, user, pwd);
            CallableStatement cst = conn.prepareCall("{call netsalary(?, ?, ?, ?)}")
        ) {
            // 绑定第1个入参：查询的员工ID
            cst.setInt(1, 127);
            // 注册第2-4个OUT参数的JDBC类型
            cst.registerOutParameter(2, Types.NUMERIC);
            cst.registerOutParameter(3, Types.VARCHAR);
            cst.registerOutParameter(4, Types.NUMERIC);
            cst.execute();
            // 按OUT参数的索引位置取值，*注意薪资是2位小数，不要用getInt读取避免精度丢失*
            System.out.println(cst.getInt(2) + ", " + cst.getString(3) + ", " + cst.getDouble(4));
        } catch (Exception e) {
            System.out.println("Connection could not be established....");
            e.printStackTrace();
        }
    }
}

方案2：不修改存储过程，直接读取DBMS_OUTPUT缓冲区内容 #

如果不想调整现有存储过程逻辑，可以在JDBC中先开启DBMS_OUTPUT开关，存储过程执行完成后逐行读取缓冲区拿到打印内容，示例代码如下：

import java.sql.*;

public class UCST {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "user";
        String pwd = "password";
        try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
            // 先开启DBMS_OUTPUT，设置缓冲区大小为1M
            try (Statement enableStmt = conn.createStatement()) {
                enableStmt.execute("BEGIN DBMS_OUTPUT.ENABLE(1000000); END;");
            }
            // 执行原有存储过程调用
            try (CallableStatement cst = conn.prepareCall("{call netsalary(?)}")) {
                cst.setInt(1, 127);
                cst.execute();
            }
            // 读取DBMS_OUTPUT缓冲区的打印内容
            try (CallableStatement readOutStmt = conn.prepareCall(
                    "DECLARE " +
                    "  l_line VARCHAR2(4000); " +
                    "  l_done NUMBER; " +
                    "BEGIN " +
                    "  LOOP " +
                    "    DBMS_OUTPUT.GET_LINE(l_line, l_done); " +
                    "    EXIT WHEN l_done = 1; " +
                    "    ? := l_line; " +
                    "  END LOOP; " +
                    "END;"
            )) {
                readOutStmt.registerOutParameter(1, Types.VARCHAR);
                while (true) {
                    readOutStmt.execute();
                    String line = readOutStmt.getString(1);
                    if (line == null) break;
                    // 输出拿到的打印结果，格式为 员工ID , 姓名 , 净薪资
                    System.out.println(line);
                }
            }
        } catch (Exception e) {
            System.out.println("Connection could not be established....");
            e.printStackTrace();
        }
    }
}

该方案拿到的是存储过程拼接好的字符串，若需要拆分独立字段需自行做字符串分割，适合不想修改数据库对象的临时场景，生产环境优先用方案1。

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