PL/SQL: Selecting multiple variables in a single query

Question

In the initialization section of an oracle package I try to determine the current ID of certain objects with a given name. Reason: The package is used on multiple databases and the IDs of these objects my vary while their names are constant.

The code looks like this:

SELECT id INTO varCat FROM myTable WHERE Type = 'Object' AND Name = 'Cat';
SELECT id INTO varDog FROM myTable WHERE Type = 'Object' AND Name = 'Dog';
...
SELECT id INTO varMouse FROM myTable WHERE Type = 'Object' AND Name = 'Mouse';

Is there a way to optimize the queries and perhaps do it in a single query?

is the type and name unique? Or could there be more than one id for, say, Object and Cat? — Boneist
– Boneist, Commented Dec 21, 2017 at 10:20
The combination Type + Name is unique. But there can be multiple Cats with different types and obviously multiple Objects with different names. — Thanthla
– Thanthla, Commented Dec 21, 2017 at 10:25

Alex Poole · Accepted Answer · 2017-12-21 10:25:33Z

6

You can combine them with a manual pivot:

select max(case when name = 'Cat' then id end),
  max(case when name = 'Dog' then id end),
  max(case when name = 'Mouse' then id end)
into varCat, varDog, varMouse
from mytable
where type = 'Object'
and name in ('Cat', 'Dog', 'Mouse');

Quick demo:

create table mytable (id number, type varchar2(10), name varchar2(10));
insert into mytable (id, type, name) values (1, 'Object', 'Mouse');
insert into mytable (id, type, name) values (2, 'Object', 'Cat');
insert into mytable (id, type, name) values (3, 'Object', 'Dog');

set serveroutput on
declare
  varCat mytable.id%type;
  varDog mytable.id%type;
  varMouse mytable.id%type;
begin
  select max(case when name = 'Cat' then id end),
    max(case when name = 'Dog' then id end),
    max(case when name = 'Mouse' then id end)
  into varCat, varDog, varMouse
  from mytable
  where type = 'Object'
  and name in ('Cat', 'Dog', 'Mouse');

  dbms_output.put_line('varCat: ' || varCat);
  dbms_output.put_line('varDog: ' || varDog);
  dbms_output.put_line('varMouse: ' || varMouse);
end;
/

varCat: 2
varDog: 3
varMouse: 1

PL/SQL procedure successfully completed.

If the combination of type and name is not unique then your current code would error (too-many-rows); this would silent pick the highest ID.

If you're also getting IDs for other types where the name might be the same, you can include the type in the case expression too:

select max(case when type = 'Object' and name = 'Cat' then id end),
  max(case when type = 'Object' and name = 'Dog' then id end),
  max(case when type = 'Object' and name = 'Mouse' then id end)
  -- , ... other combinations you want to get
into varCat, varDog, varMouse --, ... other variables
from mytable
where (type = 'Object' and name in ('Cat', 'Dog', 'Mouse'))
or ... ;

answered Dec 21, 2017 at 10:25

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

2 Comments

Thanthla Over a year ago

Nice answer. Two questions: (a) Will the query (most likely) be faster than executing the previous multiple ones? (b) Why are you checking in the where-part for name in (x,y,z)?

Alex Poole Over a year ago

(a) almost certainly, because you're only hitting the table once; but it may depend a bit on the indexing and selectivity. And (b) so it doesn't consider doing a full table scan if you only want a few rows; without that it will evaluate all the data in the table - or at least all Objects - in the case expression, and all except those will be lost in the non-existent else condition. The optimiser might be smart enough to avoid that but I don't think so. If you are evaluating a large proportion of the rows anyway, not just a small subset, then you probably don't need that filter.

Dmitriy · Accepted Answer · 2017-12-21 10:26:32Z

2

Maybe with the pivot query:

select cat, dog, mouse
  into varCat, varDog, varMouse
  from (select * from mytable where Type = 'Object')
 pivot (max(id) for name in ('Cat' cat, 'Dog' dog, 'Mouse' mouse))

This query will return highest ID for each name column. If you have more names add them into the list in the last line of a query. Also, you can choose another aggregate function.

answered Dec 21, 2017 at 10:26

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

Comments

Kaushik Nayak · Accepted Answer · 2017-12-21 11:20:07Z

1

You could use a loop with the simple select query.

   FOR rec IN (SELECT ID, NAME
                 FROM myTable
                WHERE TYPE = 'Object' AND name in ('Cat', 'Dog', 'Mouse'))
   LOOP
      IF rec.NAME = 'Cat'
      THEN
         varCat := rec.ID;
      ELSIF rec.NAME = 'Dog'
      THEN
         varDog := rec.ID;
         ..
         ..
      END IF;
  END LOOP;

edited Dec 21, 2017 at 11:20

answered Dec 21, 2017 at 10:31

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

3 Comments

Alex Poole Over a year ago

Is this preferable to doing it as part of a single select ... into ... query? It's maybe clearer which name's ID goes into which variable; aside from that, is there a performance or some other advantage I'm missing? (And wouldn't a case statement be better than the multiple if/elsif branches?)

Kaushik Nayak Over a year ago

@AlexPoole : Probably it's a static data table with few standard entries used repeatedly for lookup. So, I think we could use this. If it isn't the case , then pure select with case is preferable.

Boneist Over a year ago

@KaushikNayak That cursor ought to be restricted to only the names expected, otherwise you could be pulling all the rows from the table only to throw most of them away.

ahmed sdiri · Accepted Answer · 2017-12-21 11:01:49Z

-2

INSERT INTO mytable (varCat, varDog, varMouse) VALUES( (SELECT id FROM Table1 where id), (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?), (SELECT recipient_id FROM recipient WHERE recipient_code = ?))

answered Dec 21, 2017 at 11:01

ahmed sdiri

15 bronze badges

1 Comment

Alex Poole Over a year ago

The question is about PL/SQL select ... into ..., not SQL insert into a table. The mytable already has the required data, this is about getting it from there into PL/SQL variables.

Collectives™ on Stack Overflow

PL/SQL: Selecting multiple variables in a single query

4 Answers 4

2 Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related