切割字符串函数

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