用Java调用Oracle存储过程 [英] Calling an Oracle Stored Procedure in Java

问题描述

这是我如何通过SqlDeveloper执行存储过程的方法

Here's how I'm able to execute my stored procedure through SqlDeveloper

var p refcursor;
exec DMG.Getstudentids(12342343,:p);
print p;

输出

P
-----------
STUDENT_ID
-----------
23432425
54353455

现在，我正在尝试以Java相同的方式执行存储过程.这是我的代码，我缺少有关输入/输出参数或其数据类型的信息.

Now I'm trying execute the stored procedure the same way but in Java. Here's my code and I'm missing something about the input/output parameters or their datatypes.

Connection connection = DriverManager.getConnection(url, user, pass);
CallableStatement cs = connection.prepareCall("{call DMG.Getstudentids(?,?)}");
cs.setFloat(1, 12342343);
cs.registerOutParameter(2, Types.OTHER);
cs.execute();
List<Integer> result = (List<Integer>) cs.getArray(2);

我收到以下错误

java.sql.SQLException: Invalid column type: 1111

我想我这里缺少一些基本的东西.有人看到我在哪里失败吗?谢谢.

I think I'm missing something fundamental here. Anyone see where I'm failing? Thanks.

推荐答案

尝试以下操作:

Connection connection = DriverManager.getConnection(url, user, pass);
CallableStatement cs = connection.prepareCall("{call DMG.Getstudentids(?,?)}");
cs.setFloat(1, 12342343);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.executeQuery();
ResultSet resultSet=cs.getObject(1);
List<Integer> result = new ArrayList<Integer>();
while(resultSet.next()){
 result.add(resultSet.getInt(STUDENT_ID));
}

注意:由于程序返回 refcursor ，因此需要将 OracleTypes.CURSOR 注册为输出参数.

Note : Since the procedure is returning refcursor, you need to register OracleTypes.CURSOR as output parameter.

要注意的另一件事是，您需要将整个数据集(refcursor)捕获到 Result Set 中，对其进行迭代，然后将提取的值放入 List .

Nother thing to note is you need to catch the whole dataset(refcursor) into Result Set, iterate it and put the extracted value into List.

这篇关于用Java调用Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！