在Oracle数据库中声明临时变量 [英] Declaring a Temporary Variable in Oracle Database

问题描述

我以前使用过SQL Server，并且能够学习如何创建临时变量并在SQL中使用它们

i have worked previously with SQL Server and was able to learn how to create temporary variables and use them in 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

然后

select @student, @teacher, other columns from sometable where some condition

我想在ORACLE数据库中做同样的事情.

i want to do the same thing in ORACLE Database.

请帮助！

推荐答案

如果要在SQL * Plus中执行此操作而不使用PL/SQL，则可以使用

If you want to do this in SQL*Plus without using PL/SQL, you can use substitution variables:

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;

其中的 new_value子句 column指令会自动将以下所有选择中的值分配给本地变量，而我已经在本地变量前面加上了l_，但是您可以调用任何东西.然后，您可以在以后的查询中使用&变量替换语法引用该局部变量.

The new_value clause in the column directive 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.

您几乎可以在通常有价值的任何地方使用它们.例如在where子句中.注意，文本值必须用引号引起来，因此'&l_studentname';没有引号的 value 在这种情况下将被解释为列名.

You can use them pretty much anywhere you'd normally have a value. e.g. in the where clause. Note that text values have to be enclosed in quotes, hence '&l_studentname'; without the quotes the value would be interpreted as a column name in this case, which wouldn't work.

这篇关于在Oracle数据库中声明临时变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！