SQL 在 Select 语句中声明和设置变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3400058/
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
Declaring & Setting Variables in a Select Statement
提问by Matthew Hoenstine
I'm attempting to write a simple query where I declare some variables and then use them in a select statement in Oracle. I've been able to do this before in SQL Server with the following:
我正在尝试编写一个简单的查询，在其中声明一些变量，然后在 Oracle 的 select 语句中使用它们。我以前在 SQL Server 中使用以下方法可以做到这一点：
DECLARE @date1 DATETIME
SET @date1 = '03-AUG-2010'
SELECT U.VisualID
FROM Usage u WITH(NOLOCK)
WHERE U.UseTime > @Date1
From the searching I've done it appears you can not declare and set variables like this in Select statements. Is this right or am I mssing something?
从我所做的搜索来看，您似乎无法在 Select 语句中声明和设置这样的变量。这是正确的还是我在发什么？
采纳答案by Shannon Severance
From the searching I've done it appears you can not declare and set variables like this in Select statements. Is this right or am I missing something?
从我所做的搜索来看，您似乎无法在 Select 语句中声明和设置这样的变量。这是正确的还是我错过了什么？
Within Oracle PL/SQL and SQL are two separate languages with two separate engines. You can embed SQL DML within PL/SQL, and that will get you variables. Such as the following anonymous PL/SQL block. Note the /at the end is not part of PL/SQL, but tells SQL*Plus to send the preceding block.
Oracle PL/SQL 和 SQL 是两种独立的语言，具有两个独立的引擎。您可以在 PL/SQL 中嵌入 SQL DML，这将为您提供变量。比如下面的匿名PL/SQL块。注意/最后的不是 PL/SQL 的一部分，而是告诉 SQL*Plus 发送前面的块。
declare
v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
v_Count number;
begin
select count(*) into v_Count
from Usage
where UseTime > v_Date1;
dbms_output.put_line(v_Count);
end;
/
The problem is that a block that is equivalent to your T-SQL code will not work:
问题是与您的 T-SQL 代码等效的块将不起作用：
SQL> declare
2 v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
3 begin
4 select VisualId
5 from Usage
6 where UseTime > v_Date1;
7 end;
8 /
select VisualId
*
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
To pass the results of a query out of an PL/SQL, either an anonymous block, stored procedure or stored function, a cursor must be declared, opened and then returned to the calling program. (Beyond the scope of answering this question. EDIT:see Get resultset from oracle stored procedure)
要将查询结果从 PL/SQL（匿名块、存储过程或存储函数）传递出，必须声明、打开游标，然后将其返回给调用程序。（超出了回答这个问题的范围。编辑：请参阅从 oracle 存储过程获取结果集）
The client tool that connects to the database may have it's own bind variables. In SQL*Plus:
连接到数据库的客户端工具可能有它自己的绑定变量。在 SQL*Plus 中：
SQL> -- SQL*Plus does not all date type in this context
SQL> -- So using varchar2 to hold text
SQL> variable v_Date1 varchar2(20)
SQL>
SQL> -- use PL/SQL to set the value of the bind variable
SQL> exec :v_Date1 := '02-Aug-2010';
PL/SQL procedure successfully completed.
SQL> -- Converting to a date, since the variable is not yet a date.
SQL> -- Note the use of colon, this tells SQL*Plus that v_Date1
SQL> -- is a bind variable.
SQL> select VisualId
2 from Usage
3 where UseTime > to_char(:v_Date1, 'DD-Mon-YYYY');
no rows selected
Note the above is in SQLPlus, may not (probably won't) work in Toad PL/SQL developer, etc. The lines starting with variable and exec are SQLPlus commands. They are not SQL or PL/SQL commands. No rows selected because the table is empty.
注意以上是在 SQL Plus 中，可能（可能不会）在 Toad PL/SQL 开发人员等中工作。以变量和 exec 开头的行是 SQLPlus 命令。它们不是 SQL 或 PL/SQL 命令。由于表为空，因此未选择任何行。
回答by SVK
I have tried this and it worked:
我试过这个并且它有效：
define PROPp_START_DT = TO_DATE('01-SEP-1999')
select * from proposal where prop_start_dt = &PROPp_START_DT
回答by OMG Ponies
The SETcommand is TSQL specific - here's the PLSQL equivalent to what you posted:
该SET命令是特定于 TSQL 的 - 这是与您发布的内容等效的 PLSQL：
v_date1 DATE := TO_DATE('03-AUG-2010', 'DD-MON-YYYY');
SELECT u.visualid
FROM USAGE u
WHERE u.usetime > v_date1;
There's also no need for prefixing variables with "@"; I tend to prefix variables with "v_" to distinguish between variables & columns/etc.
也不需要用“@”前缀变量；我倾向于在变量前加上“v_”以区分变量和列/等。
