怎么对IN子查询使用绑定变量

?

?

在实际使用中，经常会有带in的子查询，如where id in (1,2,3)这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池SQL碎片。所以，在实际应用中，可以采用其他方法，将这些in list给绑定起来。

?

如果需要绑定in list，首先，需要创建两个类型(type)：

?

针对数据类型的

?

CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;

?

针对字符串类型的(每个list的单元大小不要超过1000字节)

?

create or replace type vartabletype as table of varchar2(1000);

?

然后创建两个相关的函数

?

数字列表函数

?

create or replace function str2numList( p_string in varchar2 ) return numTableType

as

? ? v_str long default p_string || ',';

? ? v_n number;

? ? v_data numTableType := numTableType();

begin

? ? loop

? ? v_n := to_number(instr( v_str, ',' ));

? ? exit when (nvl(v_n,0) = 0);

? ? v_data.extend;

? ? v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));

? ? v_str := substr( v_str, v_n+1 );

? ? end loop;

? ? return v_data;

end;

?

字符列表函数

?

create or replace function str2varList( p_string in varchar2 ) return VarTableType

as

v_str long default p_string || ',';

v_n varchar2(2000);

v_data VarTableType := VarTableType();

begin

? ?loop

? ? ? v_n :=instr( v_str, ',' );

? ? exit when (nvl(v_n,0) = 0);

? ? v_data.extend;

? ?v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));

? ? v_str := substr( v_str, v_n+1 );

? ?end loop;

? ?return v_data;

end;

?

创建之后，我们就可以采用如下的方式来使用in list的绑定了。如可以采用如下的三种方案

?

SELECT /*+ ordered use_nl(a,u) */ id, user_id, BITAND(promoted_type,4) busauth

from table(STR2NUMLIST(:bind0)) a,

bmw_users u

where u.user_id = a.column_value;

?

SELECT /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth

from bmw_users u where user_id in

(select * from table(STR2NUMLIST(:bind0)) a);

?

SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id

from bmw_users where user_id in

(SELECT * FROM THE (SELECT CAST(STR2NUMLIST(:bind0) AS NUMTABLETYPE) FROM dual) WHERE rownum<1000);

?

在如上的方案中，以上语句中的hint提示，是为了稳定执行计划，防止Oracle对in list的错误估计而导致走hash连接。一般建议采用第一种方法，比较简单可靠并且可以指定稳定的计划。但是要求数据库的版本比较高，在老版本中(8i)，可能只能采用第三种方法。总的来说，1、2两种方法比3要少6个逻辑读左右

?

例子：

?

create or replace procedure SP_MOVE_DATA(p_string in varchar2)

is

?

begin

?

update t1 set name= 'Orphean' where t1.id in

(select * from table(str2numList(p_string)));

?

insert into t2

select * from t1 where id in

(select * from table(str2numList(p_string)));

?

delete from t1 where id in

(select * from table(str2numList(p_string)));

?

commit;

?

exception

when others then

rollback;

?

end SP_MOVE_DATA;

-----------------