Oracle函数 实现 Split

第一，先创建一个Type

 

 

CREATE OR REPLACE TYPE type_split IS TABLE OF VARCHAR2 (4000)


 

第二，创建函数

 

create or replace function split(p_list varchar2,p_sep varchar2 := ',') return type_split pipelined
IS
 l_idx pls_integer;
 v_list varchar2(50) := p_list;

 begin
      loop
           l_idx := instr(v_list,p_sep);
           if l_idx > 0 then
               pipe row(substr(v_list,1,l_idx-1));
               v_list := substr(v_list,l_idx+length(p_sep));
           else
                pipe row(v_list);
                exit;
           end if;

      end loop;


      return;
 end split;


 

第三，调试

 

select * from table(split('aaa,bbb,ccc',','))