1.传进去一个字符串,类似于'123,456';
CREATE OR REPLACE FUNCTION GET_STRING_TAB (v_str in varchar2) return table_string pipelined as
v_new_str varchar2(8000);
begin
if v_str is null then
pipe row(-1);
else
v_new_str:=v_str;
while 1=1 loop
if instr(v_new_str,',')=0 then
pipe row(v_new_str);
exit;
else
pipe row(substr(v_new_str,1,instr(v_new_str,',')-1));
v_new_str:=substr(v_new_str,instr(v_new_str,',')+1);
end if;
end loop;
end if;
return;
end;
2 select * from table(get_string_tab('100002236,100002236'))
结果: COLUMN_VALUE
1 100002236
2 100002236
CREATE OR REPLACE FUNCTION GET_STRING_TAB (v_str in varchar2) return table_string pipelined as
v_new_str varchar2(8000);
begin
if v_str is null then
pipe row(-1);
else
v_new_str:=v_str;
while 1=1 loop
if instr(v_new_str,',')=0 then
pipe row(v_new_str);
exit;
else
pipe row(substr(v_new_str,1,instr(v_new_str,',')-1));
v_new_str:=substr(v_new_str,instr(v_new_str,',')+1);
end if;
end loop;
end if;
return;
end;
2 select * from table(get_string_tab('100002236,100002236'))
结果: COLUMN_VALUE
1 100002236
2 100002236