存储过程解决数据拆分并插入表的问题

[yiyuanhong]

一个表的数据如下：
WID WEEK TIMET NAME
SYS_GUID() ,1,2,3,4,5 a0506 JIM
SYS_GUID() ,1,3,5 a091011 TOM
现在需要拆分并插入另外一张表，表结构和上面的一样，希望获得的结果是：
WID WEEK TIMET NAME
SYS_GUID() 1 05 JIM
SYS_GUID() 2 05 JIM
SYS_GUID() 3 05 JIM
SYS_GUID() 4 05 JIM
SYS_GUID() 5 05 JIM

SYS_GUID() 1 06 JIM
SYS_GUID() 2 06 JIM
SYS_GUID() 3 06 JIM
SYS_GUID() 4 06 JIM
SYS_GUID() 5 06 JIM

SYS_GUID() 1 09 TOM
SYS_GUID() 3 09 TOM
SYS_GUID() 5 09 TOM

SYS_GUID() 1 10 TOM
SYS_GUID() 3 10 TOM
SYS_GUID() 5 10 TOM

SYS_GUID() 1 11 TOM
SYS_GUID() 3 11 TOM
SYS_GUID() 5 11 TOM

就是根据WEEK和TIMET来拆分，WEEK x TIMET交叉着拆分。TIMET前面字母a的不需要截取的，a后面的2位为一个数，比如a0102，实际上就是01和02。有哪位大侠帮我写一点存储过程，小弟新手，水平太差，谢谢

[大明湖畔_帅锅]

还没怎么想 写个一般常理的 可能效率没那么好 可以再优化的

with t as
(
     select 'SYS_GUID()' wid,',1,2,3,4,5' week,'a0506' timet,'jim' name from dual union all
     select 'SYS_GUID()' wid,',1,3,5' week,'a091011' timet,'TOM' name from dual 
)

select distinct wid,week,substr(timet,2*level,2) timet,name
from 
(
select wid,substr(week,2*level,1) week,timet,name
from t 
connect by level < length(week)/2+1
)
connect by level < length(timet)/2
order by timet


      wid     week    timet    name 
----------------------------------------------------
1	SYS_GUID()	1	05	jim
2	SYS_GUID()	2	05	jim
3	SYS_GUID()	3	05	jim
4	SYS_GUID()	4	05	jim
5	SYS_GUID()	5	05	jim
6	SYS_GUID()	1	06	jim
7	SYS_GUID()	2	06	jim
8	SYS_GUID()	3	06	jim
9	SYS_GUID()	4	06	jim
10	SYS_GUID()	5	06	jim
11	SYS_GUID()	1	09	TOM
12	SYS_GUID()	3	09	TOM
13	SYS_GUID()	5	09	TOM
14	SYS_GUID()	1	10	TOM
15	SYS_GUID()	3	10	TOM
16	SYS_GUID()	5	10	TOM
17	SYS_GUID()	1	11	TOM
18	SYS_GUID()	3	11	TOM
19	SYS_GUID()	5	11	TOM

[yiyuanhong]

引用 1 楼 HJ_daxian 的回复:

还没怎么想 写个一般常理的 可能效率没那么好 可以再优化的 SQL code ? 123456789101112131415161718192021222324252627282930313233343536373839 with t as( select 'SYS_GUID()' wid,',1,2,3,4,5' week,'a0506' time……

嗯，数据量小的时候还好，几W条我这边就不行了，执行了几分钟都没出结果了

[forgetsam]

直接connect by肯定连死。 先查询出串的最大切分数，比如10，造个自然数表(select level n from dual connect by level< 10)和主表笛卡尔积，结果集最多x10