在带有 Java/JDBC 的 Oracle PL/SQL 中使用游标并获取结果

Question

我有一个这样构造的 PL/SQL 查询：

 DECLARE
a NUMBER;
B NUMBER;
CURSOR cursor
IS
 ( SOME SELECT QUERY);
BEGIN
  OPEN cursor;
    LOOP
    SOME STUFF;
    END LOOP;
  CLOSE cursor;
END

如何使用 jdbc 从 java 代码运行此查询并获取结果集？我尝试在不使用游标的情况下运行查询，并且它运行正常。我想不出在java代码中做到这一点的方法。如果我直接在 oracle 客户端上运行查询，它可以正常工作。所以查询没有问题。

PS 由于某些限制，我不想将代码存储为存储过程并调用它。

原文由 Rajat Garg 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这是不可能的。您不能从匿名 PL/SQL 块返回结果集（因此无法从 JDBC 获取它）。

您将需要直接从 JDBC 运行选择。

唯一的，真正丑陋的解决方法是使用 dbms_output.put_line() 然后阅读。但这是一个非常丑陋的 hack，直接在 JDBC 中处理 SELECT 查询的结果要好得多。

---

编辑 1

下面是一个使用 dbms_output 的小例子：

 Connection con = ....;

// turn on support for dbms_output
CallableStatement cstmt = con.prepareCall("{call dbms_output.enable(32000) }");
cstmt.execute();

// run your PL/SQL block
Statement stmt = con.createStatement();
String sql =
    "declare  \n" +
    " a number;  \n" +
    " cursor c1 is select id from foo;  \n" +
    "begin  \n" +
    "  open c1; \n" +
    "  loop \n" +
    "    fetch c1 into a;  \n" +
    "    exit when c1%notfound;  \n" +
    "    dbms_output.put_line('ID: '||to_char(a)); \n" +
    "  end loop; \n" +
    "end;";
stmt.execute(sql);

// retrieve the messages written with dbms_output
cstmt = con.prepareCall("{call dbms_output.get_line(?,?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.registerOutParameter(2,java.sql.Types.NUMERIC);

int status = 0;
while (status == 0)
{
    cstmt.execute();
    String line = cstmt.getString(1);
    status = cstmt.getInt(2);
    if (line != null && status == 0)
    {
        System.out.println(line);
    }
}

---

编辑 2（这对于评论来说太长了）

嵌套循环来检索数据几乎总是一个坏主意。如果你发现自己在做这样的事情：

 begin
  for data_1 in (select id from foo_1) loop
    dbms_output.put_line(to_char(data_1.id));

    for data_2 in (select f2.col1, f2.col2 from foo_2 f2 where f2.id = data_1.id) loop
        ... do something else
    end loop;

  end loop;
end;
/

这样做会更有效率：

 begin
  for data_1 in (select f2.col1, f2.col2 from foo_2 f2
                 where f2.id in (select f1.id from foo_1 f1)) loop

     ... do something

  end loop;
end;
/

这可以在 JDBC 中使用这样的东西在没有过多内存的情况下进行处理：

 String sql = "select f2.col1, f2.col2 from foo_2 f2 where f2.id in (select f1.id from foo_1 f1)";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
   String col1_value = rs.getString(1);
   int    col2_value = rs.getInt(2);
   ... do something
}

即使您处理数十亿行，上面的代码也只会在内存中保存一行。准确地说：JDBC 驱动程序实际上会预取多行。默认值为 10，可以更改。但即便如此，您也没有过多的内存使用。

原文由 a_horse_with_no_name 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

这里的其他答案似乎超级复杂。

使用 SYS_REFCURSOR

因为永远，你可以很容易地从 JDBC 检索 SYS_REFCURSOR 类型：

 DECLARE
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR SELECT ...;
  ? := cur;
END;

现在像这样从Java运行上面的代码：

 try (CallableStatement c = con.prepareCall(sql)) {
    c.registerOutParameter(1, OracleTypes.CURSOR); // -10
    c.execute();

    try (ResultSet rs = (ResultSet) c.getObject(1)) {
        ...
    }
}

当然，您也可以按照 pmr’s answer 的 建议在包中声明自己的游标，但是如果您从 JDBC 运行匿名块，为什么要这样做呢？

使用 Oracle 12c 隐式结果集

Oracle 12c 为这些情况添加了一个方便的新特性，类似于 SQL Server/Sybase 和 MySQL 考虑返回结果的过程/批处理的方式。您现在可以在任何游标上使用 DBMS_SQL.RETURN_RESULT 过程，它“通过魔法”返回它：

 DECLARE
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR SELECT ...;
  DBMS_SQL.RETURN_RESULT(cur);
END;

由于 Oracle JDBC 驱动程序中的一个错误（或“功能”）， 从 JDBC 正确获取该游标有点棘手， 但它当然可以按照我在本文中所展示的方式完成。这就是您可以从任何匿名 PL/SQL 块和/或过程、触发器等中发现任意数量的隐式游标的方法…：

 try (PreparedStatement s = cn.prepareStatement(sql)) {
    // Use good old three-valued boolean logic
    Boolean result = s.execute();

    fetchLoop:
    for (int i = 0;; i++) {

        // Check for more results if not already done in this iteration
        if (i > 0 && result == null)
            result = s.getMoreResults();
        System.out.println(result);

        if (result) {
            result = null;

            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
            }
            catch (SQLException e) {
                // Ignore ORA-17283: No resultset available
                if (e.getErrorCode() == 17283)
                    continue fetchLoop;
                else
                    throw e;
            }
        }
        else if (s.getUpdateCount() == -1)
            // Ignore -1 value if there is one more result!
            if (result = s.getMoreResults())
                continue fetchLoop;
            else
                break fetchLoop;
    }
}

使用 jOOQ

如果您已经在应用程序中使用 jOOQ 来调用存储过程，您可以避免上述 JDBC 循环并使用 DSLContext.fetchMany() 以便轻松地将所有游标提取到一个数据结构中：

 for (Result<?> result : ctx.fetchMany(sql))
  for (Record record : result)
    System.out.println(record);

或者， 如果这是来自过程而不是匿名块，则使用代码生成。然后，您的程序将使用 procedure.getResults() 访问这些结果

原文由 Lukas Eder 发布，翻译遵循 CC BY-SA 4.0 许可协议