如何正确的在对IN操作使用变量绑定

"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 如何正确的在对IN操作使用变量绑定 - 站长资讯 中国站长第一门户：WWW.CHINAHTML.COM 网通镜像 技术文档 CH Blog 极 客 集 电信论坛 网通论坛 免费体验美国主机条码条形码打印机文字广告位招租 首页　新闻资讯　搜索优化　推广研究　策划盈利　建站心得　站长故事 商业　人物　门户　报告 评论　网站欣赏　极客集   网页制作　网络编程　数据库　图象媒体　服务器　网络应用　软件教学 产品　新知 　安全　开源 访谈　网址大全　实用查询       栏目搜索     标题 简介 内容 作者 搜索全部
搜索全部 新闻资讯 网站运营 网页制作 网络编程 数 据 库 图象设计 服 务 器 网络应用 软件教学 代码下载 工具下载 素材下载 书籍下载 网站欣赏 Flash欣赏         你的位置：首页 > 数 据 库 > 数据库综合 >   如何正确的在对IN操作使用变量绑定 日期：2006-12-11 12:12:25  点击：21  作者：  来源：

家都知道在sql语句中变量绑定的重大意义，甚至有高人指出：一个应用想要它变的很糟糙的话，只要不使用变量绑定就可以了。这话的确不假。这时可能有人就会问：我该绑定的都绑定了，可是我实在无法想到好的方法在in操作符中使用变量绑定。下面的方法可以解决这个疑问。

我们知道in操作符接受两种list, 一个是由一个个item组成的list,　另一个是由另一个表中选出的list。第一种方式由于值个数不一定，变量绑定具有一定的困难。于是我们思路就集中到准备将一个字符串传入到sql语句中，然后使用一个方法将字符串parse成一个table, 再传回in operator:

首先我们来创建方法，及由此方法返回的table类型：

　　rudolf@test9i> create or replace type numTableType as table 　　2　　　 of number 　　3　/ 　　 　　Type created. 　　 　　rudolf@test9i> create or replace function str2numList( p_string in varchar2 ) return 　　2　numTableType 　　3　as 　　4　　　v_str　 long default p_string || ','; 　　5　　　v_n　　　　number; 　　6　　　v_data　　numTableType := numTableType(); 　　7　begin 　　8　　　loop 　　9　　　　　v_n := to_number(instr( v_str, ',' )); 　　10　　　　　exit when (nvl(v_n,0) = 0); 　　11　　　　　v_data.extend; 　　12　　　　　v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); 　　13　　　　　v_str := substr( v_str, v_n+1 ); 　　14　　　end loop; 　　15　　　return v_data; 　　16　end; 　　17　/ 　　 　　Function created.　　

我们把下列语句：

　　select object_name from t where object_id in ( xx,xxx,xxx,...);

改为：

　　select object_name from t 　　where object_id in 　　( select * from　THE ( select cast( str2numList(:variable ) as numtableType ) from　dual ) 　　);　　

现在我们来看看是否达到了我们的目的：

　　rudolf@test9i> var STR varchar2(3000) 　　 　　rudolf@test9i>　exec :STR := '5770,1810,4481' 　　 　　PL/SQL procedure suclearcase/" target="_blank" >ccessfully completed. 　　 　　rudolf@test9i> alter session set events= '10046 trace name context forever, level 4' 　　2　/ 　　 　　Session altered. 　　 　　rudolf@test9i> select object_id,object_name from t where object_id in ( 　　2　　 select * from　THE ( select cast( str2numList(:STR ) as numtableType ) from 　　3　　dual ) ) 　　4 　　rudolf@test9i> / 　　 　　OBJECT_ID OBJECT_NAME 　　---------- ------------------------------ 　　1810 ALL_ALL_TABLES 　　4481 AGGXMLINPUTTYPE 　　5770 ALL_APPLY　　

检查dump文件，我们看到(注意星号的行)：

　　 　　PARSING IN CURSOR #1 len=146 dep=0 uid=81 oct=3 lid=81 tim=1036636837633718 hv=2833917919 ad='529b9f48' 　　select object_id,object_name from t where object_id in ( 　　select * from　THE ( select cast( str2numList(:STR ) as numtableType ) from 　　dual ) ) 　　END OF STMT 　　PARSE #1:c=0,e=655,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1036636837633701 　　BINDS #1:　　　　　　　　　***** 　　bind 0: dty=1 mxl=2000(200) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=2000 offset=0 　　bfp=406402fc bln=2000 avl=14 flg=05 　　value="5770,1810,4481" 　　EXEC #1:c=0,e=245,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1036636837645450 　　FETCH #1:c=20000,e=16739,p=0,cr=52,cu=0,mis=0,r=1,dep=0,og=4,tim=1036636837662283 　　FETCH #1:c=0,e=4644,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=4,tim=1036636837667979

这样我们就完成了。

原文转自：http://www.ltesting.net