SQL Oracle:使用WHERE语句从SELECT保存变量 [英] SQL Oracle: Save variable from select with where statement
本文介绍了SQL Oracle:使用WHERE语句从SELECT保存变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用的是SQL Developer 19.1。我有一个包含多个subselect语句的大型脚本。它看起来像这样
def var1 = '1234'
def var2 = '5678'
select a.id
from tablea a
where something.id = (select other.id from .... where number1 = &var1 and number2 = &var2)
我希望使用另一个SELECT语句获得var1和var2。我该怎么做?我试过
declare
var1 number;
begin
select somenumber into var1 from ... where ...
end;
但我似乎不能在那里使用WHERE语句。我还尝试了BIND或@var1..
你们知道我下一步可以尝试什么吗?
我的目标类似于
var1 = (select somenumber from ... where ... )
推荐答案
如果您想继续使用替代变量,您可以使用the column ... new_value
functionality(本文档针对的是SQL*Plus,但主要适用于SQL developer;重点是将其用于报表标题,但它比建议的更有用):
-- define not needed now
--def var1 = '1234'
--def var2 = '5678'
column var1 new_value var1
select somenumber as var1 from ... where ...
column var2 new_value var2
select somenumber as var2 from ... where ...
select a.id
from tablea a
where something.id = (select other.id from .... where number1 = &var1 and number2 = &var2)
实际显示这一点的简单示例;如果工作表有:
column var1 new_value var1
select 1234 as var1 from dual;
column var2 new_value var2
select 5678 as var2 from dual;
select * from dual where 1234 = &var1 and 5678 = &var2;
然后脚本输出窗格显示:
VAR1
----------
1234
VAR2
----------
5678
old:select * from dual where 1234 = &var1 and 5678 = &var2
new:select * from dual where 1234 = 1234 and 5678 = 5678
D
-
X
或者您可以使用绑定变量:
var var1 number;
var var2 number;
begin
select somenumber into :var1 from ... where ...
select somenumber into :var2 from ... where ...
end;
/
select a.id
from tablea a
where something.id = (select other.id from .... where number1 = :var1 and number2 = :var2)
请注意,对var1
和var2
的引用现在前面带有冒号以指示绑定变量,而不是与号以指示替代变量;而且冒号也出现在into :var1
部分中-该部分仍引用绑定变量,而不是本地PL/SQL变量。
又是一个简单的示例;工作表脚本:
var var1 number;
var var2 number;
begin
select 1234 into :var1 from dual;
select 5678 into :var2 from dual;
end;
/
select * from dual where 1234 = :var1 and 5678 = :var2;
获取输出:
PL/SQL procedure successfully completed.
D
-
X
如果要查看匿名PL/sql挡路分配的值,可以使用print var1
。
这篇关于SQL Oracle:使用WHERE语句从SELECT保存变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文