尝试从 IN 语句生成子集

Question

我正在为用户编写一个解决方案，该解决方案将他们输入的电话号码列表与客户数据库相匹配。

用户需要输入以逗号分隔的电话号码（整数）列表，并且查询需要告诉用户列表中的哪些电话号码不在数据库中。

我想到的唯一方法是首先创建一个子集 NUMBER_LIST，其中包含我可以加入的所有电话号码，然后从我从客户数据库带回的列表中排除该列表。

WITH NUMBER_LIST AS (
    SELECT INTEGERS 
    FROM (
        SELECT level - 1 + 8000000000 INTEGERS
           FROM dual
        CONNECT BY level <= 8009999999-8000000000+1
    )
    WHERE INTEGERS IN (8001231001,8001231003,8001231234,8001231235,...up to 1000 phone numbers)
)

这里的问题是上面的代码可以很好地创建我的子集，对于 800-000-0000 和 800-999-9999 之间的数字。我的列表和客户数据库中的电话号码可以是任何范围（不仅仅是 800 个号码）。我这样做只是为了测试。从该查询生成子集大约需要 6 秒。如果我创建 CONNECT BY LEVEL 以包含从 100-000-0000 到 999-999-9999 的所有数字，那么运行我的查询就会耗尽内存来创建这么大的子集（而且我认为创建一个巨大的列表是荒谬的矫枉过正并使用我的 IN 语句将其分解）。

问题是创建初始子集。我可以处理其余的查询，但我需要能够生成数字子集，以便从 IN 语句针对我的客户数据库进行查询。

有几件事需要记住：

1. 我无法首先将数字加载到临时表中。用户将自己输入“IN(...,...,...)”语句。
2. 这需要是单个语句，没有额外的函数或变量声明
3. 数据库是Oracle 10g，我正在使用SQL Developer来创建查询。
4. 用户知道他们只能在 IN 语句中输入 1000 个数字。这需要足够强大才能从整个区号范围中选择任何 1000 个号码。
5. 最终结果是获取数据库中没有的电话号码列表。简单的 NOT IN... 不起作用，因为这将返回哪些数字在数据库中，但不在我的列表中。

我如何才能使此功能适用于 1000000000-9999999999 之间的所有号码（或所有可能的美国 10 位电话号码）。我可能会完全错误地生成我最初的巨大列表，然后排除除 IN 语句之外的所有内容，但我不确定从这里到哪里去。

非常感谢您提前提供的帮助。我从你们所有人身上学到了很多东西。

Answer 1

您可以使用以下命令：

SELECT *
  FROM (SELECT regexp_substr(&x, '[^,]+', 1, LEVEL) phone_number
           FROM dual
         CONNECT BY LEVEL <= length(&x) - length(REPLACE(&x, ',', '')) + 1)
 WHERE phone_number NOT IN (SELECT phone_table.phone_number 
                              FROM phone_table)

第一个查询将构建包含各个电话号码的列表。

Answer 2

这个问题与“如何绑定列表中的”问题密切相关，该问题已经在这里出现过几次。我发布了一个答案 Dynamic query with HibernateCritera API & Oracle - 过去的性能。

像这样的事情应该可以满足您的要求：

create table phone_nums (phone varchar2(10));

insert into phone_nums values ('12345');

insert into phone_nums values ('23456');

with bound_inlist
  as
  (
  select
    substr(txt,
           instr (txt, ',', 1, level  ) + 1,
           instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
           as token
    from (select ','||:txt||',' txt from dual)
  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
  )
  select *
from bound_inlist a
where  not exists (select null from phone_nums where phone = token); 

这里逗号分隔的电话号码列表已绑定到查询中，因此您可以正确使用绑定变量，并且您将能够输入可能无限数量的电话号码来一次性检查（尽管我会检查 4000 和 32767 个字符边界以确定）。

Answer 3

你说你不能使用临时表或过程或自定义函数——如果可以的话，这将是一个简单的任务。

用于提交此查询的客户端工具是什么？是否有原因导致您无法从数据库中查询所有电话号码并在客户端上进行比较？

Answer 4

如果您受到限制，必须使用 IN (n1,n2,n3,...,n1000) 来解决该问题，那么您的方法似乎是唯一的解决方案。

正如您所提到的，这是您预先创建的一个大列表。

你能稍微调整一下你的方法吗？

WITH NUMBER_LIST (number) AS (
            SELECT n1    FROM DUAL
  UNION ALL SELECT n2    FROM DUAL
  UNION ALL SELECT n3    FROM DUAL
  ...
  UNION ALL SELECT n1000 FROM DUAL
)