使用JDBC使用绑定变量编写匿名PL/SQL块 [英] Writing an anonymous PL/SQL block with bind variables using JDBC

问题描述

我有一个匿名PL/SQL块，其中包含要通过JDBC运行的绑定变量.

I have an Anonymous PL/SQL Block with bind variables that I want to run via JDBC.

PL/SQL块示例:

variable v_value number
declare
v_return varchar2(30);
begin
:v_value:=300;
select ename into v_return from emp where empno=:v_value;
end;

相应的Java代码将使用带有?" 的转义语法来设置变量.因此，此块看起来像这样(如果我输入错了，请纠正我):

The corresponding Java code would make use of the escape syntax with "?" to set the variables. So this block would look like this(correct me if I'm wrong):

String block = "declare v_return varchar2(30);" +
               "begin" + 
               "? := 300;" +
               "select ename into v_return from emp where empno = ?;" +
               "end;"

现在，假设我的变量是一个INPUT参数，我将必须像这样设置参数:

Now, assuming that my variable is an INPUT parameter, I'll have to set the parameter like this:

// omitting the CallableStatement and conn declarations
cs = conn.prepareCall(block);

cs.setInt(parameterIndex, parameterValue);

问题是在我的代码块中，我有两个?" 用于替换绑定参数:v_value.这意味着，在使用转义语法时，只会设置第一个?" .第二个?" 将保持悬挂"状态.

The PROBLEM is that in my block I have two "?" used to replace the bound parameter :v_value. This means that when using the escape syntax only the 1'st "?" will be set. The 2'nd "?" will be left "hanging".

在这种情况下，当在PL/SQL块中多次使用相同的绑定变量时，应该如何用JDBC转义语法进行翻译?

我发现了这个

I found this question on SO that is related to my problem. What I understand from it is that I'll have to REWRITE all Anonymous PL/SQL Blocks that make use of multiple bind variable instances in the same block. Is there ANY workaround for this? Or this is it... game over... it's the way JDBC works and I'll have to make due.

期待得到答案...搜索了2个小时，没有结果.

Looking forward for an answer... searched for this for 2 hours with no results.

推荐答案

看看这个文档.

基本上，您可以像这样将与Input和Output绑定的变量绑定在一起:

Basicaly, you can bind the same variable as Input and Output like this:

CallableStatement call = conn.prepareCall(
    "{CALL doubleMyInt(?)}");
// for inout parameters, it is good practice to
// register the outparameter before setting the input value
call.registerOutParameter(1, Types.INTEGER);
call.setInt(1,10);

希望对您有帮助.

这篇关于使用JDBC使用绑定变量编写匿名PL/SQL块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！