insert語句為:
insert into temp_0428_test2(pro_key,nip)
with temp0 as
(select LEVEL lv from dual CONNECT BY LEVEL <= 100)
select pro_key, substr(t.vals, instr(t.vals, ',', 1, tv.lv) + 1, instr(t.vals, ',', 1, tv.lv + 1) - (instr(t.vals, ',', 1, tv.lv) + 1)) AS name
from (select pro_key,
',' || nip || ',' AS vals,
length(nip || ',') - nvl(length(REPLACE(nip, ',')), 0) AS cnt
from temp_0428_test1) t
join temp0 tv
on tv.lv <= t.cnt
order by 1;
