请教一个数据切分的问题

[ddd11326]

表TEMP中数据结构
ID TEMP
1 1,2,3
2 4,5,6
想写出语句检索结果为
1 1
1 2
1 3
2 4
2 5
2 6
在网上找一个切分的函数
create or replace type strsplit_type is table of varchar2(100)
create or replace function strsplit(p_value varchar2, p_split varchar2)
return strsplit_type
pipelined is
v_idx integer;
v_str varchar2(500);
v_strs_last varchar2(4000) := p_value;
begin
loop
v_idx := instr(v_strs_last, p_split);
exit when v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx - 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
pipe row(v_str);
end loop;
pipe row(v_strs_last);
return;
end strsplit;
但是这个只能单列切分
SELECT * FORM TABLE(SELECT strsplit(TEMP,',') FROM TEMP WHERE ID = 1);这个可以
下面这个语句就会报ORA-00600错误
SELECT (SELECT * FROM A.TEMP),ID FROM
(SELECT strsplit(TEMP,',') AS TEMP,ID FROM TEMP)A
请高手帮忙

[CT_LXL]

引用 楼主 ddd11326 的回复:

表TEMP中数据结构 ID TEMP 1 1,2,3 2 4,5,6 想写出语句检索结果为 1 1 1 2 1 3 2 4 2 5 2 6 在网上找一个切分的函数 create or replace type strsplit_type is table of varchar2(100) create or replace function strsplit(p_value varchar2, p_split varchar2) return strsplit_type pipelined is v_idx integer; v_str varchar2(500); v_strs_last varchar2(4000) := p_value; begin loop v_idx := instr(v_strs_last, p_split); exit when v_idx = 0; v_str := substr(v_strs_last, 1, v_idx - 1); v_strs_last := substr(v_strs_last, v_idx + 1); pipe row(v_str); end loop; pipe row(v_strs_last); return; end strsplit; 但是这个只能单列切分 SELECT * FORM TABLE(SELECT strsplit(TEMP,',') FROM TEMP WHERE ID = 1);这个可以 下面这个语句就会报ORA-00600错误 SELECT (SELECT * FROM A.TEMP),ID FROM (SELECT strsplit(TEMP,',') AS TEMP,ID FROM TEMP)A 请高手帮忙

一条SQL就能搞定：

select id, regexp_substr(temp, '[^,]+', 1, level)
  from (select id, temp, rownum rn from t) t1
connect by level <= regexp_count(t1.temp, ',') + 1
       and prior id = id
       and prior rn = rn
       and prior dbms_random.value is not null;

[ddd11326]

谢谢，学了不少东西

[ddd11326]

因为10g没有regexp_count函数可以采用下面的语句 select id, regexp_substr(temp, '[^,]+', 1, level) from (select id, temp, rownum rn from t) t1 connect by level <= LENGTH(temp) - LENGTH(REGEXP_REPLACE(temp, ',', ''))+1 and prior id = id and prior rn = rn and prior dbms_random.value is not null;