Oracle在Java中的RETURNING INTO使用（JDBC，Prepared Statement） [英] Oracle's RETURNING INTO usage in Java (JDBC, Prepared Statement)
问题描述
我正在使用JDBC来执行如下所示的Oracle语句：
I'm using JDBC to execute Oracle statement which looks like this:
"INSERT INTO MYTABLE(MYDATA) VALUES(?) RETURNING MY_CALCULATED_DATA INTO ?"
// MYTABLE's def makes MY_CALCULATED_DATA be auto-generated by DB on insert
我发现了几个在Java中调用上述语句的方法主要是：
I found several ways to call the statement above in Java, mainly:
-
使用OraclePreparedStatement：
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));
使用CallableStatement：
Using CallableStatement:
cs = conn.prepareCall(sql);
cs.setString(1, "myvalue");
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
System.out.print(cs.getString(1));
- 方法＃2抛出SQLException：并非所有返回参数都已注册， BUT ，如果我包装SQL声明为 BEGIN..END; - 然后方法＃2工作正常。
- 为什么方法＃1在没有 BEGIN..END 的情况下工作，但方法＃2需要 BEGIN..END 工作？
- 什么样的魔术 BEGIN..END 对声明做了什么，以便并非所有参数注册问题突然解决了？
- 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?
谢谢你，
AG。
Thank you, AG.
推荐答案
因为与正常输出参数相比，返回子句中指定的参数以不同的方式处理（getReturnResultSet vs getResultSet vs callablestatement中的返回参数）。
它们需要使用OraclePreparedStatement处理。在第二种情况下，当您在begin..end中包装insert语句时，insert将由数据库本身处理，而al jdbc看到的是一个匿名的plsql块。
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm #BABJJDDA
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
这篇关于Oracle在Java中的RETURNING INTO使用（JDBC，Prepared Statement）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
