问题描述
使用SQLPlus时如何设置Oracle绑定变量?
How do I set Oracle bind variables when using SQLPlus?
示例:
SELECT orders.order_no FROM orders WHERE orders.order_date BETWEEN :v1 AND :v2
如何设置:v1
和:v2
的日期?
推荐答案
注意以下几点:
-
VARIABLE是一个SQLPlus命令.您不要以分号(;)结尾.
VARIABLE is a SQLPlus command. You don't end it with a semicolon (;).
在VARIABLE命令中,不要在变量名之前添加冒号(:).
In the VARIABLE command, you do not precede the variable name withcolon (:).
绑定变量不能为"date"数据类型-它们是某种字符值.
Bind variable can't be of data type "date" - they are some sort ofcharacter value.
因此,在您的代码中,必须将to_date()
与适当的格式模型或其他机制,将字符串转换为日期.您的代码中当前缺少该代码.切勿将日期与字符串!
For that reason, IN YOUR CODE you must use to_date()
with theproper format model, or some other mechanism, to convert string todate. That is currently missing in your code. NEVER compare dates tostrings!
下面的简短演示.
SQL> variable v1 varchar2(20)
SQL> exec :v1 := '2015/12/22';
PL/SQL procedure successfully completed.
SQL> select 1 as result from dual where to_date(:v1, 'yyyy/mm/dd') < sysdate;
RESULT
----------
1
这篇关于使用SQLPlus时如何设置Oracle绑定变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!