oracle 批量列转行 逗号分隔

示例：

with temp as(select '1,2,3' nums, 'a' names from dual        union all        select '4,5' nums, 'b' names from dual        union all        select '6,7' nums, 'c' names from dual        union all        select '8' nums, 'c' names from dual        union all        select '9,10' nums, 'c' names from dual        union all        select '11,12' nums, 'c' names from dual        union all        select '13,14' nums, 'c' names from dual        union all        select '15,16' nums, 'c' names from dual        union all        select '17,18' nums, 'c' names from dual        union all        select '19,20' nums, 'c' names from dual        union all        select '21,22,23,24,25,26,27,28,29,30,31,32,33,34' nums, 'c' names from dual)select regexp_substr(nums,'[^,]+',1,b.lv) order_num,namesfrom temp, (select level lv from dual connect by level<=(select max(length(regexp_replace(nums,'[^,]+'))+1) from temp)) b   --用于分配行数（行数取最大的nums分隔数）where b.lv <=length(regexp_replace(nums,'[^,]+'))+1 order by order_num