对于表中不存在的IN列表中的元素甚至返回结果 [英] Returning result even for elements in IN list that don't exist in table
问题描述
我试图找到一种最简单的方法来返回结果集，该结果集指示表中是否存在某些值.考虑此表:
I am trying to find the easiest way to return a result set that indicates if some values are or are not present in a table. Consider this table:
id
------
1
2
3
7
23
我将收到一个ID列表，我需要用相同的列表进行响应，以指示表中存在哪些ID.如果我得到的列表如下所示:'1','2','3','4','8','23'，则需要生成一个如下所示的结果集:
I'm going to receive a list of IDs and I need to respond with the same list, indicating which are present in the table. If the list I get looks like this: '1','2','3','4','8','23', I need to produce a result set that looks like this:
id | status
-------------
1 | present
2 | present
3 | present
4 | missing
8 | missing
23 | present
到目前为止，我已经设法使用UNPIVOT提出了一些建议:
So far, I've managed to come up with something using UNPIVOT:
select id, 'present' as status
from my_table
where id in ('1','2','3')
union
select subq.v as id, 'missing' as status
from (
select v
from
(
(
select '1' v1, '2' v2, '3' v3 from dual
)
unpivot
(
v
for x in (v1,v2,v3)
)
)
) subq
where subq.v not in
(
select id
from my_table
where id in ('1','2','3')
);
看起来有点奇怪，但是确实有效.问题出在select '1' v1, '2' v2, '3' v3 from dual部分:我不知道如何用JDBC准备的语句填充它. ID列表不是固定的，因此使用此查询的函数的每次调用都可以传递不同的ID列表.
It looks a little weird, but it does work. The problem with this is the select '1' v1, '2' v2, '3' v3 from dual part: I have no idea how I can populate this with a JDBC prepared statement. The list of IDs is not fixed, so each call to the function that uses this query could pass a different list of IDs.
还有其他方法可以做到这一点吗?我想我缺少明显的东西，但是我不确定...
Are there any other ways to get this done? I think I'm missing something obvious, but I'm not sure...
(与Oracle 11配合使用)
(working with Oracle 11)
推荐答案
从SQL端，您可以定义一个表类型并将其用于连接到真实数据中，例如:
From the SQL side you could define a table type and use that to join to your real data, something like:
create type my_array_type as table of number
/
create or replace function f42 (in_array my_array_type)
return sys_refcursor as
rc sys_refcursor;
begin
open rc for
select a.column_value as id,
case when t.id is null then 'missing'
else 'present' end as status
from table(in_array) a
left join t42 t on t.id = a.column_value
order by id;
return rc;
end f42;
/
SQL Fiddle演示，带有包装函数，因此您可以直接对其进行查询， :
SQL Fiddle demo with a wrapper function so you can query it directly, which gives:
ID STATUS
---------- --------------------
1 present
2 present
3 present
4 missing
8 missing
23 present
在Java中，您可以基于表类型定义ARRAY，从Java数组填充，然后直接调用该函数.您的单个参数绑定变量是ARRAY，您将获得一个结果集，可以像往常一样进行迭代.
From Java you can define an ARRAY based on the table type, populate from a Java array, and call the function directly; your single parameter bind variable is the ARRAY, and you get back a result set you can iterate over as normal.
作为Java方面的概述:
As an outline of the Java side:
int[] ids = { 1, 2, 3, 4, 8, 23 };
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("MY_ARRAY_TYPE",
conn);
oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);
cStmt = (OracleCallableStatement) conn.prepareCall("{ call ? := f42(?) }");
cStmt.registerOutParameter(1, OracleTypes.CURSOR);
cStmt.setArray(2, ora_ids);
cStmt.execute();
rSet = (OracleResultSet) cStmt.getCursor(1);
while (rSet.next())
{
System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
}
哪个给:
id 1: present
id 2: present
id 3: present
id 4: missing
id 8: missing
id 23: present
正如Maheswaran Ravisankar所提到的，这允许传递任意数量的元素；您不需要知道在编译时有多少个元素(或处理理论上的最大值)，就不必受IN中允许的最大表达式数或单个定界字符串的长度的限制，而且您不必组成和分解字符串即可传递多个值.
As Maheswaran Ravisankar mentions, this allows any number of elements to be passed; you don't need to know how many elements there are at compile time (or deal with a theoretical maximum), you aren't limited by the maximum number of expressions allowed in an IN or by the length of a single delimited string, and you don't have to compose and decompose a string to pass multiple values.
如ThinkJet所指出的，如果您不想创建自己的表类型，则可以使用预定义的集合，
As ThinkJet pointed out, if you don't want to create your own table type you can use a predefined collection, demonstrated here; the main function is the same apart from the declaration of the parameter:
create or replace function f42 (in_array sys.odcinumberlist)
return sys_refcursor as
...
wrapper函数填充数组的方式略有不同，但是在Java方面，您只需要更改此行:
The wrapper function populates the array slightly differently, but on the Java side you only need to change this line:
ArrayDescriptor aDesc =
ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );
使用此方法还意味着(正如ThinkJet也指出的！)，您可以运行原始的独立查询而无需定义函数:
Using this also means (as ThinkJet also pointed out!) that you can run your original stand-alone query without defining a function:
select a.column_value as id,
case when t.id is null then 'missing'
else 'present' end as status
from table(sys.odcinumberlist(1, 2, 3, 4, 8, 23)) a
left join t42 t on t.id = a.column_value
order by id;
( SQL小提琴).
这意味着您可以直接从Java调用查询:
And that means you can call the query directly from Java:
int[] ids = { 1, 2, 3, 4, 8, 23 };
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );
oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);
sql = "select a.column_value as id, "
+ "case when t.id is null then 'missing' "
+ "else 'present' end as status "
+ "from table(?) a "
+ "left join t42 t on t.id = a.column_value "
+ "order by id";
pStmt = (OraclePreparedStatement) conn.prepareStatement(sql);
pStmt.setArray(1, ora_ids);
rSet = (OracleResultSet) pStmt.executeQuery();
while (rSet.next())
{
System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
}
...，您可能更喜欢.
... which you might prefer.
还有一种预定义的ODCIVARCHAR2LIST类型，如果您实际上正在传递字符串-即使字符串包含数字，您的原始代码似乎也可以使用字符串，因此不确定您真正需要的是什么.
There's a pre-defined ODCIVARCHAR2LIST type too, if you're actually passing strings - your original code seems to be working with strings even though they contain numbers, so not sure which you really need.
因为这些类型定义为VARRAY(32767) 您只能使用32k值，而定义自己的表可以消除该限制；但很明显，只有在传递大量值的情况下，这一点才重要.
Because these types are defined as VARRAY(32767) you are limited to 32k values, while defining your own table removes that restriction; but obviously that only matters if you're passing a lot of values.
这篇关于对于表中不存在的IN列表中的元素甚至返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
