Oracle 在 Java 中的 RETURNING INTO 用法（JDBC、Prepared Statement）

Oracle's RETURNING INTO usage in Java (JDBC, Prepared Statement)

提问by Wanna Know All

I'm using JDBC to execute Oracle statement which looks like this:

我正在使用 JDBC 来执行如下所示的 Oracle 语句：

"INSERT INTO MYTABLE(MYDATA) VALUES(?) RETURNING MY_CALCULATED_DATA INTO ?"
// MYTABLE's def makes MY_CALCULATED_DATA be auto-generated by DB on insert

I found several ways to call the statement above in Java, mainly:

我在Java中找到了几种调用上面语句的方法，主要是：

• Using OraclePreparedStatement:

  ps = (OraclePreparedStatement)conn.prepareStatement(sql);
  ps.setString(1, "myvalue");
  ps.registerReturnParameter(2, Types.VARCHAR);
  ps.execute();
  rs = ps.getReturnResultSet();
  rs.next();
  System.out.print(rs.getString(1));
  

• Using CallableStatement:

  cs = conn.prepareCall(sql);
  cs.setString(1, "myvalue");
  cs.registerOutParameter(2, Types.VARCHAR);
  cs.execute();
  System.out.print(cs.getString(1));
  

• 使用 OraclePreparedStatement：

  ps = (OraclePreparedStatement)conn.prepareStatement(sql);
  ps.setString(1, "myvalue");
  ps.registerReturnParameter(2, Types.VARCHAR);
  ps.execute();
  rs = ps.getReturnResultSet();
  rs.next();
  System.out.print(rs.getString(1));
  

• 使用 CallableStatement：

  cs = conn.prepareCall(sql);
  cs.setString(1, "myvalue");
  cs.registerOutParameter(2, Types.VARCHAR);
  cs.execute();
  System.out.print(cs.getString(1));
  

Questions:

问题：

1. Method #2 throws "SQLException: Not all return parameters registered", BUT, if I wrap the SQL statement into "BEGIN..END;" - then method #2 works just fine.
   • Why method #1 works without "BEGIN..END" but method #2 requires "BEGIN..END" to work?
   • What kind of "magic" "BEGIN..END" does to the statement so that "not all parameters registered" problem suddenly solves itself?

2. Is there some third, better method of doing the above?

1. 方法#2 抛出“SQLException：并非所有返回参数都已注册”，但是，如果我将 SQL 语句包装到“ BEGIN..END;”中，那么方法 #2 就可以正常工作。
   • 为什么方法#1 在没有“ BEGIN..END”的情况下工作，但方法#2 需要“ BEGIN..END”才能工作？
   • 什么样的“魔法”“ BEGIN..END”对语句做了什么，以至于“不是所有参数都注册了”问题突然解决了？

2. 有没有第三种更好的方法来做上述事情？

Thank you, AG.

谢谢你，AG。

回答by mic.sca

Because parameters specified in returning clauses are handled in a different way compared to normal output parameters(getReturnResultSet vs getResultSet vs returning parameters in a callablestatement).
They need to be handled with OraclePreparedStatement. In the second case when you wrap the insert statement in begin..end the insert is handled by the database itself and al jdbc sees is an anonymous plsql block.
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#BABJJDDA

因为与正常输出参数相比，返回子句中指定的参数以不同的方式处理（getReturnResultSet 与 getResultSet 与可调用语句中的返回参数）。
它们需要用 OraclePreparedStatement 处理。在第二种情况下，当您将插入语句包装在 begin..end 中时，插入由数据库本身处理，而 jdbc 看到的是一个匿名 plsql 块。
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#BABJJDDA

回答by Arelli

To get auto generated key we have method getGeneratedKeys method in preparestatement which return resultset that contain key value all we need is pass key column name to preparestatement

为了获得自动生成的键，我们在 preparestatement 中有 getGeneratedKeys 方法，它返回包含键值的结果集，我们只需要将键列名传递给 preparestatement

pstm = con.prepareStatement("insert query",new String[]{primarykeycolumnname});
int i = pstm.executeUpdate();
if (i > 0) 
{
    ResultSet rs = pstm.getGeneratedKeys();
    while(rs.next())
    {
        System.out.println(rs.getString(1)); 
    }
}