Oracle查询无法编译 [英] Oracle query won't compile

问题描述

我有点困惑.谁能告诉我为什么它无法编译?

I'm a bit confused. Can anyone tell me why this won't compile?

declare
varSampleCode varchar2(80);
varResult integer;

varFilterString varchar2(80);

begin

varSampleCode := 'sam001';
varFilterString := 'somecolumn=2'; 

execute immediate  'select CASE WHEN EXISTS ( SELECT 1 from samples where samplecode = :varSamplecode  and auditflag = 0 and ' || varFilterString || ') then 1 else 0 end  into :varResult from DUAL'
using IN varSampleCode, OUT varResult;

end;

假设所有变量都已声明，并且查询本身有意义.我收到此错误报告: 错误报告: ORA-01006:绑定变量不存在 ORA-06512:在第12行 01006. 00000-绑定变量不存在"

Assume the variables are all declared and the query itself makes sense. I get this error report: Error report: ORA-01006: bind variable does not exist ORA-06512: at line 12 01006. 00000 - "bind variable does not exist"

推荐答案

我认为问题在于，您在EXECUTE IMMEDIATE中尝试的动态语句是SQL语句，而不是PL/SQL语句.而且INTO不是SQL，而是PL/SQL.

I think the problem is that the dynamic statement you are trying in your EXECUTE IMMEDIATE is a SQL statement, not a PL/SQL statement. And INTO is not SQL, it is PL/SQL.

您可以只使用一个绑定变量来执行动态 SQL 语句，然后将INTO置于动态语句之外.在执行动态 SQL 语句时，支持以下操作:

You can either do a dynamic SQL statement with just one bind variable and then place your INTO outside the dynamic statement. This is supported when doing a dynamic SQL statement:

execute immediate  'select CASE WHEN EXISTS ( SELECT 1 from samples where samplecode = :varSamplecode  and auditflag = 0 and ' || varFilterString || ') then 1 else 0 end from DUAL'
into varResult 
using IN varSampleCode;

或者您可以执行动态的 PL/SQL 匿名阻止:

Or you can do a dynamic PL/SQL anonymous block:

execute immediate  'begin select CASE WHEN EXISTS ( SELECT 1 from samples where samplecode = :varSamplecode  and auditflag = 0 and ' || varFilterString || ') then 1 else 0 end into :varresult from DUAL; end;'
using IN varSampleCode, OUT varResult;

这一次INTO必须在动态字符串中并使用绑定变量，因为使用BEGIN和END;您现在正在动态执行 PL/SQL .

This time the INTO must be inside the dynamic string and using a bind variable, because with the BEGIN and END; you are now doing dynamic PL/SQL.

对于您的用例，我建议使用第一种方法，即执行动态 SQL ，并将INTO 放在语句字符串之后.

For your use case I'd recommend the first option, doing dynamic SQL and placing the INTO after the statement string.

这篇关于Oracle查询无法编译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！