当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

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


  家都知道在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 successfully 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
相关内容
赞助商链接