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

2021-12-05 00:00:00 resultset oracle stored-procedures jdbc out-parameters

我正在尝试了解使用 JDBC 从 Oracle 存储过程/函数获取表数据的不同方法.六种方式如下:

I'm trying to understand different ways of getting table data from Oracle stored procedures / functions using JDBC. The six ways are the following ones:

  1. 将架构级表类型作为 OUT 参数返回的过程
  2. 将包级表类型作为 OUT 参数返回的过程
  3. 将包级游标类型作为 OUT 参数返回的过程
  4. 返回架构级表类型的函数
  5. 返回包级表类型的函数
  6. 返回包级游标类型的函数

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

Here are some examples in 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:

I have succeeded in calling 3, 4, and 6 with JDBC:

// 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

很明显，我在理解上有困难

So obviously, I'm having trouble understanding

  1. 如何从存储过程中的 OUT 参数中检索架构级和包级表类型
  2. 如何从存储的函数中检索包级表类型

我似乎找不到任何关于此的文档，因为每个人都总是使用游标而不是表类型.也许是因为不可能?不过，我更喜欢表类型，因为它们是正式定义的，可以使用字典视图(至少是模式级表类型)发现.

I can't seem to find any documentation on this, as everyone always uses cursors instead of table types. Maybe because it's not possible? I prefer table types, though, because they are formally defined and can be discovered using the dictionary views (at least the schema-level table types).

注意:显然，我可以编写一个包装函数来返回 OUT 参数和包级表类型.但我更喜欢干净的解决方案.

Note: obviously, I could write a wrapper function returning the OUT parameters and package-level table types. But I'd prefer the clean solution.

推荐答案

您无法从 java 访问 PLSQL 对象(案例 2 & 5 = 包级对象)，请参阅 "java - 在 oracle 存储过程中传递数组".但是，您可以访问 SQL 类型(案例 1 和 4).

You can't access PLSQL objects (cases 2 & 5 = package-level objects) from java, see "java - passing array in oracle stored procedure". You can however access SQL types (case 1 and 4).

从PL/SQL中获取OUT参数到java，可以使用Tom Kyte 的一个线程 使用 OracleCallableStatement.由于您检索的是 Object 表而不是 VARCHAR 表，因此您的代码将有一个额外的步骤.

To get OUT parameters from PL/SQL to java, you can use the method described in one of Tom Kyte's thread using OracleCallableStatement. Your code will have an additional step since you're retrieving a table of Object instead of a table of VARCHAR.

这是一个使用 SQL 对象表的演示，首先是设置:

Here's a demo using Table of SQL Object, first the setup:

SQL> CREATE TYPE t_type AS OBJECT (val VARCHAR(4));
  2  /
Type created

SQL> CREATE TYPE t_table AS TABLE OF t_type;
  2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE p_sql_type (p_out OUT t_table) IS
  2  BEGIN
  3     p_out := t_table(t_type('a'), t_type('b'));
  4  END;
  5  /
Procedure created

实际的java类(使用dbms_output.put_line来记录，因为我将从SQL调用它，如果从java调用则使用System.out.println):

The actual java class (using dbms_output.put_line to log because I will call it from SQL, use System.out.println if called from java):

SQL> CREATE OR REPLACE
  2  AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3  as
  4  import java.sql.*;
  5  import oracle.sql.*;
  6  import oracle.jdbc.driver.*;
  7  
  8  public class ArrayDemo {
  9     
 10     private static void log(String s) throws SQLException {
 11        PreparedStatement ps =
 12           new OracleDriver().defaultConnection().prepareStatement
 13           ( "begin dbms_output.put_line(:x); end;" );
 14        ps.setString(1, s);
 15        ps.execute();
 16        ps.close();
 17     }
 18  
 19     public static void getArray() throws SQLException {
 20  
 21        Connection conn = new OracleDriver().defaultConnection();
 22  
 23        OracleCallableStatement cs =
 24           (OracleCallableStatement)conn.prepareCall
 25           ( "begin p_sql_type(?); end;" );
 26        cs.registerOutParameter(1, OracleTypes.ARRAY, "T_TABLE");
 27        cs.execute();
 28        ARRAY array_to_pass = cs.getARRAY(1);
 29  
 30        /*showing content*/
 31        Datum[] elements = array_to_pass.getOracleArray();
 32  
 33        for (int i=0;i<elements.length;i++){
 34           Object[] element = ((STRUCT) elements[i]).getAttributes();
 35           String value = (String)element[0];
 36           log("array(" + i + ").val=" + value);
 37        }
 38     }
 39  }
 40  /
Java created

让我们称之为:

SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME 'ArrayDemo.getArray()';
  5  /

Procedure created

SQL> EXEC show_java_calling_plsql;

array(0).val=a
array(1).val=b

相关文章