在 SQL 查询 (Oracle DBMS) 中使用变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10409653/
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
Using a variable in an SQL query (Oracle DBMS)?
提问by tyrex
I am typing SQL queries to get some data from a software tool which is based on an Oracle database. I am using the typical SELECT-statement.
我正在键入 SQL 查询以从基于 Oracle 数据库的软件工具中获取一些数据。我正在使用典型的 -SELECT语句。
Now, in my SQL-query I am using at different places the date "02.05.2012". Is there a way to define a variable date_stringat the beginning and then use at all relevant places this variable?
现在，在我的 SQL 查询中，我在不同的地方使用日期“02.05.2012”。有没有办法date_string在开始时定义一个变量，然后在所有相关的地方使用这个变量？
This would simplify things a lot. Thanks for all hints and tips!
这将使事情简化很多。感谢所有提示和技巧！
回答by David Aldridge
You might try to rewrite your query to return the literal from an inline view ...
您可能会尝试重写查询以从内联视图返回文字...
select
my_date,
...
from(
select to_date('02.05.2012','DD.MM.YYYY') my_date from dual),
table2
where
some_column <= my_date
回答by winkbrace
What you look for is a bind variable.
您要查找的是绑定变量。
select to-date(:date, 'dd.mm.yyyy') date1
, to-date(:date, 'dd.mm.yyyy') + 1 date2
from dual
On runtime you need to pass the value to the bind variable. It all depends on your programming language how to bind the variable, but there is plenty documentation for that. DEFINE only works if you use sql*plus, and that's usually not the case inside a "software tool" :)
在运行时，您需要将值传递给绑定变量。这完全取决于您的编程语言如何绑定变量，但是有很多相关的文档。DEFINE 仅在您使用 sql*plus 时才有效，而在“软件工具”中通常不是这种情况:)
EDIT:
编辑：
I'm beginning to understand now. It's just a textarea where you can enter a query and it will execute it and return the result. In that case you either write some complicated pl/sql code, or enter all the dates manually, or use a cross join with a select from dual:
我现在开始明白了。它只是一个文本区域，您可以在其中输入查询，它将执行它并返回结果。在这种情况下，您要么编写一些复杂的 pl/sql 代码，要么手动输入所有日期，或者使用带有选择的交叉连接：
with (select to_date('02.05.2012', 'dd.mm.yyyy') my_date from dual) d
select *
from some_table t
cross join d -- no ON required
回答by Rene
If you want to select using the current date you can use sysdate.
如果要选择使用当前日期，可以使用 sysdate。
Using SQLPLUS you can define your own variables:
使用 SQLPLUS，您可以定义自己的变量：
SQL> define mydate ="01-05-2012"
SQL> select to_date('&mydate','DD-MM-YYYY') from dual;
01-MAY-12
回答by mcha
try the following :
尝试以下操作：
SELECT *
FROM table1
WHERE to_char(date1,'DD/MM/YYYY') = '&&date'
AND to_char(date2,'DD/MM/YYYY') = '&&date'
AND to_char(date3,'DD/MM/YYYY') = '&&date'
you will get a prompt to enter the value for the &&date , if you want to enter different values for each date, you should type &date instead of &&date
你会得到一个输入 &&date 值的提示，如果你想为每个日期输入不同的值，你应该输入 &date 而不是 &&date
