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

看到这个文章的时候感觉这个文章很不错，所以转过来收藏一下。

 

在实际使用中，经常会有带 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;