即使对于表中不存在的 IN 列表中的元素也返回结果
我试图找到返回结果集的最简单方法，该结果集指示表中是否存在某些值.考虑这张表:
<前>ID------123723我将收到一个 ID 列表，我需要用相同的列表进行响应，指出表中存在哪些 ID.如果我得到的列表看起来像这样:'1','2','3','4','8','23'，我需要生成一个看起来像的结果集这个:
到目前为止，我已经设法使用 UNPIVOT 想出了一些东西:
select id, 'present' as status来自 my_tablewhere id in ('1','2','3')联盟选择 subq.v 作为 id，'missing' 作为状态从 (选择 v从((从双中选择1"v1、2"v2、3"v3)转轴(v对于 x in (v1,v2,v3)))) 子qsubq.v 不在的地方(选择身份证来自 my_tablewhere id in ('1','2','3'));看起来有点奇怪，但确实有效.问题在于 select '1' v1, '2' v2, '3' v3 from dual 部分:我不知道如何使用 JDBC 准备好的语句填充它.ID 列表不是固定的，因此每次调用使用此查询的函数都可能传递不同的 ID 列表.
还有其他方法可以完成此操作吗?我想我遗漏了一些明显的东西，但我不确定......
(使用 Oracle 11)
解决方案从 SQL 方面，您可以定义表类型并使用它来连接您的真实数据，例如:
创建类型 my_array_type 作为数字表/创建或替换函数 f42 (in_array my_array_type)返回 sys_refcursor 作为rc sys_refcursor;开始打开 rc选择 a.column_value 作为 id，t.id 为空然后缺失"的情况否则当前"以状态结束从表(in_array)一个在 t.id = a.column_value 上左连接 t42 t按 id 排序；返回 rc;结束 f42;/SQL Fiddle 演示 带有包装函数，因此您可以直接查询它，这使得:
ID 状态---------- --------------------1 份2 礼物3 礼物4 失踪8 失踪23 礼物在Java中，您可以根据表类型定义一个ARRAY，从Java数组中填充，并直接调用该函数；您的单参数绑定变量是 ARRAY，您会返回一个可以正常迭代的结果集.
作为Java端的概述:
int[] ids = { 1, 2, 3, 4, 8, 23 };ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("MY_ARRAY_TYPE",连接)；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);而 (rSet.next()){System.out.println("id" + rSet.getInt(1) + ":" + rSet.getString(2));}给出:
id 1:存在id 2:存在id 3:存在编号 4:缺失编号 8:缺失id 23:现在正如 Maheswaran Ravisankar 提到的，这允许传递任意数量的元素；您不需要知道在编译时有多少元素(或处理理论最大值)，您不受 IN 中允许的最大表达式数或长度的限制单个分隔字符串，您不必组合和分解字符串来传递多个值.
正如 ThinkJet 指出的，如果您不想创建自己的表类型，您可以使用预定义的集合，此处展示；除了参数的声明外，main函数是一样的:
创建或替换函数 f42 (in_array sys.odcinumberlist)返回 sys_refcursor 作为...包装函数填充数组的方式略有不同，但在 Java 端，您只需要更改这一行:
ArrayDescriptor aDesc =ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );使用这也意味着(正如 ThinkJet 也指出的！)您可以在不定义函数的情况下运行原始的独立查询:
选择a.column_value作为id，t.id 为空然后丢失"的情况否则当前"以状态结束来自表(sys.odcinumberlist(1, 2, 3, 4, 8, 23)) a在 t.id = a.column_value 上左连接 t42 t按 id 排序；(SQL 小提琴.
这意味着您可以直接从 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 = "选择a.column_value 作为id, "+ "t.id 为空然后 'missing' 的情况"+ "else 'present' 以状态结尾 "+ "来自表(?) a "+在 t.id = a.column_value 上左连接 t42 t"+按id排序"；pStmt = (OraclePreparedStatement) conn.prepareStatement(sql);pStmt.setArray(1, ora_ids);rSet = (OracleResultSet) pStmt.executeQuery();而 (rSet.next()){System.out.println("id" + rSet.getInt(1) + ":" + rSet.getString(2));}...你可能更喜欢.
还有一个预定义的 ODCIVARCHAR2LIST 类型，如果您实际上是在传递字符串 - 即使它们包含数字，您的原始代码似乎也在处理字符串，所以不确定您真正需要哪个.
因为这些 类型被定义为 VARRAY(32767) 您被限制为 32k 个值，而定义您自己的表则消除了该限制；但很明显，这只有在您传递大量值时才重要.
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
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
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')
);
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...
(working with Oracle 11)
解决方案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 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
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.
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));
}
Which gives:
id 1: present
id 2: present
id 3: present
id 4: missing
id 8: missing
id 23: present
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.
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
...
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 );
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 Fiddle).
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.
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.
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.
相关文章