在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 中是否可以获取值使用 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屋!