<bdo id='WXct0'></bdo><ul id='WXct0'></ul>
      <legend id='WXct0'><style id='WXct0'><dir id='WXct0'><q id='WXct0'></q></dir></style></legend>
    1. <small id='WXct0'></small><noframes id='WXct0'>

      <tfoot id='WXct0'></tfoot>

    2. <i id='WXct0'><tr id='WXct0'><dt id='WXct0'><q id='WXct0'><span id='WXct0'><b id='WXct0'><form id='WXct0'><ins id='WXct0'></ins><ul id='WXct0'></ul><sub id='WXct0'></sub></form><legend id='WXct0'></legend><bdo id='WXct0'><pre id='WXct0'><center id='WXct0'></center></pre></bdo></b><th id='WXct0'></th></span></q></dt></tr></i><div id='WXct0'><tfoot id='WXct0'></tfoot><dl id='WXct0'><fieldset id='WXct0'></fieldset></dl></div>
    3. 使用 JDBC 从存储过程中获取 Oracle 表类型

      Fetch Oracle table type from stored procedure using JDBC(使用 JDBC 从存储过程中获取 Oracle 表类型)
        <bdo id='JWLZ4'></bdo><ul id='JWLZ4'></ul>
        <legend id='JWLZ4'><style id='JWLZ4'><dir id='JWLZ4'><q id='JWLZ4'></q></dir></style></legend>

                <small id='JWLZ4'></small><noframes id='JWLZ4'>

                <tfoot id='JWLZ4'></tfoot>
                  <tbody id='JWLZ4'></tbody>

                <i id='JWLZ4'><tr id='JWLZ4'><dt id='JWLZ4'><q id='JWLZ4'><span id='JWLZ4'><b id='JWLZ4'><form id='JWLZ4'><ins id='JWLZ4'></ins><ul id='JWLZ4'></ul><sub id='JWLZ4'></sub></form><legend id='JWLZ4'></legend><bdo id='JWLZ4'><pre id='JWLZ4'><center id='JWLZ4'></center></pre></bdo></b><th id='JWLZ4'></th></span></q></dt></tr></i><div id='JWLZ4'><tfoot id='JWLZ4'></tfoot><dl id='JWLZ4'><fieldset id='JWLZ4'></fieldset></dl></div>

                本文介绍了使用 JDBC 从存储过程中获取 Oracle 表类型的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我正在尝试了解使用 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
                

                这篇关于使用 JDBC 从存储过程中获取 Oracle 表类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM(Oracle PL/SQL - 使用自定义 SQLERRM 引发用户定义的异常)
                Oracle: is there a tool to trace queries, like Profiler for sql server?(Oracle:是否有跟踪查询的工具,例如用于 sql server 的 Profiler?)
                SELECT INTO using Oracle(使用 Oracle SELECT INTO)
                How to handle Day Light Saving in Oracle database(如何在 Oracle 数据库中处理夏令时)
                PL/SQL - Use quot;Listquot; Variable in Where In Clause(PL/SQL - 使用“列表Where In 子句中的变量)
                Oracle: Import CSV file(Oracle:导入 CSV 文件)
                <i id='KizQG'><tr id='KizQG'><dt id='KizQG'><q id='KizQG'><span id='KizQG'><b id='KizQG'><form id='KizQG'><ins id='KizQG'></ins><ul id='KizQG'></ul><sub id='KizQG'></sub></form><legend id='KizQG'></legend><bdo id='KizQG'><pre id='KizQG'><center id='KizQG'></center></pre></bdo></b><th id='KizQG'></th></span></q></dt></tr></i><div id='KizQG'><tfoot id='KizQG'></tfoot><dl id='KizQG'><fieldset id='KizQG'></fieldset></dl></div>
              1. <legend id='KizQG'><style id='KizQG'><dir id='KizQG'><q id='KizQG'></q></dir></style></legend>
                  • <bdo id='KizQG'></bdo><ul id='KizQG'></ul>

                        <tbody id='KizQG'></tbody>

                        <small id='KizQG'></small><noframes id='KizQG'>

                      1. <tfoot id='KizQG'></tfoot>