【分享】一次有意思的解决绑定变量问题(where in list问题)

[sotom]

啊，墨迹一句，脱离java圈很久，但依旧觉得自己是搞java的，没办法~~ 呵呵，这次又到j2EE来发牢骚了~~ 内个、内个大家不喜欢的绕过~绕过，呵呵， 下午刚解决了点问题，拿出来给新人们分享下~~~

感觉我java群的朋友们、传给我大话西游的那位（对不起，我很努力的找你,没有找到）、BUG、Lv9(9仔维护群很用心~)、坦克、ChanelＡ哆啦梦、AI~非主流文文、实习牧师~~、大胡子（你丫的很久不见啊...没挂掉吧？）...还有很多..不一一提名~和你们混的很开心

近期公司整理数据库，优化数据库效率问题，提取了一部分未绑定变量的SQL语句（硬解析语句），进行绑定变量优化
简单提取SQL如下

select substr(sql_text,1,35), count(*)
          from v$sqlarea
         group by substr(sql_text,1,35) having count(*) > 50; 

其中有一个语句是类似这样的

sprintf(tmpbuf, "select user_id,user_name,logic_code from t_user a where a.logic_%d in (%s)", i,user_name);

这就需要对现有的 一条拼接的where in list SQL进行绑定变量的优化
基于以前解决过的 where in list 问题，想到的解决方式如下。

1、采用正则表达式

select REGEXP_SUBSTR(&str,'[^,]+',1,level) as value_str
from dual
connect by level <= length(&str)-length(replace(&str,',',''))+1;

直接采用此语句替换

2、采用table函数(貌似又叫做管道函数--如有错误请指正) （我采用了此种方式解决）
思路，将where in list 逗号分隔的字符串转换成一个表

建立数据类型

create or replace type str2tblType as table of varchar2(4000);

将逗号分隔的字符串转换成一行一行的数据函数

create or replace
function str2tbl
( p_str in varchar2,
  p_delim in varchar2 default ',' )
return str2tblType
as
   l_str long default p_str || p_delim;
   l_n   number;
   l_data str2tblType := str2tblType();
 begin
     loop
         l_n := instr( l_str, p_delim );
         exit when (nvl(l_n,0) = 0);
         l_data.extend;
         l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
         l_str := substr( l_str, l_n+1 );
     end loop;
     return l_data;
 end;

实验一下

select * from TABLE(cast(str2tbl('123,abc') as str2TblType));

OK~~呵呵


虽然是一个绑定变量问题，此方法实际是经典的 where in list问题

select * from tab_ a where  a.u_ in(.........)

类似于这样的语句，in 中可拼入的元素个数是有限的（忘记多少个了）...
采用如上正则表达式方式，或table函数方式均可突破此上限~~

[sotom]

沙发~~自己

[lg_asus]

擦，我慢了，还有地板我占了

[疯狂的驴子]

哈哈 木子 ！！！

[BearKin]

...好吧 我回一个兰州烧饼 鄙视下删我回复的家伙

[hackersun12345]

好吧，我来了，学习了