Oracle a Parameter with multi value
另外，个人测试，性能不如”将数据插入物理表再JOIN查询“或”每1000次ID做一次IN查询“的总的运行速度。
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: :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
);
