使用内存表table()和connect by level 处理 一行多条数据显示问题

<p>一行多条数据显示问题比如:check_value varchar2(4000) values('1240,1110,1207,1134,1148,1184,10386')</p>
<p>想把这行该列的数据作为参数给SQL语句</p>

<p>select *</p>
<p>from a</p>
<p>where value in( check_value);</p>

<p>核心代码:</p>
<p> select substr<br>
(<br>
pi_column,<br>
instr(','||pi_column,',',1,level),<br>
instr(pi_column||',',',',1,level)-instr(','||pi_column,',',1,level)<br>
) a<br>
from dual<br>
connect by level<=length(pi_column)-length(replace(pi_column,','))+1;</p>

<p>用游标 open r_cur for 上面代码</p>

<p>然后 提取到字符数组中</p>
<p> FETCH r_cur bulk collect into lv_sql;</p>

<p>数组声明和初始化:</p>
<p> lv_sql type_array := type_array();</p>

<p>数组类型:</p>
<p> create or replace type type_array as table of varchar2(50)</p>

<p>整个函数代码:</p>
<p>create or replace function f_etl_proc(pi_column in varchar2) return type_array is<br><br>
lv_sql type_array := type_array();<br>
r_cur sys_Refcursor;<br><strong>begin</strong>
<br><br>
open r_cur for<br>
select substr<br>
(<br>
pi_column,<br>
instr(','||pi_column,',',1,level),<br>
instr(pi_column||',',',',1,level)-instr(','||pi_column,',',1,level)<br>
) a<br>
from dual<br>
connect by level<=length(pi_column)-length(replace(pi_column,','))+1;<br><br>
FETCH r_cur bulk collect into lv_sql;<br><br>
return(lv_sql);<br><strong>end</strong>
f_etl_proc; </p>

<p>返回的对象数组还不能被使用需要TABLE()来处理</p>
<p>select *<br>
from <strong>table </strong>
(select f_etl_proc(check_value)<br>
from etl_condition <br>
where condition_id = 2)</p>

<p>最后</p>
<p>select *</p>
<p>from a</p>
<p>where value in( select *<br>
from <strong>table </strong>
(select f_etl_proc(check_value)<br>
from etl_condition <br>
where condition_id = 2</p>
<p> )</p>
<p>);</p>