ORACLE 如何解析号段，高手上。。。

 有如下数据

写一条sql得到如下数据

麻烦各位大哥大姐帮忙谢谢

[quote]
Sql代码

SQL> WITH tb AS (

2 SELECT 'KC0003' min_card_id,'KC0012' max_card_id,'ww' source_dept FROM DUAL UNION ALL

3 SELECT 'KB0006' min_card_id,'KB0010' max_card_id,'aa' source_dept FROM DUAL

4 )

5 SELECT SUBSTR(t1.min_card_id,1,2) || TO_CHAR(SUBSTR(t1.min_card_id,3) + rn - 1,'fm0999') card_id,

6 t1.source_dept

7 FROM tb t1,

8 (SELECT ROWNUM rn

9 FROM (SELECT MAX(SUBSTR(max_card_id,3) - SUBSTR(min_card_id,3)) + 1 loop_num

10 FROM tb)

11 CONNECT BY ROWNUM <= loop_num) t2

12 WHERE SUBSTR(max_card_id,3) >= SUBSTR(t1.min_card_id,3) + rn - 1

13 ORDER BY t1.source_dept,t2.rn

14 ;

CARD_ID SOURCE_DEPT

KB0006 aa

KB0007 aa

KB0008 aa

KB0009 aa

KB0010 aa

KC0003 ww

KC0004 ww

KC0005 ww

KC0006 ww

KC0007 ww

KC0008 ww

KC0009 ww

KC0010 ww

KC0011 ww

KC0012 ww

15 rows selected

[/quote]

改进下！

[code="sql"]
SELECT REGEXP_SUBSTR (t1.min_card_id, '[a-zA-Z]+')
|| TO_CHAR (REGEXP_SUBSTR (t1.min_card_id, '[0-9]+') + rn - 1,
'FM0999')
card_id,
t1.source_dept
FROM CARD_TEST1 t1,
( SELECT ROWNUM rn
FROM (SELECT MAX(REGEXP_SUBSTR (max_card_id, '[0-9]+')
- REGEXP_SUBSTR (min_card_id, '[0-9]+'))
+ 1
loop_num
FROM CARD_TEST1 )
CONNECT BY ROWNUM <= loop_num) t2
WHERE REGEXP_SUBSTR (max_card_id, '[0-9]+') >=
REGEXP_SUBSTR (t1.min_card_id, '[0-9]+') + rn - 1
ORDER BY t1.source_dept, t2.rn;
[/code]

这个你查询出来在代码里处理更好。不然的话你需要用存储过程，游标，函数之类的。这个在oracle中可以用函数试一试
[code="java"]
public class StorkSpit {

}
[/code]

不好意思 代码没有提交上。

你可以用oracle中的函数试一下。不过这个比较难，你要返回一个数据集，函数通常只返回一个值，不知道能不能返回数据集。如果能够返回数据集。
你写好函数后直接select 执行函数就可以了。

[code="sql"]
SQL> WITH tb AS (
2 SELECT 'KC0003' min_card_id,'KC0012' max_card_id,'ww' source_dept FROM DUAL UNION ALL
3 SELECT 'KB0006' min_card_id,'KB0010' max_card_id,'aa' source_dept FROM DUAL
4 )
5 SELECT SUBSTR(t1.min_card_id,1,2) || TO_CHAR(SUBSTR(t1.min_card_id,3) + rn - 1,'fm0999') card_id,
6 t1.source_dept
7 FROM tb t1,
8 (SELECT ROWNUM rn
9 FROM (SELECT MAX(SUBSTR(max_card_id,3) - SUBSTR(min_card_id,3)) + 1 loop_num
10 FROM tb)
11 CONNECT BY ROWNUM <= loop_num) t2
12 WHERE SUBSTR(max_card_id,3) >= SUBSTR(t1.min_card_id,3) + rn - 1
13 ORDER BY t1.source_dept,t2.rn
14 ;

CARD_ID SOURCE_DEPT

KB0006 aa
KB0007 aa
KB0008 aa
KB0009 aa
KB0010 aa
KC0003 ww
KC0004 ww
KC0005 ww
KC0006 ww
KC0007 ww
KC0008 ww
KC0009 ww
KC0010 ww
KC0011 ww
KC0012 ww

15 rows selected
[/code]