在 Oracle 数据库中声明临时变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10529539/
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 a Temporary Variable in Oracle Database
提问by Ghassan
i have worked previously with SQL Server and was able to learn how to create temporary variables and use them in SQL
我以前使用过 SQL Server，并且能够学习如何创建临时变量并在 SQL 中使用它们
i used to write something like this:
我曾经写过这样的东西：
declare @Student nvarchar(255)
select @Student = studentname from sometable where somecondition = 1
declare @teacher nvarchar(255)
select @teacher = teachername from sometable2 where somecondition >2
and then
进而
select @student, @teacher, other columns from sometable where some condition
i want to do the same thing in ORACLE Database.
我想在 ORACLE 数据库中做同样的事情。
Please Help!
请帮忙！
回答by Alex Poole
If you want to do this in SQL*Plus without using PL/SQL, you can use substitution variables:
如果您想在不使用 PL/SQL 的情况下在 SQL*Plus 中执行此操作，您可以使用替换变量：
column studentname new_value l_studentname
select studentname from sometable where somecondition = 1;
column teachername new_value l_teachername
select teachername from sometable2 where somecondition >2;
select '&l_studentname', '&l_teachername,' other columns
from sometable where somecondition;
The new_valueclausein the columndirective automatically assigns the value from any following select to a local variable, which I've prepended with l_but you can call anything. You then reference that local variable in future queries with the &variable substitution syntax.
该new_value条款的column指令自动分配从任何后续的价值选择一个局部变量，我已经与前置l_但你可以叫什么。然后在以后的查询中使用&变量替换语法引用该局部变量。
You can use them pretty much anywhere you'd normally have a value. e.g. in the whereclause. Note that text values have to be enclosed in quotes, hence '&l_studentname'; without the quotes the valuewould be interpreted as a column name in this case, which wouldn't work.
您几乎可以在通常具有值的任何地方使用它们。例如在where条款中。请注意，文本值必须用引号括起来，因此'&l_studentname'; 如果没有引号，在这种情况下，该值将被解释为列名，这是行不通的。
回答by Rohan
You can declare a variable say
你可以声明一个变量说
SOME_VAR VARCHAR2(255);
Then use it in your query directly
然后直接在您的查询中使用它
SELECT DISTINCT YT.TEACHER_NAME
INTO SOME_VAR
FROM YOUR_TABLE YT
WHERE YT.TEACHER_ID = 1;
Then you are free to use this variable, SOME_VAR, for further use
然后你可以自由使用这个变量, SOME_VAR, 以供进一步使用
Of course, this will not work in a simple SQL statement, but in case you use it in a programming block, like a procedure.
当然，这不适用于简单的 SQL 语句，但如果您在编程块（如过程）中使用它。
Hope it helps
希望能帮助到你
