oracle CallableStatement + registerOutParameter + 多行结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5942203/
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
CallableStatement + registerOutParameter + multiple row result
提问by Dirk
I've got a SQL statement of the form:
我有以下形式的 SQL 语句：
BEGIN\n
UPDATE tab
SET stuff
WHERE stuff
RETURNING intA, intB, stringC
INTO ?,?,?
I've registered the appropriate Out parameters.
我已经注册了适当的 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?
这是我有一些问题的地方：我应该调用 stmt.executeQuery() 还是 stmt.execute()？此外，我知道使用普通的 SELECT 查询，我可以遍历 resultSet 并填充我的对象——多行 Out 参数的等效项是什么？
EDIT: Perhaps I can register a single out parameter of type CURSOR and loop over this result.
编辑：也许我可以注册一个 CURSOR 类型的单出参数并循环遍历这个结果。
EDIT2: Could I potentially have multiple resultSet's that I need to loop over? Thanks!
EDIT2：我可能有多个需要循环的结果集吗？谢谢！
回答by Michael Rickman
To build upon what Luke Woodward answered and to refine my previous answer, you can create an Oracle type, use it to temporarily store data, and then return a sys_refcursor with your updates.
为了建立在 Luke Woodward 回答的基础上并完善我之前的回答，您可以创建一个 Oracle 类型，使用它来临时存储数据，然后返回一个带有更新的 sys_refcursor。
Create the new type:
创建新类型：
CREATE OR REPLACE TYPE rowid_tab AS TABLE OF varchar2(30);
Create the database function:
创建数据库函数：
CREATE OR REPLACE
FUNCTION update_tab
RETURN sys_refcursor
IS
ref_cur sys_refcursor;
v_tab rowid_tab;
BEGIN
UPDATE tab
SET intA = intA+2
, intB = intB*2
, stringC = stringC||' more stuff.'
RETURNING ROWID
BULK COLLECT INTO v_tab;
OPEN ref_cur FOR
WITH DATA AS (SELECT * FROM TABLE(v_tab))
SELECT intA, intB, stringC
FROM tab
where rowid in (select * from data);
RETURN ref_cur;
END;
Now, call the function in your java:
现在，在您的 java 中调用该函数：
import java.math.BigDecimal;
import java.util.Arrays;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class StructTest {
public static void main(String[] args)
throws Exception
{
System.out.println("Start...");
ResultSet results = null;
Connection c = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
c.setAutoCommit(false);
String sql = "begin ? := update_tab(); end;";
System.out.println("sql = "+sql);
CallableStatement stmt = c.prepareCall(sql);
/* Register the out parameter. */
System.out.println("register out param");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// get the result set
stmt.execute();
results = (ResultSet) stmt.getObject(1);
while (results.next()){
System.out.println("intA: "+results.getString(1)+", intB: "+results.getString(2)+", stringC: "+results.getString(3));
}
c.rollback();
c.close();
}
}
With my test data, I got the following results:
通过我的测试数据，我得到了以下结果：
intA: 3, intB: 4, stringC: a more stuff.
intA: 6, intB: 10, stringC: C more stuff.
intA: 3, intB: 4, stringC: a more stuff.
回答by Luke Woodward
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.
我相信你可以实现你想要的，但你需要处理 PL/SQL 数组而不是游标或结果集。下面是一个演示。
I have a table, called TEST, with the following structure:
我有一个名为 的表，TEST其结构如下：
SQL> desc test; Name Null? Type ----------------------------------------- -------- ----------------- A NUMBER(38) B NUMBER(38) C NUMBER(38)
and containing the following data:
并包含以下数据：
SQL> select * from test;
A B C
---------- ---------- ----------
1 2 3
4 5 6
7 8 9
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 VARCHAR2columns as well, you'll need to create a type for those too.
我需要为使用的每种类型的列创建一个数组类型。在这里，我只有NUMBERs，但如果您也有一个或多个VARCHAR2列，则还需要为这些列创建一个类型。
SQL> create type t_integer_array as table of integer; 2 / Type created.
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:
表和任何必要的类型都是我们需要在数据库中设置的。完成后，我们可以编写一个简短的 Java 类来执行UPDATE ... RETURNING ...，向 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]
The outputs displayed are the values returned from the columns A, Band Crespectively. There are only two values for each column since we filtered out the row with Bequal to 5.
显示的输出是从柱返回的值A，B并C分别。每列只有两个值，因为我们过滤掉了B等于 5的行。
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.
您可能希望值按行分组而不是按列分组。换句话说，您可能希望输出包含[11, 2, 3]和[17, 8, 9]。如果这就是你想要的，恐怕你需要自己做那部分。
