oracle 在java中获取pl/sql数组返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10785459/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetch pl/sql array return values in java
提问by J?cob
In Java how to get values from a pl/sqlfunction which returns an array.
在 Java 中如何从pl/sql返回数组的函数中获取值。
if my pl/sql function returns array called myArray, in java is it possible to fetch values
from myArrayinto java objects using callablestatement?
如果我的 pl/sql 函数返回调用的数组myArray，在 Java 中是否可以myArray使用callablestatement?
Thanks
谢谢
Update 1
更新 1
My Java code where I am calling function, but I am getting exception.
我在其中调用函数的 Java 代码，但出现异常。
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();
Update 2
更新 2
private final String PRODECURE_NAME = "{? = call myfunc(?,?,?)}";
and
和
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);
function
功能
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);
Fixed Update 3
固定更新 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();
采纳答案by Simon Dorociak
I didn't do that with ARRAYbut it should works. First you must register outparameter of your function. So it can be like this.
我没有这样做，ARRAY但它应该有效。首先，您必须注册out函数的参数。所以它可以是这样的。
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.
试试这个人，然后给我答案，不管它有没有。
EDIT:
编辑：
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.
意味着，您设置了三个参数 (?)，方法的第一个参数是列索引，第二个是您的特定类型的数据。
EDIT 2:
编辑2：
So sorry i wrote bad solution, already updated so check code now and try it.
很抱歉我写了错误的解决方案，已经更新，所以现在检查代码并尝试它。
回答by Chandra Sekhar
myArray returned from PL/SQL is of type java.sql.Array. You can invoke getArray()on it and type cast it to get java array.
从 PL/SQL 返回的 myArray 是java.sql.Array类型。您可以在其上调用getArray()并键入 cast 以获取java array。
String[] array = (String[]) myArray.getArray();
回答by A.B.Cade
回答by Edwin Dalorzo
The JDBC Specificationcontains a whole section (16.5) dedicated to dealing with arrays. You might want to give it a look.
该JDBC规范包含一个整体部分（16.5）专用于处理阵列。你可能想看看它。
回答by dharam
I believe you can use the following method present in the SerialArray class which is a direct descendant of java.sql.Array:
我相信您可以使用 SerialArray 类中的以下方法，它是 java.sql.Array 的直接后代：
Object getArray(Map<String, Class<?>> map)
Here the Map is a holder of an object and its property which is to be mapped to the fetched resultset in the returned array.
这里 Map 是一个对象的持有者，它的属性将被映射到返回数组中获取的结果集。
For more details see http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/serial/SerialArray.htmljavadoc
有关更多详细信息，请参阅http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/serial/SerialArray.htmljavadoc
