oracle如何将以固定分隔符(逗号)分隔的多行字符串拆分为多行（表生成）

--将多行字符串拆分

with p as --构建数据临时表
 (select 1 as id, 'aa,bb,cc,dd,ee' as split_string, 1 as num
    from dual
  union all
  select 2, 'ff,gg,hh,ii,jj,kk', 3
    from dual
  union all
  select 3, 'aa,bb,cc,dd,ee', 2
    from dual
  union all
  select 4, 'ff,gg,hh,ii,jj,kk', 4
    from dual)
select t1.split_string,
       regexp_substr(t1.split_string, '[^,]+', 1, level) as split_string,
--level为对应第level个所拆分字符串；正则表达式匹配分隔符拆分
       t1.num
  from p t1
connect by t1.id = prior t1.id --递归自身
       and prior dbms_random.value is not null--避免死循环
       and level <= length(t1.split_string) -
           length(regexp_replace(t1.split_string, ',', '')) + 1 --找到拆分字符串个数,即分隔符个数加1