如何将1000多个值放入Oracle IN子句[duplicate]

将这些值放在临时表中,然后执行select where id in(select id from TENTABLE)

select column_X, ... from my_table
where ('magic', column_X ) in (
        ('magic', 1),
        ('magic', 2),
        ('magic', 3),
        ('magic', 4),
             ...
        ('magic', 99999)
    ) ...

我几乎可以肯定,您可以使用或在多个INs之间拆分值:

select * from table1 where ID in (1,2,3,4,...,1000) or 
ID in (1001,1002,...,2000)

select * from table1 where ID in (1,2,3,4,...,1000)
union all
select * from table1 where ID in (1001,1002,...)

您首先从哪里获得ID列表?因为它们是数据库中的ID,所以它们是否来自以前的某个查询?

我过去看到这一点是因为:-

1. 缺少一个引用表,正确的方法是添加新表,在该表上放置一个属性并连接到该表

我认为可能有更好的方法来重做这段代码,而仅仅是让这条SQL语句工作。如果你提供更多的细节,你可能会有一些想法。

使用表(…)中的…:

create or replace type numbertype
as object
(nr number(20,10) )
/ 

create or replace type number_table
as table of numbertype
/ 

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select *
    from employees , (select /*+ cardinality(tab 10) */ tab.nr from table(p_numbers) tab) tbnrs 
    where id = tbnrs.nr; 
end; 
/ 

这是极少数需要提示的情况之一,否则Oracle将不使用列id上的索引。这种方法的优点之一是Oracle不需要一次又一次地硬解析查询。大多数情况下,使用临时表的速度较慢。

编辑1 简化了程序(感谢jimmyorr)+示例

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select /*+ cardinality(tab 10) */ emp.*
    from  employees emp
    ,     table(p_numbers) tab
    where tab.nr = id;
end;
/

set serveroutput on 

create table employees ( id number(10),name varchar2(100));
insert into employees values (3,'Raymond');
insert into employees values (4,'Hans');
commit;

declare
  l_number number_table := number_table();
  l_sys_refcursor sys_refcursor;
  l_employee employees%rowtype;
begin
  l_number.extend;
  l_number(1) := numbertype(3);
  l_number.extend;
  l_number(2) := numbertype(4);
  tableselect(l_number, l_sys_refcursor);
  loop
    fetch l_sys_refcursor into l_employee;
    exit when l_sys_refcursor%notfound;
    dbms_output.put_line(l_employee.name);
  end loop;
  close l_sys_refcursor;
end;
/

这将输出:

Raymond
Hans

我也在这里寻找解决办法。

//remove dupes
items = items.RemoveDuplicates();

//how to break the items into 1000 item batches        
batches = new batch list;
batch = new batch;
for (int i = 0; i < items.Count; i++)
{
    if (batch.Count == 1000)
    {
        batches.Add(batch);
        batch.Clear()
    }
    batch.Add(items[i]);
    if (i == items.Count - 1)
    {
        //add the final batch (it has < 1000 items).
        batches.Add(batch); 
    }
}

// now go query the db for each batch
results = new results;
foreach(batch in batches)
{
    results.Add(query(batch));
}

在通常不超过1000项的情况下,这可能是一个很好的折衷方案,因为超过1000项将是您的“高端”边缘案例方案。例如,如果您有1500个项目,那么两个(1000500)的查询就不会那么糟糕了。这还假设每个查询本身并不特别昂贵。

这 不会

如果在IN子句中指定2000个ID,它将失败。

select ... 
where id in (1,2,....2000) 

以下查询:

select ... 
where id in (select userId 
             from temptable_with_2000_ids ) 

你所能做的,实际上是把这些记录分成1000条记录,然后一组一组地执行。

下面是一些Perl代码,它试图通过创建一个内联视图并从中进行选择来绕过限制。语句文本通过使用每行12个项目进行压缩,而不是单独从DUAL中选择每个项目,然后通过将所有列合并在一起进行解压缩。解压中的UNION或UNION ALL应该没有什么区别,因为它都在一个in中,在加入之前,它会对其施加唯一性,但在压缩中,UNION ALL用于防止大量不必要的比较。因为我过滤的数据都是整数,引用不是问题。

#
# generate the innards of an IN expression with more than a thousand items
#
use English '-no_match_vars';
sub big_IN_list{
    @_ < 13 and return join ', ',@_;
    my $padding_required = (12 - (@_ % 12)) % 12;  
    # get first dozen and make length of @_ an even multiple of 12
    my ($a,$b,$c,$d,$e,$f,$g,$h,$i,$j,$k,$l) = splice @_,0,12, ( ('NULL') x $padding_required );

    my @dozens; 
    local $LIST_SEPARATOR = ', '; # how to join elements within each dozen
    while(@_){
        push @dozens, "SELECT @{[ splice @_,0,12 ]} FROM DUAL"
    };  
    $LIST_SEPARATOR = "\n    union all\n    "; # how to join @dozens 
    return <<"EXP";
WITH t AS (
    select $a A, $b B, $c C, $d D, $e E, $f F, $g G, $h H, $i I, $j J, $k K, $l L FROM     DUAL
    union all
    @dozens
 )
select A from t union select B from t union select C from t union
select D from t union select E from t union select F from t union
select G from t union select H from t union select I from t union 
select J from t union select K from t union select L from t
EXP
}

可以这样使用:

my $bases_list_expr = big_IN_list(list_your_bases());
$dbh->do(<<"UPDATE");
    update bases_table set belong_to = 'us'
    where id in ($bases_list_expr)
UPDATE

IN 子句,您可以尝试使用 JOIN 另一个表正在获取id。这样我们就不需要担心限制。只是我这边的一个想法。

而不是 SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1000);

使用以下命令:

SELECT * FROM table1 WHERE ID IN (SELECT rownum AS ID FROM dual connect BY level <= 1000);

SELECT * FROM table1 WHERE ID IN (SELECT distinct(ID) FROM tablewhereidsareavailable);

干杯