使用JDBC从存储过程中获取Oracle表类型

Question

我试图了解使用JDBC从Oracle存储过程/函数获取表数据的不同方法。六种方式如下：

过程返回模式级表类型作为OUT参数

程序返回包级表类型作为OUT参数

程序返回包级游标类型作为OUT参数

返回模式级表类型的函数

返回包级表类型的函数

返回包级游标类型的函数

以下是PL / SQL中的一些示例：

-- schema-level table type

CREATE TYPE t_type AS OBJECT (val VARCHAR(4));

CREATE TYPE t_table AS TABLE OF t_type;

CREATE OR REPLACE PACKAGE t_package AS

  -- package level table type

  TYPE t_table IS TABLE OF some_table%rowtype;

  -- package level cursor type

  TYPE t_cursor IS REF CURSOR;

END library_types;

-- and example procedures:

CREATE PROCEDURE p_1 (result OUT t_table);

CREATE PROCEDURE p_2 (result OUT t_package.t_table);

CREATE PROCEDURE p_3 (result OUT t_package.t_cursor);

CREATE FUNCTION f_4 RETURN t_table;

CREATE FUNCTION f_5 RETURN t_package.t_table;

CREATE FUNCTION f_6 RETURN t_package.t_cursor;

我已经成功使用JDBC调用了3、4和6：

// Not OK: p_1 and p_2

CallableStatement call = connection.prepareCall("{ call p_1(?) }");

call.registerOutParameter(1, OracleTypes.CURSOR);

call.execute(); // Raises PLS-00306. Obviously CURSOR is the wrong type

// OK: p_3

CallableStatement call = connection.prepareCall("{ call p_3(?) }");

call.registerOutParameter(1, OracleTypes.CURSOR);

call.execute();

ResultSet rs = (ResultSet) call.getObject(1); // Cursor results

// OK: f_4

PreparedStatement stmt = connection.prepareStatement("select * from table(f_4)");

ResultSet rs = stmt.executeQuery();

// Not OK: f_5

PreparedStatement stmt = connection.prepareStatement("select * from table(f_5)");

stmt.executeQuery(); // Raises ORA-00902: Invalid data type

// OK: f_6

CallableStatement call = connection.prepareCall("{ ? = call f_6 }");

call.registerOutParameter(1, OracleTypes.CURSOR);

call.execute();

ResultSet rs = (ResultSet) call.getObject(1); // Cursor results

所以很明显，我很难理解

如何从存储过程中的OUT参数中检索模式级和包级表类型

如何从存储的函数中检索包级表类型

我似乎找不到任何文档，因为每个人都总是使用游标而不是表类型。也许是因为不可能？不过，我更喜欢表类型，因为它们是形式上定义的，并且可以使用字典视图（至少是架构级别的表类型）发现。

注意：很明显，我可以编写一个包装函数，返回OUT参数和包级表类型。但是我更喜欢干净的解决方案。

Answer 1

您也可以使用以下之一public List<EmployeeBean> fetchDataFromSPForRM(String sInputDate) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;List<EmployeeBean> employeeList = new ArrayList<EmployeeBean>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Connection dbCon = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ResultSet data = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CallableStatement cstmt = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dbCon = DBUtil.getDBConnection();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String sqlQuery = "{? = call PKG_HOLD_RELEASE.FN_RM_PDD_LIST()}";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cstmt = dbCon.prepareCall(sqlQuery);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cstmt.registerOutParameter(1, OracleTypes.CURSOR);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cstmt.execute();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; data = (ResultSet) cstmt.getObject(1);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while(data.next()){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EmployeeBean employee = new EmployeeBean();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setEmpID(data.getString(1));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setSubBusinessUnitId((Integer)data.getObject(2));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setMonthOfIncentive((Integer)data.getObject(3));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setPIPStatus(data.getString(5));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setInvestigationStatus(data.getString(6));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setEmpStatus(data.getString(7));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setPortfolioPercentage((Integer)data.getObject(8));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setIncentive((Double)data.getObject(9));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setTotalSysemHoldAmt((Double)data.getObject(10));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employee.setTotalManualHoldAmt((Double)data.getObject(11));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; employeeList.add(employee);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (SQLException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }finally{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(data != null){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; data.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; data = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(cstmt != null){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cstmt.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cstmt = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(dbCon != null){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dbCon.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dbCon = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (SQLException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return employeeList;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;}