oracle 如何在sqlplus中声明一个日期变量作为绑定变量传递？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19534331/
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
How to declare a date variable in sqlplus to pass as bind variable?
提问by nayakam
I want to pass a date as bind variable in Sqlplus. I used the suggestion provided herebut it seems declared bind variable passed as varchar2?
我想在 Sqlplus 中传递一个日期作为绑定变量。我使用了这里提供的建议，但似乎声明的绑定变量作为 varchar2 传递？
variable my_date varchar2(30)
exec :my_date := '2013-10-01';
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,'yyyy-mm-dd');
select sql_text, v.sql_id, name, value_string, datatype_string
from v$sql_bind_capture vbc
join v$sql v
using (hash_value)
where v.sql_id in ('8c5xc95vxc7yr', '6vn08798ax8bw', '61g3km3x621wt');
SQL_TEXT SQL_ID NAME VALUE_STRING DATATYPE_STRING
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,:"SYS_B_0") 6vn08798ax8bw :MY_DATE VARCHAR2(32)
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,:"SYS_B_0") 6vn08798ax8bw :MY_DATE VARCHAR2(32)
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,:"SYS_B_0") 6vn08798ax8bw :SYS_B_0 yyyy-mm-dd VARCHAR2(32)
How to pass a date variable as bind parameter in Sqlplus?
如何在Sqlplus中将日期变量作为绑定参数传递？
updateI wrote pl/sql code block to pass date variable as bind variable. Does bind variable type affect execution plan?
更新我编写了 pl/sql 代码块以将日期变量作为绑定变量传递。绑定变量类型会影响执行计划吗？
回答by David Aldridge
As documented, you cannot define a date variable in SQL*Plus.
如文档所述，您不能在 SQL*Plus 中定义日期变量。
The bind variable is indeed passed as Varchar2 in your code, which exactly what AskTom says will happen. However the explicit date conversion method converts it to a date in the SQL engine.
绑定变量确实在您的代码中作为 Varchar2 传递，这正是 AskTom 所说的会发生的。但是，显式日期转换方法将其转换为 SQL 引擎中的日期。
