oracle - 动态 SQL 执行期间的 PL/SQL 错误

Question

我有一个 PL/SQL 过程，可以打印出正确的 sql，但是一旦我调用 EXECUTE，我就会收到一条错误，表明 sql 未正确结束。

这是我的程序

procedure my_proc(p_first VARCHAR2, p_second NUMBER, p_third NUMBER DEFAULT NULL, p_fouth NUMBER DEFAULT NULL)
IS
str varchar2(3200);
v1 VARCHAR2(20);
v2 VARCHAR2(20);
v3 VARCHAR2(20);
str2 varchar2(1000);

begin

str2:='SELECT t2.c1,t2.c2,t2.c3 FROM ';

if p_third=3 then
        str:= 't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=p_first ;' ;
elsif p_third=2 then
        str:=' t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=p_second ;' ;
else
        str:='t2 where t2.c1=p_second ;';
end if;

str2:=str2 || str;

dbms_output.put_line(str2);
EXECUTE IMMEDIATE  str2 into v1,v2,v3;

--dbms_output.put_line(v1||','||v2||','||v3);

end;

当我注释掉 EXECUTE 语句时，我得到

SELECT t2.c1,t2.c2,t2.c3 FROM t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=p_second;

看起来不错，所以我不确定为什么执行会给出“SQL 命令未正确结束”。

我尝试删除 sql 末尾的分号，但后来我得到了 ORA-00904: "p_second": 无效标识符
.

我做错了什么？

Answer 1

您收到的错误是因为动态语句末尾不应有分号。这是一个客户端语句分隔符，不是语句本身的一部分，并且立即执行仅接受单个语句。 (您是否收到“ORA-00933:SQL命令未正确结束”或“ORA-00911:无效字符”取决于您的Oracle版本)。

所以第一个修复是更改代码以删除这些:

if p_third=3 then
        str:= 't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=p_first' ;
elsif p_third=2 then
        str:=' t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=p_second' ;
else
        str:='t2 where t2.c1=p_second';
end if;

但是，当执行这些语句时，它们位于单独的 SQL 上下文中，该上下文不可见也不了解 PL/SQL 变量和过程参数。它会尝试将 p_first 或 p_second 解释为列名称，但会失败，并且它们不太可能存在(即使它们存在，也不会执行您期望的操作) )。

您可以按照@建议直接连接参数(但仍然没有分号):

        str:= 't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=''' || p_first || '''';
        str:=' t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=' || p_second;
        str:='t2 where t2.c1=' || p_second;

等等，对于其他数据类型来说，这会变得稍微复杂一些，因为您需要正确处理日期等等。

但最好使用绑定(bind)变量；这意味着有单独的立即执行调用，以便您可以为每个生成的查询提供相关的绑定(bind)值。像这样的东西:

begin

  str := 'SELECT t2.c1,t2.c2,t2.c3 FROM ';

  if p_third=3 then
    str := str || 't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=:p_first';
    dbms_output.put_line(str);
    execute immediate str into v1, v2, v3 using p_first;
  elsif p_third=2 then
    str := str || ' t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=:p_second';
    dbms_output.put_line(str);
    execute immediate str into v1, v2, v3 using p_second;
  else
    str := str || 't2 where t2.c1=:p_second';
    dbms_output.put_line(str);
    execute immediate str into v1, v2, v3 using p_second;
  end if;

  --dbms_output.put_line(v1||','||v2||','||v3);

end;