oracle pl/sql - to_date 不适用于执行立即参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6234276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pl/sql - to_date not working with execute immediate parameter
提问by poots
i wanna be able to execute my below proc like so:
我希望能够像这样执行我下面的过程：
exec procname('29-JAN-2011');
proc code is:
过程代码是：
PROCEDURE procname(pardate VARCHAR2) IS
vardate DATE := to_date(pardate, 'DD-MON-YYYY');
SQLS VARCHAR2(4000);
BEGIN
SQLS := 'SELECT cola, colb
FROM tablea
WHERE TRUNC(coldate) = TRUNC(TO_DATE('''||pardate||''',''DD/MON/YYYY''))';
EXECUTE IMMEDIATE SQLS;
END;
It keeps throwing error:
它不断抛出错误：
ORA-00904: "JAN": invalid identifier.
ORA-00904: "JAN": 无效标识符。
It compiles, but it throws the error when I run this command:
它可以编译，但是当我运行此命令时会引发错误：
EXEC procname('29-JAN-2011');
回答by APC
You declare a variable which casts the input parameter to a date: why not use it?
您声明了一个将输入参数转换为日期的变量：为什么不使用它？
Also, the TRUNC() applied to a date removes the time element. You don't need it here because the value you're passing has no time.
此外，应用于日期的 TRUNC() 会删除时间元素。您在这里不需要它，因为您传递的值没有时间。
So, your code should be:
所以，你的代码应该是：
PROCEDURE procname(pardate VARCHAR2) IS
vardate DATE := to_date(pardate, 'DD-MON-YYYY');
SQLS VARCHAR2(4000) := 'select cola, colb FROM tablea
WHERE TRUNC(coldate) = :1';
l_a tablea.cola%type;
l_b tablea.colb%type;
BEGIN
EXECUTE IMMEDIATE SQLS
into l_a, l_b
using vardate;
END;
Specifying the dynamic SQL statement with a bind variable and executing it with the USING syntax is a lot more efficient. Note that we still have to SELECT into some variables.
使用绑定变量指定动态 SQL 语句并使用 USING 语法执行它会更有效率。请注意，我们仍然需要 SELECT 到一些变量中。
回答by GolezTrol
You're using two different notations in the two calls to to_date. I think one of them (the second) is wrong.
您在对 的两次调用中使用了两种不同的符号to_date。我认为其中一个（第二个）是错误的。
