在java中获取pl / sql数组返回值 [英] Fetch pl/sql array return values in java

问题描述

在Java中如何从 pl / sql 函数中获取值，该函数返回一个数组。

In Java how to get values from a pl/sql function which returns an array.

如果我的话pl / sql函数返回名为 myArray 的数组，在java中可以从 myArray 获取值
java对象使用 callablestatement ？

if my pl/sql function returns array called myArray, in java is it possible to fetch values from myArray into java objects using callablestatement?

谢谢

更新1

我调用函数的Java代码，但我得到例外。

My Java code where I am calling function, but I am getting exception.

PLS-00306: wrong number or types of arguments in call to 'myfunc'




connection = con.getConnection();
        callablestatement = connection.prepareCall("{call myfunc(?,?,?}");
        callablestatement.setInt(1, param1);
        callablestatement.setInt(2, param2);
        callablestatement.setString(3, param3);
        callablestatement.registerOutParameter(4, Types.ARRAY);
        callablestatement.execute();        
        resultSet = callablestatement.getArray(4).getResultSet();

更新2

private final String PRODECURE_NAME = "{? = call myfunc(?,?,?)}";

和

connection = con.getConnection();
    callablestatement = connection.prepareCall(PRODECURE_NAME);
    callablestatement.registerOutParameter(1, Types.ARRAY);
    callablestatement.setInt(2, param1);
    callablestatement.setInt(3, param2);
    callablestatement.setString(4, param3);

    callablestatement.execute();

create or replace type dates
       is varray(100) of varchar2(32);

功能

CREATE OR REPLACE function myfunc (    
    p_id    IN number,
    p_c_id     IN number,
    p_co_no     IN number

)
    RETURN dates
AS
    myarray contract_dates;
    par1        VARCHAR2 (32);
    par2        VARCHAR2 (32);

固定更新3

connection = con.getConnection();
        callablestatement = 
                connection.prepareCall("begin ? :=myfunc(?,?,?); end;");
        callablestatement.registerOutParameter(1, OracleTypes.ARRAY, "DATES");
        callablestatement.setInt(2, param1);
        callablestatement.setInt(3, param2);
        callablestatement.setString(4, param3);
        callablestatement.execute();

推荐答案

我没有用 ARRAY 但它应该有效。首先，您必须注册函数的 out 参数。所以它可以是这样的。

I didn't do that with ARRAY but it should works. First you must register out parameter of your function. So it can be like this.

private final String PRODECURE_NAME = "{? = call <ProcedureName>(?,?,?)}";

Connection con = null;
CallableStatement cs = null;

       try {
            con = DAOFactory.getDatabaseConnection();
            cs = con.prepareCall(PRODECURE_NAME);
            cs.registerOutParameter(1, java.sql.Types.ARRAY);
            cs.setYourType(2, <yourData>);
            cs.setYourType(3, <yourData>);
            cs.setYourType(4, <yourData>);
            cs.execute();
            Array arr = cs.getArray(1);
            if (arr != null) {
               String[] data = (String[]) arr.getArray();
            }
        } 
        catch (SQLException ex) {
            Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
            try {
                con.rollback();
            }
        }
        finally {
            if (con != null) {
                try {
                    con.close();
                } 
                catch (SQLException ex) {
                    Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

试试这位男士并给我然后再说不管了。

Try this man and give me then asnwer wether it do or not.

编辑：

这些字符？表示您将设置的一个参数（它被命名为参数化）。
所以这个：

These char ? represents one parameter that you will set(it's named as parametrized). So this:

cs.setYourType(2, <yourData>);
cs.setYourType(3, <yourData>);
cs.setYourType(4, <yourData>);

表示你设置三个参数（？），方法的第一个参数是列索引，第二个参数你是特定类型的数据。

means, that you set your three parameters (?), first parameter of method is column index and second are you data of your specific type.

编辑2：

很抱歉，我写了一个糟糕的解决方案，已经更新，所以现在检查代码并试一试。

So sorry i wrote bad solution, already updated so check code now and try it.

这篇关于在java中获取pl / sql数组返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！