SQL Oracle: Save variable from select with where statement-CodePudding

i'm using SQL Developer 19.1. I have a huge script with multiple subselect statements. It looks like this

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)

i want to get var1 and var2 with a another select statements. How do i do that? i tried

declare
 var1 number;
begin
 select somenumber into var1 from ... where ... 
end;

but it seems like i cant use a where statement there. i also tried something with bind or @var1..

Do you guys have any idea what i could try next?

My goal is something like

var1 = (select somenumber from ... where ... )

CodePudding user response：

If you want to keep using substitution variables you could use the column ... new_value functionality (the documentation is for SQL*Plus but mostly applies to SQL Developer; and focuses on using this for report titles but it's more useful than that suggests):

-- 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)

Trivial example to show this in action; if the worksheet has:

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;

then the script output pane shows:

      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

Or you could use bind variables:

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)

Notice that the references to var1 and var2 are now preceded by a colon to indicate a bind variable, rather than an ampersand to indicate a substitution variable; and that colon appear in the into :var1 part as well - that is still referring to the bind variable, not to a local PL/SQL variable.

Trivial example again; worksheet script:

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;

gets output:

PL/SQL procedure successfully completed.


D
-
X

You can use print var1 if you want to see the value that was assigned by the anonymous PL/SQL block.