oracle的in的值超过3000,如何将超过1000个值放入Oracle IN子句中

tuinstoel..

5

从表中使用......(...:

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