PLSQL Variables

Question

I'm new to programming and I was wonder how I would declare a variable I can use through out my code.

What I want to achieve is :

Myvariable = (select Column from table1 where column =1234 group by column);
select column from table2 where column in (myvariable);
select column from table3 where column in (myvariable);

and etc

Thanks in advance :)

The tags `sql-server` and `plsql` do not mix. Are you using Microsoft SQL Server or Oracle? — D Stanley, Feb 16 '17 at 15:16
I removed the incompatible database tags. Please tag with the database you are really using. — Gordon Linoff, Feb 16 '17 at 15:16
In sql you can declare variable by `DECLARE @variableName DATATYPE`. And value from select query is assigned to the variable by `SELECT @variableName = column FROM table WHERE column=1234` — Chetan, Feb 16 '17 at 15:20

score 0 · Answer 1 · answered Feb 16 '17 at 15:57

0

this is not really a variable... you can write the sql like this

select column 
from table2 
where column in (select Column from table1 where column =1234 group by column);

answered Feb 16 '17 at 15:57

Randy

16,480
1
37
55

Hi thanks for the answer, I wanted to create a tool where I would only have to change that one number once, instead of going through all my code to change where all that number exists. – Oxy111 Feb 16 '17 at 16:00

score 0 · Accepted Answer · answered Feb 16 '17 at 16:10

0

If you're using PL/SQL Developer to access an Oracle database you can create a Test window (File - New - Test Window) with code similar to the following:

DECLARE
  myVariable  TABLE1.COLUMN%TYPE := 1234;
BEGIN
  FOR aRow2 IN (SELECT COLUMN
                  FROM TABLE2
                  WHERE COLUMN = myVariable)
  LOOP
    DBMS_OUPUT.PUT_LINE('Do something with ''aRow2''');
  END LOOP;

  COMMIT;

  FOR aRow3 IN (SELECT COLUMN
                  FROM TABLE3
                  WHERE COLUMN = myVariable)
  LOOP
    DBMS_OUPUT.PUT_LINE('Do something with ''aRow3''');
  END LOOP;

  COMMIT;
END;

You'll need to edit the above to do whatever you want with the rows from TABLE2 and TABLE3.

Best of luck.

answered Feb 16 '17 at 16:10

Bob Jarvis - Слава Україні

48,992
9
77
110

Hi thank you for the reply, I will take a look into this. also does this only work for test window? or can this work for SQL window as well. – Oxy111 Feb 16 '17 at 16:23
To run this from an SQL window you'd need to highlight all the text from `DECLARE` through `END;` using your mouse, then click the Execute button (or press the F8 key), so it's easier to run it from a Test window where all you have to do is click Execute (or press the F8 key). – Bob Jarvis - Слава Україні Feb 16 '17 at 17:13

score 0 · Answer 3 · answered Feb 17 '17 at 08:28

You can use a temporary TEMPORARY TABLE bound to the current session:

DECLARE GLOBAL TEMPORARY TABLE temp
{ 
    column INTEGER 
}
ON COMMIT DELETE ROWS  -- Specify PRESERVE ROWS if you want to keep them through commits
NOT LOGGED 
ON ROLLBACK DELETE ROWS  -- Remove this line if you want to keep rows when  you rollback a transaction

You load the table:

insert into temp1 (select Column from table1 where column =1234 group by column)

Then you can use the data using generic code:

select column from table2 where column in (select column from temp1);
select column from table3 where column in (select column from temp1);

PLSQL Variables

3 Answers3