创建 Table Type
CREATE OR REPLACE TYPE type_splitStr IS TABLE OF VARCHAR2 (4000);
Split函数（splitStr）
1 create or replace function splitStr(p_list varchar2,p_sep varchar2 := ',') return type_splitStr pipelined
2 IS
3 l_idx pls_integer;
4 v_list varchar2(50) := p_list;
5 begin
6 loop
7 l_idx := instr(v_list,p_sep);
8 if l_idx > 0 then
9 pipe row(substr(v_list,1,l_idx-1));
10 v_list := substr(v_list,l_idx+length(p_sep));
11 else
12 pipe row(v_list);
13 exit;
14 end if;
15 end loop;
16
17 return;
18 end splitStr;
测试：
select * from table(splitStr('971FA4W3006586|971FA4W3006686','|'))