sql - CallableStatement + registerOutParameter + 多行结果

Question

我有一个如下形式的 SQL 语句:

BEGIN\n 
UPDATE tab 
SET stuff
WHERE stuff
RETURNING intA, intB, stringC
INTO ?,?,?

我已经注册了适当的 Out 参数。

这里是我有一些问题的地方:我调用 stmt.executeQuery() 还是 stmt.execute()？此外，我知道通过普通的 SELECT 查询，我可以遍历 resultSet 并填充我的对象——多行 Out 参数的等效项是什么？

编辑:或许我可以注册一个 CURSOR 类型的输出参数并循环遍历这个结果。

编辑2:我可能有多个需要循环的结果集吗？谢谢!

Answer 1

我相信您可以实现您的目标，但您需要处理 PL/SQL 数组而不是游标或结果集。下面是一个演示。

我有一个名为 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 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.

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();
    }
}

当我运行它时，我得到以下输出:

C:\Users\Luke\stuff>java UpdateWithBulkReturning[11, 17][2, 8][3, 9]

显示的输出分别是从列A、B 和C 返回的值。由于我们过滤掉了 B 等于 5 的行，所以每列只有两个值。

您可能希望值​​按行分组而不是按列分组。换句话说，您可能希望输出包含 [11, 2, 3] 和 [17, 8, 9]。如果那是您想要的，恐怕您需要自己完成那部分。