试试这个
[php]
with tt as (
select 1 id ,'abc' name ,'c001/c002/c007' code from dual union all
select 2,'efg','c001/c003' from dual union all
select 3,'ttt','c008/c010' from dual
)
select * from (
select id,
name,
rn,
decode(rn,
1,
substr(code, 1, instr('c001/c002/c007', '/', 1, 1)-1),
2,
substr(code, instr('c001/c002/c007', '/', 1, 1)+1,
instr('c001/c002/c007', '/', 1, 2)-instr('c001/c002/c007', '/', 1, 1)-1),
3,
substr(code,instr('c001/c002/c007', '/', 1, 2)+1)
) rnn
from (select * from tt, (select rownum rn from dual connect by rownumcreate table h_t(id number ,name varchar2(20),code varchar2(50));
表已创建。
SQL> begin
2insert into h_t values (1, 'abc', 'c001/c002/c007');
3insert into h_t values (2, 'efg', 'c001/c003');
4insert into h_t values (3, 'ttt', 'c008/c010');
5commit;
6end;
7/
PL/SQL 过程已成功完成。
SQL> select * from h_t;
ID NAME
CODE
---------- -------------------- --------------------------------------------------
1 abc
c001/c002/c007
2 efg
c001/c003
3 ttt
c008/c010
SQL> create table h_t_1 as select * from h_t where rownumdeclare
2l_i number;
3l_p number;
4l_times number;
5l_str varchar2(10);
6begin
7for x in (select * from h_t) loop
8l_i := 1;
9l_times := length(x.code) - length(replace(x.code, '/', '')) + 1;
10for y in 1 .. l_times loop
11l_p := l_i;
12l_i := instr(x.code || '/', '/', l_i) + 1;
13l_str := substr(x.code, l_p, l_i - l_p - 1);
14insert into h_t_1 values (x.id, x.name, l_str);
15end loop;
16end loop;
17commit;
18end;
19/
PL/SQL 过程已成功完成。
SQL> select * from h_t_1;
ID NAME
CODE
---------- -------------------- --------------------------------------------------
1 abc
c001
1 abc
c002
1 abc
c007
2 efg
c001
2 efg
c003
3 ttt
c008
3 ttt
c010
已选择7行。
[/php]
|
