1

I have the table my_table that may looks like:

+----+--------------------+
| id | some_other_columns |
+====+====================+
| 2  | data_2             |
+----+--------------------+
| 4  | data_4             |
+----+--------------------+
| 6  | data_6             |
+----+--------------------+

and have the array my_array that is defined as follows:

type t_numbers_type is table of number;
my_array t_numbers_type := t_numbers_type(2, 4, 6);

Q: I am looking for the fastest way to determine if list of all ids in my_table are identical with my_array?

i.e.: want to make sure that in the table my_table there are only 3 ids each one can be found in my_array

what I've tried so far the naïve straight-forward approach:

l_cnt        := 0;
l_missing_id := false;

for r_rec in (select id
                from my_table)
loop
  l_cnt := l_cnt +1;
  if r_rec.id not member of my_array then
    l_missing_id := true;
    exit;   
  end if;
end loop;

if l_cnt = my_array.count and not l_missing_id then
  dbms_output.putline('OK');
else
  dbms_output.putline('Not OK');
end if;

is there more direct way, something like: exists(select * from my_table where id in my_array)

or maybe: not exists(select * from my_table where id not in my_array)

Bauerhof
  • 155
  • 11
  • If you need only to check identifiers, then you may refer to [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/q/163887/2778710). If you want other columns along with identifiers, then you may `left join` in to the list turned into table with `table(my_array)` – astentx Mar 03 '23 at 08:04

1 Answers1

3

You can make use of TABLE() FUNCTION in Oracle PLSQL.

As you are asking:

Q: I am looking for the fastest way to determine if list of all ids in my_table are identical with my_array?

The more direct way to achieve would be :

select count(*) into l_cnt 
from my_table
where id in (select column_value from table(my_array));
Tushar
  • 3,527
  • 9
  • 27
  • 49