代码之家 › 专栏 › 技术社区 › Coding Duchess

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

toad plsql oracle

2

Coding Duchess · 技术社区 · 7 年前

我有一个PL/SQL过程,它输出正确的SQL,但是一旦调用 EXECUTE

这是我的程序

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;

执行 陈述,我明白

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结尾的分号,但是我得到了 ORA-00904: "p_second": invalid identifier .

3 回复 | 直到 7 年前

1

2

are 7 年前

不能这样传递参数

't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=p_first ;'

't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1='''||p_first ||''';'

2

3

Alex Poole 7 年前

您得到的错误是因为动态语句的结尾不应该有分号。这是一个客户端语句分隔符,不是语句本身的一部分,而execute immediate只接受一个语句。(是否得到“ORA-00933:SQL命令未正确结束”或“ORA-00911:invalid character”取决于您的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变量;这意味着 execute immediate 调用,以便为每个生成的查询提供相关的绑定值。类似于:

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;

3

2

Wernfried Domscheit 7 年前

begin

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

  if p_third=3 then
    str := str || 't1 JOIN t2 ON t1.c1=t2.c1 JOIN t3 ON t2.c1=t3.c1 WHERE 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 JOIN t2 ON t1.c1=t2.c1 JOIN t4 ON t2.c1=t4.c1 where 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;