CODEWITHSUNDEEP
databaseoracle-databaseplsql
Isaac Perez
Isaac Perez

Reputation: 13

It's possible to use a collection in WHERE clausule and condition IN?

It's possible to do something like the following?

create or replace TYPE "TypeKeys" IS TABLE OF INTEGER;
/
Declare
    Keys TypeKeys;
    personT PERSON%ROWTYPE;
BEGIN
    Keys := TypeKeys();
    Keys.EXTEND(2);
    Keys(1) := 14;
    Keys(2) := 21;

    SELECT * INTO personT FROM PERSON WHERE ID IN (Keys);
END;
/

I need to find rows by multiple id's

Upvotes: 1

Views: 60

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

You could use member of:

SELECT * INTO personT FROM PERSON WHERE ID MEMBER OF Keys;

or a table collection expression:

 SELECT * INTO personT FROM PERSON WHERE ID IN (SELECT * FROM TABLE(Keys));

db<>fiddle

Of course, as you're selecting into a single record you'll get an error if there isn't exactly one matching row, however many key values you include in the collection.

Upvotes: 3

Related Questions