oracle 函数实现 返回数据集

/* ******************* 方法一 *****************************/
-- create type
create or replace type t_str is table of varchar2(2000);

-- create function
create or replace function uf_splitstr(i_str varchar2, i_split varchar2)
  return t_str
  pipelined is
  l_str    t_str := t_str();
  l_tmpstr varchar2(2000) := '';
  l_char   varchar2(1);
begin
  if i_str = '' then
    return;
  end if;

  for i in 1 .. length(i_str)
  loop
    l_char := substr(i_str, i, 1);
    if l_char = i_split then
      pipe row(l_tmpstr);
      l_tmpstr := '';
    else
      l_tmpstr := l_tmpstr || l_char;
    end if;
  end loop;
 
  pipe row(l_tmpstr);
 
  return;
end;

-- 例子
select * from table(uf_splitstr('abcd, asdfad, adfadf,', ','))

/* ******************* 方法二 *****************************/
-- 直接使用sql
with t as (select ',aa' str, ',' sep from dual)
select substr(t.str,
              t2.l,
              decode(instr(t.str, sep, l),
                     0,
                     length(t.str) + 1,
                     instr(t.str, sep, l)) - t2.l)
  from t,
       (select level l from dual, t connect by rownum <= length(t.str) + 1) t2
 where substr(sep || t.str, l, 1) = sep