切割字符串函数

CREATE OR REPLACE TYPE table_userid is table of varchar2(100);

create or replace function get_userid_tab(v_str in varchar2) return table_userid pipelined as

v_new_str varchar2(4000);

begin

if nvl(v_str,null) is not null then


v_new_str:=v_str;

while 1=1 loop

if instr(v_new_str,',')=0 then

pipe row(to_number(v_new_str));

exit;

else

pipe row(to_number(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;

测试: select * from table(get_userid_tab('16,17,18,19,20'));

结果:
16
17
18
19
20