how to get multiple values from different tables plsql

Question

I usually do this query every day:

select * from table1
where name = 'PAUL';

select * from table2
where id_user = 012345;

select * from table25
where name = 'PAUL';

select * from table99
where name = 'PAUL';

select * from table28
where id_user = 012345;
.
.
.

I do this query every day. Today is 15 tables, tomorrow maybe 20, the only change is the name or id of the user to search. I have to go placing the username or id in each of the queries, so I need a query where there is a variable and assign the name and id. . . There is a way to simplify this query and make it better? Such as:

DECLARE
l_name  table1.name %type;
l_id_user table28.id_user %type;
BEGIN
l_id_user := 012345;
l_name := 'PAUL';

select * from table1
where name in ('l_name');

select * from table2
where id_user in (l_id_user);
.
.
.

END;

I tried that way but fails. I need this query because in most cases need to see up to 20 tables or more.

Your original SQL script looks simple and good. Why do you think it's not ? Do you need to process the data somehow or is this just a visual check ? Based on provided information I'd say adding PL/SQL makes it just more complex without any other benefits. It's hard to say anything else as you don't describe what you're trying to do. — user272735
– user272735, Commented Mar 13, 2015 at 4:21
Hello @user272735 please see the post again, I explained the situation better. — spikeTJ
– spikeTJ, Commented Mar 13, 2015 at 19:18

Jens Krogsboell · Accepted Answer · 2015-03-19 10:32:04Z

1

If what you want is an easy way to repeatedly run a sequence of select statements in SQL*Plus - but with varying criteria you can do it like this:

ACCEPT l_name PROMPT "Input user name: "
ACCEPT l_id_user PROMPT "Input user id: "

select * from table1
where name = '&l_name';

select * from table2
where id_user = &l_id_user;

select * from table25
where name = '&l_name';

select * from table99
where name = '&l_name';

select * from table28
where id_user = &l_id_user;

In a pl/sql block declare ... begin .. end; select statements behave differently from when you run them stand alone in SQL*Plus:

They won't automatically print output.
You must use e.g. a FOR LOOP or a variation of the SELECT INTO syntax to capture the result for subsequent processing. You should seek some documentation on pl/sql programming.

You should understand that pl/sql is definitely not designed for screen output.

It is possible to print out string values using dbms_output.put_line, but you are completely on your own with regards to formatting a row of values into a suitable string.

If your result contains more than a few columns formatting for screen output becomes very cumbersome as you wont have any formatting utilities like the java String.format() to assist you.

And if your result contains more than one row you have to provide some sort of loop construct allowing you to print out the individual rows.

Conlusion:

These are the drawbacks of pl/sql - I really don't see any benefits - unless your aim is to apply some logic to the data being read rather than just printing it to the screen or a file.

edited Mar 19, 2015 at 10:32

answered Mar 12, 2015 at 21:40

Jens Krogsboell

1,12311 silver badges18 bronze badges

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

5 Comments

spikeTJ Over a year ago

Do you know any way to do with PL/SQL? @Jens-Krogsboell

spikeTJ Over a year ago

I didnt find the way to make it better @JensKrogsboell

Jens Krogsboell Over a year ago

What do you expect to benefit from doing it in pl/sql as opposed to the sql*plus suggestion I already gave in my answer?

Jens Krogsboell Over a year ago

I have added to my answer some thoughts on the use of pl/sql for this task.

spikeTJ Over a year ago

Hello, it works in plsql: select * from table1 where name = (upper('&var1')); --varchar select * from table28 where id_user = &var2; --number . . . When I run this code I just have to put the value of the variables in the prompt. This way I can use the same code to find multiple users in different tables without placing data in each SELECT statement. @JensKrogsboell

Collectives™ on Stack Overflow

how to get multiple values from different tables plsql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related