CallableStatement + registerOutParameter +多行结果 [英] CallableStatement + registerOutParameter + multiple row result
问题描述
我有以下形式的SQL语句:
I've got a SQL statement of the form:
BEGIN\n
UPDATE tab
SET stuff
WHERE stuff
RETURNING intA, intB, stringC
INTO ?,?,?
我已经注册了适当的Out参数.
I've registered the appropriate Out parameters.
在这里，我有一些问题:我叫stmt.executeQuery()还是stmt.execute()?此外，我知道通过普通的SELECT查询，我可以遍历resultSet并填充我的对象-多行Out参数等效于什么?
Here's where I have some questions: Do I call stmt.executeQuery() or stmt.execute()? Further, I know with a normal SELECT query I can loop through the resultSet and populate my object -- what's the equivalent for multiple rows of Out parameters?
也许我可以注册CURSOR类型的单个out参数并遍历此结果.
Perhaps I can register a single out parameter of type CURSOR and loop over this result.
我是否可能需要循环使用多个resultSet? 谢谢！
Could I potentially have multiple resultSet's that I need to loop over? Thanks!
推荐答案
我相信您可以实现所需的功能，但是您将需要处理PL/SQL数组而不是游标或结果集.下面是一个演示.
I believe you can achieve what you are looking for, but you will need to handle PL/SQL arrays rather than cursors or result sets. Below is a demonstration.
我有一个名为TEST的表，其结构如下:
I have a table, called TEST, with the following structure:
SQL> desc test;
Name Null? Type
----------------------------------------- -------- -----------------
A NUMBER(38)
B NUMBER(38)
C NUMBER(38)
并包含以下数据:
SQL> select * from test;
A B C
---------- ---------- ----------
1 2 3
4 5 6
7 8 9
我需要为使用的每种列创建一个数组类型.在这里，我只有NUMBER个，但是如果您还有一个或多个VARCHAR2列，则也需要为它们创建一个类型.
I need to create an array type for each type of column used. Here, I only have NUMBERs, but if you have one or more VARCHAR2 columns as well, you'll need to create a type for those too.
SQL> create type t_integer_array as table of integer;
2 /
Type created.
表和任何必要的类型都是我们需要在数据库中设置的.完成此操作后，我们可以编写一个简短的Java类，该类执行UPDATE ... RETURNING ...并将多个值返回给Java:
The table and any necessary types are all we need to set up in the database. Once we've done that, we can write a short Java class that does an UPDATE ... RETURNING ..., returning multiple values to Java:
import java.math.BigDecimal;
import java.util.Arrays;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class UpdateWithBulkReturning {
public static void main(String[] args) throws Exception {
Connection c = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE", "user", "password");
c.setAutoCommit(false);
/* You need BULK COLLECT in order to return multiple rows. */
String sql = "BEGIN UPDATE test SET a = a + 10 WHERE b <> 5 " +
"RETURNING a, b, c BULK COLLECT INTO ?, ?, ?; END;";
CallableStatement stmt = c.prepareCall(sql);
/* Register the out parameters. Note that the third parameter gives
* the name of the corresponding array type. */
for (int i = 1; i <= 3; ++i) {
stmt.registerOutParameter(i, Types.ARRAY, "T_INTEGER_ARRAY");
}
/* Use stmt.execute(), not stmt.executeQuery(). */
stmt.execute();
for (int i = 1; i <= 3; ++i) {
/* stmt.getArray(i) returns a java.sql.Array for the output parameter in
* position i. The getArray() method returns the data within this
* java.sql.Array object as a Java array. In this case, Oracle converts
* T_INTEGER_ARRAY into a Java BigDecimal array. */
BigDecimal[] nums = (BigDecimal[]) (stmt.getArray(i).getArray());
System.out.println(Arrays.toString(nums));
}
stmt.close();
c.rollback();
c.close();
}
}
运行此命令时，将得到以下输出:
When I run this, I get the following output:
C:\Users\Luke\stuff>java UpdateWithBulkReturning
[11, 17]
[2, 8]
[3, 9]
显示的输出分别是从A，B和C列返回的值.每列只有两个值，因为我们用B等于5过滤了该行.
The outputs displayed are the values returned from the columns A, B and C respectively. There are only two values for each column since we filtered out the row with B equal to 5.
您可能希望按行分组而不是按列分组的值.换句话说，您可能希望输出包含[11, 2, 3]和[17, 8, 9].如果那是您想要的，恐怕您需要自己完成这一部分.
You might want the values grouped by row instead of grouped by column. In other words, you might want the output to contain [11, 2, 3] and [17, 8, 9] instead. If that's what you want, I'm afraid you'll need to do that part yourself.
这篇关于CallableStatement + registerOutParameter +多行结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
