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

啊，墨迹一句，脱离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函数方式均可突破此上限~~

11 个解决方案

#1

沙发~~自己

#2

该回复于2012-05-21 16:31:14被版主删除

#3

该回复于2012-05-21 16:31:50被版主删除

#4

该回复于2012-05-21 16:31:15被版主删除

#5

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

#6

哈哈木子！！！

#7

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

#8

好吧，我来了，学习了

#9

该回复于2012-05-21 16:39:27被版主删除

#10

该回复于2012-05-21 17:06:41被版主删除

#11

该回复于2012-05-21 17:10:52被版主删除

#1