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?

Which SQL client are you using?

user330315
– user330315

2012-02-23 16:10:14 +00:00
Commented Feb 23, 2012 at 16:10 — user330315
– user330315, Commented Feb 23, 2012 at 16:10

Mark J. Bobak · Accepted Answer · 2012-02-23 16:15:36Z

9

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.

answered Feb 23, 2012 at 16:15

Mark J. Bobak

14.5k6 gold badges45 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sorpigal Over a year ago

What's wrong with the value going back to the client exactly as if you had done the select without a declare block?

Patrick Szalapski Over a year ago

Yes, we want to discard all the data without a dummy INTO to receive it so that we can cause the side-effects of running a SELECT query to happen, but not use the data at all. I'm sad that this doesn't answer the question.

Mark J. Bobak Over a year ago

Not sure what to tell you....sorry you're sad that Oracle doesn't actually work like you wish it did....If you re-read my reply, I think it's pretty clear why INTO is required when running a PL/SQL block.

northpole · Accepted Answer · 2012-02-23 16:24:37Z

1

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

edited Feb 23, 2012 at 16:24

answered Feb 23, 2012 at 16:12

northpole

10.3k7 gold badges38 silver badges60 bronze badges

5 Comments

Yasin Okumuş Over a year ago

But I want to select all columns?

Yasin Okumuş Over a year ago

And I don't get, what is the purpose of "into" really? What does that mean for a DECLARE?

northpole Over a year ago

Then the answer you want is @Edurado Ivanec's solution. Where you set the value in the BEGIN clause

northpole Over a year ago

To answer your question about the INTO, please read @Mark J. Bobak response.

MatBailie Over a year ago

@YasinOkumus - It depends on how you're using it. In the case above it's copying values from a table into a variable.

APC · Accepted Answer · 2012-02-23 17:49:58Z

1

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.

answered Feb 23, 2012 at 17:49

APC

147k20 gold badges180 silver badges289 bronze badges

4 Comments

Yasin Okumuş Over a year ago

Very interestingly "var" is a keyword. But Pl/Sql Developer doesn't accept this all statement. So this code doesn't work. By the way, I can't find proper usage of "var" for plsql...

user330315 Over a year ago

@YasinOkumus: var is not PL/SQL, it's a PL/SQL Developer feature. You need to look it up in the PL/SQL developer manual

Yasin Okumuş Over a year ago

Thank you, I meant PL/SQL Developer.

David Balažic Aug 26 at 9:25

You are mixing bind variables (the VAR command) and user variables (&&n1). See my answer.

Collectives™ on Stack Overflow

Simple "SELECT" with variable but without "INTO"

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related