oracle - 在 JDBC PL/SQL block 中多次使用命名参数时出错

Question

当使用命名参数调用 PL/SQL block 时出现错误，当所有命名参数仅使用一次时，我的代码工作正常，但是当我复制标有“//SQL”的 SQL 时。然后所有命名参数(以冒号开头，:q)都使用了两次，现在我得到一个 SQL 异常，它说:参数名称的数量与注册的参数数量不匹配。 似乎 JDBC 驱动程序或 DB 认为有 2 个参数，但只注册了 1 个参数？为什么我们不能多次使用命名参数？是否不需要 JDBC 驱动程序来支持这种情况？ 我如何获得替代方案(除了将 PL/SQL block 重写为存储过程)？

我的 Oracle JDBC 驱动程序是最新版本 11.2.0.3.0。

因为我的项目有很多 PL/SQL block ，所以我尽力避免将 SQL 重写为存储过程，最好使用命名参数运行原始 PL/SQL block (只需将其视为过程)。我测试过将PL/SQL block 转换为存储过程，然后只导出1个参数，但我不想重写所有PL/SQL block ，这需要更多努力。

感谢您的任何提示。

我的Java代码:

CallableStatement stmt = ...;
stmt.registerOutParameter("q", Types.VARCHAR);
stmt.execute();
String v1 = stmt.getString("q");

SQL如下:

BEGIN
    select DUMMY into :q from dual where dummy = 'X';
    select DUMMY into :q from dual where dummy = 'X';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;

我刚刚发现“立即执行”可以用于我的情况，当动态SQL是PL/SQL block (用begin和end引用)时，那么带有名称的命名参数可以被所有出现的引用引用。即下面附加的 SQL，在该 block 中，参数“q”仅使用一次， 但现在我还有另外两个问题， Q1: 我不知道参数'q'是I​​N、OUT还是同时是IN和OUT。如果我给出错误的 IN/OUT 模式，出现错误，我们如何测试参数是 IN/OUT 还是两者都有？我想扫描 SQL 中的 ':q :=' 和 'into :q'，这似乎不是一个好方法。 Q2:为什么当参数'q'分配为IN OUT模式时，我无法获取它的结果？只有当它是OUT的时候，我才能得到它的值。当它都是 IN OUT 时，我得到 NULL。

begin
  execute immediate 'begin select dummy into :q from dual where :q is not null; end;'
  using in out :q;
end;

哦，当参数为 IN OUT 模式时，我得到了 NULL 的解决方法，我只是认为这是 Oracle JDBC 驱动程序的一个错误，我将命名参数“q”的 IN/OUT 角色分为两部分，第一个是 IN ，第二个是OUT，使用一个变量来保存'using in out :q'子句返回的值，然后将变量分配给第二个角色，如下所附，在JDBC中我们将其视为IN OUT，仅使用精确IN，将 ' :q := ' 和 ' 扫描到 :q ' 后，使用 USING 子句中的 OUT 或 IN OUT。

declare 
    p varchar2(100);
    q varchar2(100);
begin
    p := ?;
    q := ?;
    execute immediate 'begin if :p is null then :p := ''X''; else :p := ''Y''; :q := ''Z''; end if; end;' using in out p, out q;
    ? := p;
    ? := q;
end;

Answer 1

不能在 SQL 语句中多次使用同一个绑定(bind)参数。您必须为每次出现的参数提供一个值。这是因为 Oracle 忽略绑定(bind)参数名称，只考虑冒号。
Oracle docs