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:并非所有返回参数都已注册"，但是，如果我将 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.
推荐答案
因为在返回子句中指定的参数与正常输出参数相比以不同的方式处理(getReturnResultSet vs getResultSet vs 在 callablestatement 中返回参数).
它们需要用 OraclePreparedStatement 处理.在第二种情况下，当您将插入语句包装在 begin..end 中时，插入是由数据库本身处理的，而 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屋！
