Oracle SQL:对日期使用绑定变量 [英] Oracle sql: using bind variable for dates

问题描述

这是一个没有绑定变量的简单工作查询:

Here is a simple working query without bind variables:

select * from table1 where time_stamp > sysdate - INTERVAL '1' day;

其中time_stamp是DATE类型.

我应该能够使用绑定变量在上述查询中输入任意天数.

I should be able to input any number of days in the above query using bind variable.

所以我尝试了以下操作，但似乎不起作用:

So I tried the following and does not seem to work:

select * from table1 where time_stamp > sysdate - INTERVAL :days day;

我尝试输入数字输入，例如10和'10'.您在10g上收到 ORA-00933 错误.

I tried entering the numeric input both as 10 and '10',for eg. You get ORA-00933 error on 10g.

推荐答案

原始查询中的字符串INTERVAL '1' day是间隔文字，即解析器将其评估为单个值.您不能用绑定变量替换它的一部分.

The string INTERVAL '1' day in your original query is an interval literal, i.e. it is evaluated by the parser to a single value. You can't replace part of it with a bind variable.

如果您改用NUMTODSINTERVAL( 1, 'DAY' )，则1是整数文字，您应该可以将其替换为绑定变量.

If you instead use NUMTODSINTERVAL( 1, 'DAY' ), then 1 is an integer literal which you should be able to replace with a bind variable.

这篇关于Oracle SQL:对日期使用绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！