为了账号安全,请及时绑定邮箱和手机立即绑定

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

/ 猿问

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

汪汪一只猫 2019-12-04 10:55:39

我试图了解使用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参数和包级表类型。但是我更喜欢干净的解决方案。


查看完整描述

3 回答

?
拉风的咖菲猫

您无法从Java访问PLSQL对象(2和5 =包级对象),请参阅“在Oracle存储过程中通过Java传递数组”。但是,您可以访问SQL类型(案例1和4)。


要从PL / SQL到Java获取OUT参数,可以使用Tom Kyte使用OracleCallableStatement 在其中一个线程中描述的方法。由于您要检索Object表而不是VARCHAR表,因此代码将需要额外的步骤。


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


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调用它,System.out.println如果从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


查看完整回答
反对 回复 2019-12-04
?
慕哥6287543

您也可以使用以下之一


public List<EmployeeBean> fetchDataFromSPForRM(String sInputDate) {


         List<EmployeeBean> employeeList = new ArrayList<EmployeeBean>();


         Connection dbCon = null;

         ResultSet data = null;

         CallableStatement cstmt = null;



         try {

                dbCon = DBUtil.getDBConnection();

                String sqlQuery = "{? = call PKG_HOLD_RELEASE.FN_RM_PDD_LIST()}";


                cstmt = dbCon.prepareCall(sqlQuery);


                cstmt.registerOutParameter(1, OracleTypes.CURSOR);


                cstmt.execute();


                data = (ResultSet) cstmt.getObject(1);              


                    while(data.next()){

                        EmployeeBean employee = new EmployeeBean();


                        employee.setEmpID(data.getString(1));

                        employee.setSubBusinessUnitId((Integer)data.getObject(2));

                        employee.setMonthOfIncentive((Integer)data.getObject(3));

                        employee.setPIPStatus(data.getString(5));

                        employee.setInvestigationStatus(data.getString(6));

                        employee.setEmpStatus(data.getString(7));

                        employee.setPortfolioPercentage((Integer)data.getObject(8));

                        employee.setIncentive((Double)data.getObject(9));

                        employee.setTotalSysemHoldAmt((Double)data.getObject(10));

                        employee.setTotalManualHoldAmt((Double)data.getObject(11));


                        employeeList.add(employee);

                    }


            } catch (SQLException e) {

                e.printStackTrace();

            }finally{

                try {


                    if(data != null){


                            data.close();               

                            data = null;

                    }

                    if(cstmt != null){


                        cstmt.close();

                        cstmt = null;

                    }

                    if(dbCon != null){


                            dbCon.close();              

                            dbCon = null;

                    }


                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }



        return employeeList;                

     }


查看完整回答
反对 回复 2019-12-04
?
慕婉清6462132

我认为您可以访问ref游标类型,因为它是通用的(所有ref游标都相同)。SQL也无法访问PL / SQL类型(例如,您的查询#5在SQL * Plus中将失败),因此我不确定jdbc无法访问这些类型是否存在缺陷或是否存在技术限制(由于某种原因,PL / SQL类型为“私有”吗?)。

查看完整回答
反对 回复 2019-12-04

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信