使用JDBC从PL/SQL存储函数获取表返回值 [英] Get table return value from PL/SQL stored function using JDBC

问题描述

我有一个PL/SQL存储的函数，该函数返回一个整数表:

I have a PL/SQL stored function which returns a table of integers:

CREATE TYPE INT_TABLE IS TABLE OF INTEGER;

CREATE FUNCTION f (someParam IN INTEGER) RETURN INT_TABLE IS ...

我希望使用JDBC检索此函数的结果，以便我可以某种方式遍历整数，无论​​是ResultSet，int[]还是其他.

I wish to use JDBC to retrieve the result of this function so that I can iterate through the integers in some way, be it a ResultSet, int[], or whatever.

函数f对数据库进行修改，因此无法在诸如SELECT f(42) FROM DUAL;之类的查询中调用它.该返回值的存在是为了将其修改的行的主键通知给调用者.我大概必须使用CallableStatement，但无法确切知道如何使用.从在这里，我已经尝试过:

The function f performs modifications to the database, so it cannot be called in a query such as SELECT f(42) FROM DUAL;. The return value exists to inform the caller of the primary keys for the rows that it modified. I presumably must use a CallableStatement, but can't figure out exactly how. Taking inspiration from here, I have tried:

CallableStatement cs = conn.prepareCall("{? = call f(42)}");
cs.registerOutParameter(1, Types.ARRAY);
cs.execute();
ResultSet rs = cs.getArray(1).getResultSet();

但是，这导致在registerOutParameter行上出现java.sql.SQLException: Invalid argument(s) in call.我无法确定此错误的实际含义，因为文档对可能引发的异常情况非常笼统.

However, this resulted in java.sql.SQLException: Invalid argument(s) in call on the registerOutParameter line. I haven't been able to determine what this error actually means, as the docs are quite general on the exceptions that may be thrown.

我花了几个小时在谷歌上搜索，但对如何进行却茫然不知所措.是否可以将表类型提取到Java中?如果没有，是否还有其他方法可以从可以提取到Java中的PL/SQL函数返回多个整数?

I've spent hours googling and am at quite a loss as to how to proceed. Is it possible to extract a table type into Java? If not, is there some other way of returning multiple integers from the PL/SQL function that can be extracted into Java?

推荐答案

我想出了自己的问题.

我非常接近解决方案；我只是错过了在registerOutParameter调用中指定类型名称的机会.因此，最终的解决方案如下所示:

I was very close to the solution; I'd just missed specifying the type name in the registerOutParameter call. The final solution therefore looks like:

cs = conn.prepareCall("{? = call f(42)}");
cs.registerOutParameter(1, Types.ARRAY, "INT_TABLE");
cs.execute();
ResultSet rs = cs.getArray(1).getResultSet();

这是本机JDBC，不需要任何Oracle扩展.

This is native JDBC and does not require any of the Oracle extensions.

让我震惊的另一件事是该ResultSet的结构.它包含两列；第一个是索引，第二个是值(

One other thing that tripped me up was the structure of this ResultSet. It contains two columns; the first is the index and the second is the value (documentation link). Therefore, if you want to extract the elements of the array, you need to call rs.getInt(2) for each row rather than rs.getInt(1).

如果相反希望将结果作为纯数组而不是ResultSet，则对应的类型为java.math.BigDecimal，所以它将为

If one instead wanted the result as a plain array rather than a ResultSet, the corresponding type is java.math.BigDecimal, so it would be

...
cs.execute();
BigDecimal[] array = (BigDecimal[])(cs.getArray(1).getArray());

这篇关于使用JDBC从PL/SQL存储函数获取表返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！