oracle 将值列表与表进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14872307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comparing list of values against table
提问by ffflyer
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:
我试图为这个问题找到一段时间的解决方案，但没有成功，所以任何帮助将不胜感激。需要将 ID 列表与表进行比较，找出哪些记录存在（及其值之一），哪些不存在。
有一个文本格式的 ID 列表：
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.
明显的解决方案是创建表并加入，但我只有读取访问权限（DB 是封闭的供应商产品，我只是一个用户）。编写 PL/SQL 函数似乎也很复杂，因为表有 200 多列和 10 万多条记录，而我在创建动态记录数组方面没有运气。此外，要检查的 ID 列表包含数百个 ID，我需要定期执行此操作，因此必须在单独的代码行中更改每个 ID 的任何解决方案都不会很有用。数据库是Oracle 10g。
采纳答案by DazzaL
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.
只是用来告诉oracle我们的数组中有多少个元素（如果没有指定，默认会假设有8k个元素）。只需设置一个合理准确的数字即可。
回答by Vincent Malgrat
You can transform a variable into a query using CONNECT BY(tested on 11g, should work on 10g+):
您可以使用CONNECT BY（在 11g 上测试，应该在 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
回答by David Aldridge
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.
它不是很优雅，特别是对于大的值集，但是与您可能几乎没有特权的数据库的兼容性非常好。
