java - JDBC 在一个请求中选择和更新

Question

这是oracle db中的一个表:

ID varchar2() primary key, 
STATUS varchar2(),
....

我需要选择状态为“DELAYED”的行，同时设置状态为“PROGRESS”。
有什么办法可以使用JDBC来做到这一点？

Answer 1

如果可以创建辅助包
您可以使用 PL/SQL UPDATE .. RETURNING句法。我认为BULK COLLECT INTO语义仍然不能直接与 jdbc 一起使用，因此您必须使用匿名 PL/SQL 块和一些技巧，而不是:

-- An auxiliary package is needed to declare the type.
create or replace package p as 
  type t is table of my_table%rowtype;
end p;
/

declare
  r p.t;
  c sys_refcursor;
begin
  update my_table
  set status = 'PROGRESS'
  where status = 'DELAYED'
  returning id, status, ...
  bulk collect into r;

  -- The package is needed for this syntax, which cannot yet work on types
  -- declared in anonymous blocks:
  open c for select * from table(r);
  dbms_sql.return_result(c);
end;
/

然后获取结果。不幸的是，这有点痛苦，从这篇博文中可以看出:
https://blog.jooq.org/2017/07/13/how-i-incorrectly-fetched-jdbc-resultsets-again/
(特别是根据 ojdbc 版本，获取 DBMS_SQL.RETURN_RESULT 结果时会出现错误)

// Alternatively, use a prepared statement if needed
try (Statement s = con.createStatement()) {

    fetchLoop:
    for (int i = 0, updateCount = 0; i < 256; i++) {
        boolean result = (i == 0)
            ? s.execute(
                """
                declare
                  r p.t;
                  c sys_refcursor;
                begin
                  ...
                end;
                """)
            : s.getMoreResults();

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                // Consume result set
            }
        else if ((updateCount = s.getUpdateCount()) != -1)
            System.out.println("Update Count: " + updateCount);
        else
            break fetchLoop;
    }
}

您还可以获取 SYS_REFCURSOR值作为 OUT CallableStatement 的参数
如果您无法创建包
如果您无法创建包，那么您可以使用 DBMS_SQL.NUMBER_TABLE 序列化各个列。等等，但这更涉及。它大致如下所示:

declare
  o0 dbms_sql.number_table;
  o1 dbms_sql.varchar2_table;
  -- repeat for all columns
  c0 sys_refcursor;
  c1 sys_refcursor;
  -- repeat for all columns
begin
  update my_table
  set status = 'PROGRESS'
  where status = 'DELAYED'
  returning id, status, ...
  bulk collect into o0, o1;
  -- Optional rowcount if you need that, as an OUT parameter
  ? := sql%rowcount;
  open c0 for select * from table(o0);
  open c1 for select * from table(o1);
  -- These can also be fetched as OUT parameters if you want
  ? := c0;
  ? := c1;
end;

这可能会导致更多的网络流量，并且需要将多个每列结果集重新组合成一个，因此如果可以，我建议使用打包方法。
这是什么 jOOQ当您运行 UPDATE .. RETURNING 时正在幕后进行操作陈述。