逗号分隔的字符串转换为行数据（collection）

CREATE OR REPLACE FUNCTION "GET_STR_TAB" (v_str in varchar2) return table_str pipelined as
  v_new_str varchar2(8000);

begin

  if v_str is null then
    pipe row(-1);
  else
    v_new_str:=replace(replace(v_str,chr(10),''),chr(9),'');

    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;

 

另还要有一个type类型

CREATE OR REPLACE TYPE "TABLE_STR"       as table of number