Oracle a Parameter with multi value_.NET_编程开发_程序员俱乐部

 备注：此两种方案，都因为oracle内部字符函数的参数长度4000限制。
另外，个人测试，性能不如”将数据插入物理表再JOIN查询“或”每1000次ID做一次IN查询“的总的运行速度。 即ID的个数越多，这两种方案的总的运行效率越不好（总的耗时越多）。 

Solution 1: Use oracle Regex

select t1.field_01,t1.field_02

    from t_XXX t1

    where Exists

    (

       select 1 from

       (

            SELECT TRIM(REGEXP_SUBSTR (:v_id_list, '[^,]+', 1,rownum)) as ID

               FROM DUAL

               CONNECT BY ROWNUM <= LENGTH (:v_id_list) - LENGTH (REPLACE (:v_id_list, ',','')) + 1

        )  tmp

        where t1.ID= tmp.ID

   );

 

------------------------------------------------------------------------------------------------------------

 

Solution 2: Use record type

 

Step1: create record type:

create or replace typemyTableType as table of varchar2 (32767);

 

----------------------------------------------  

Step2:Create function that it convert string to datatable.

Create and replace function var_list(p_string in varchar2 ) return myTableType

  as

      l_string       long default p_string || ',';

      l_data         myTableType := myTableType();

      n              number;

  begin

    loop

        exit whenl_string is null;

        n := instr(l_string, ',' );

        l_data.extend;

        l_data(l_data.count) := 

                ltrim( rtrim( substr( l_string, 1, n-1 ) ) );

        l_string :=substr( l_string, n+1 );

   end loop;

 

   return l_data;

end;

 

----------------------------------------------

 

Step3: test example

select *

    from THE 

    ( 

            select cast( var_list('abc, xyz, 012') as

                             mytableType )from dual 

    ) a

 

------------------------------------------------------

 

Step4: I Use in code, sql format

Note:class="Apple-converted-space"> :v_id_list’svalue is like ：value_1,value_2,..,value_n. and n<=1000

select field_xxx01, t1.field_xxx02

    from t_xxxx t1

    where Exists

    (

       select 1 from THE

       (

            select cast( var_list(:v_id_list) as  --select cast( in_list('abc, xyz, 012') as

                             mytableType ) from dual

        )  t2

        where t1.field_ID = t2.COLUMN_VALUE

   );

------------------------------------------------------  create or replace procedure p_getData_byMultiValue (    v_id_list in varchar2,    v_cursor out sys_refcursor ) is begin  /*    open v_cursor     select *     from THE     (              select cast( in_list(v_id_list) as  -- select cast( in_list('abc, xyz, 012') as                               mytableType ) from dual      ) a; */      open v_cursor     select *     from T1     where Exists      (          select 1 from           (              select cast( in_list(v_id_list) as  -- select cast( in_list('abc, xyz, 012') as                               mytableType ) from dual           )  t2          where t1.ID = t2.COLUMN_VALUE    );     end p_getData_byMultiValue;  参考文献：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425