在遊标中如何實作Select * from AAA where BB in XXX

在遊标中如何實作一個Select * from AAA where BB in XXX語句，

XXX為傳進的一個參數，格式是('AA','BB',...)

動态遊标在所難免，而你真正需要的是 綁定變量

使用in的綁定變量形勢

如何在對in操作使用變量綁定

Posted by: RudolfLu

Posted on: 2003-08-22 09:56

如何在對in操作使用變量綁定 

　　大家都知道在sql語句中變量綁定的重大意義，甚至有高人指出：一個應用想要它變的很糟糙的話，隻要不使用變量綁定就可以了。這話的确不假。這時可能有人就會問：我該綁定的都綁定了，可是我實在無法想到好的方法在in操作符中使用變量綁定。下面的方法可以解決這個疑問。 

　　我們知道in操作符接受兩種list, 一個是由一個個item組成的list,　另一個是由另一個表中選出的list。第一種方式由于值個數不一定，變量綁定具有一定的困難。于是我們思路就集中到準備将一個字元串傳入到sql語句中，然後使用一個方法将字元串parse成一個table, 再傳回in operator: 

    首先我們來建立方法，及由此方法傳回的table類型： 

        [email protected]> create or replace type numTableType as table 

          2 of number 

          3 / 

        Type created. 

        [email protected]> 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 ) 

        　　　　　　); 

    現在我們來看看是否達到了我們的目的： 

    　　[email protected]> var STR varchar2(3000) 

        [email protected]> exec :STR := '5770,1810,4481' 

        PL/SQL procedure successfully completed. 

        [email protected]> alter session set events= '10046 trace name context forever, level 4' 

          2 / 

        Session altered. 

        [email protected]> 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 

        [email protected]> / 

         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 

　　　看得出，它的确使用了變量綁定。