CODEWITHSUNDEEP
oracle-databaseplsql
Thanthla
Thanthla

Reputation: 586

PL/SQL: Selecting multiple variables in a single query

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?

Upvotes: 0

Views: 12499

Answers (4)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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;

Upvotes: 1

ahmed sdiri
ahmed sdiri

Reputation: 1

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 = ?))

Upvotes: -2

Dmitriy
Dmitriy

Reputation: 5565

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.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191235

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

Upvotes: 6

Related Questions