Comparing list of values against table

问题:

I tried to find solution for this problem for some time but without success so any help would be much appreciated. List of IDs needs to be compared against a table and find out which records exist (and one of their values) and which are non existent.
There is a list of IDs, in text format:

100,
200,
300 

a DB table:

ID(PK)   value01 value02 value03 .....
--------------------------------------
100       Ann
102       Bob
300       John
304       Marry
400       Jane

and output I need is:

100 Ann
200 missing or empty or whatever indication
300 John

Obvious solution is to create table and join but I have only read access (DB is closed vendor product, I'm just a user). Writing a PL/SQL function also seems complicated because table has 200+ columns and 100k+ records and I had no luck with creating dynamic array of records. Also, list of IDs to be checked contains hundreds of IDs and I need to do this periodically so any solution where each ID has to be changed in separate line of code wouldn't be very useful. Database is Oracle 10g.

回答1:

there are many built in public collection types. you can leverage one of them like this:

with ids as (select /*+ cardinality(a, 1) */ column_value id
               from table(UTL_NLA_ARRAY_INT(100, 200, 300)) a
            )
select ids.id, case when m.id is null then '**NO MATCH**' else m.value end value
  from ids
         left outer join my_table m
                     on m.id = ids.id;

to see a list of public types on your DB, run :

select owner, type_name, coll_type, elem_type_name, upper_bound, precision, scale from all_coll_types
 where elem_type_name in ('FLOAT', 'INTEGER', 'NUMBER', 'DOUBLE PRECISION')

the hint

/*+ cardinality(a, 1) */

is just used to tell oracle how many elements are in our array (if not specified, the default will be an assumption of 8k elements). just set to a reasonably accurate number.

回答2:

You can transform a variable into a query using CONNECT BY (tested on 11g, should work on 10g+):

SQL> WITH DATA AS (SELECT '100,200,300' txt FROM dual)
  2  SELECT regexp_substr(txt, '[^,]+', 1, LEVEL) item FROM DATA
  3  CONNECT BY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) + 1;

ITEM
--------------------------------------------
100
200
300

You can then join this result to the table as if it were a standard view:

SQL> WITH DATA AS (SELECT '100,200,300' txt FROM dual)
  2  SELECT v.id, dbt.value01
  3    FROM dbt
  4    RIGHT JOIN
  5      (SELECT to_number(regexp_substr(txt, '[^,]+', 1, LEVEL)) ID
  6         FROM DATA
  7       CONNECT BY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) + 1) v
  8       ON dbt.id = v.id;

        ID VALUE01
---------- ----------
       100 Ann
       300 John
       200 

回答3:

One way of tackling this is to dynamically create a common table expression that can then be included in the query. The final synatx you'd be aiming for is:

with list_of_values as (
  select 100 val from dual union all
  select 200 val from dual union all
  select 300 val from dual union all
  ...)
select
  lov.val,
  ...
from
  list_of_values lov left outer join 
  other_data     t   on (lov.val = t.val)

It's not very elegant, particularly for large sets of values, but compatibility with a database on which you might have few privileges is very good.