- 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
- --该片段来自于http://www.codesnippet.cn/detail/030420132698.html
来源: http://www.codesnippet.cn/detail/030420132698.html