使用SQLPlus时如何设置Oracle绑定变量? [英] How to set Oracle bind variables when using SQLPlus?

问题描述

使用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 with colon (:).

绑定变量不能为"date"数据类型-它们是某种 字符值.

Bind variable can't be of data type "date" - they are some sort of character value.

因此，在您的代码中，必须将to_date()与 适当的格式模型或其他机制，将字符串转换为 日期.您的代码中当前缺少该代码.切勿将日期与 字符串！

For that reason, IN YOUR CODE you must use to_date() with the proper format model, or some other mechanism, to convert string to date. That is currently missing in your code. NEVER compare dates to strings!

下面的简短演示.

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绑定变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！