Simple "SELECT" with variable but without "INTO"

Question

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?

Answer 1

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.

Answer 2

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