CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Yasin Okumuş
Yasin Okumuş

Reputation: 2388

Simple "SELECT" with variable but without "INTO"

I have some different SELECT queries with same values. I want to use something like DECLARE but when I write a simple DECLARE it says that "INTO" is expected.

If I want only a "SELECT", how can I use this form witout "INTO"?

Simply I have two (or more) selects:

SELECT * FROM my_table1 WHERE column1=5 and column2=6;

and 

SELECT * FROM my_table2 WHERE col1=5 and col2=6;

Now I want to declare a variable like var_col1 and var_col2 and use them in both select queries at the same time.

I thought this would work:

DECLARE
var_col1 number := 5;
var_vol2 number := 6;
BEGIN
SELECT * FROM my_table1 WHERE column1=var_col1 and column2=var_col2;
SELECT * FROM my_table2 WHERE col1=var_col1 and col2=var_col1;
/* and more SELECTs with var_col1 and var_col2 */
END;

But no chance... How is the way to do that without a procedure or function?

Upvotes: 12

Views: 21214

Answers (3)

APC
APC

Reputation: 146219

You're using PLSQL Developer, which has a choice of many different window types. If I recall correctly, the Command window is the one which is a SQL*Plus emulator. That being so this should work:

var n1 number 
var n2 number 

exec &&n1 := 5
exec &&n2 := 6

SELECT * FROM my_table1 WHERE column1=&&n1 and column2=&&n2 ; 
SELECT * FROM my_table2 WHERE col1&&n1 and col2=&&n2;

In other words, define two substitution variables, assign values to them and finally deploy them in all your queries.

Upvotes: 1

northpole
northpole

Reputation: 10346

You have to select into your declared variables if you want to do it that way or set the columns. For example:

DECLARE
var_col1 number := 5;
var_vol2 number := 6;
BEGIN
   SELECT my_table1.var_col into var_col1 
     FROM my_table1 
    WHERE column1=var_col1 
      AND column2=var_col2;
--etc.....
END;

OR

DECLARE
    var_col1 number := 5;
    var_vol2 number := 6;
    BEGIN
       SELECT 5 into var_col1 
         FROM my_table1 
        WHERE column1=var_col1 
          AND column2=var_col2;
    --etc.....
    END;

EDIT: I should also add that you can also declare output Vvariables that you can use in SQL to return output to help with debugging etc. So you can do something like:

DECLARE
 out  varchar2(200);

And in your BEGIN statement do something like:

 dbms_output.put_line(out);

Which outputs some potential useful info (depending what you set out to).

Upvotes: 1

Mark J. Bobak
Mark J. Bobak

Reputation: 14395

When you write select * from some_table; in SQL*Plus, SQL*Plus is acting as the client program, and does a lot of work for you, under the covers, in terms of the data being returned from the database, formatting it and displaying it.

As soon as you type DECLARE, you begin a PL/SQL block. Now, You're calling PL/SQL, and PL/SQL is calling SQL. As a result, you need to decide how to handle the data being returned from the SQL, in PL/SQL. The way to do that, is via an INTO clause and a variable to receive the output. Considering that, where would the output data from the SELECT go, if you don't provide an INTO clause? It has to go somewhere, right?

Hope that's clear.

Upvotes: 9

Related Questions