日期:2014-05-16  浏览次数:21346 次

怎么讲oracle数组的数据放到查询结果里?
RT
如:得到一个oracle数组number(12,33,44,55),怎么将其转成查询结果,如下:
ndata
12
33
44
55

------解决方案--------------------
改进方法:

WITH t1 AS
( SELECT '1001,1002,1003,1004,1005' AS c2 FROM dual
UNION ALL
SELECT '101,102,103,1041,1051' FROM dual
)
SELECT substr(',' || c2 || ',',--前后补分割符,
instr(',' || c2 || ',',',',1,b.rn) + 1,--取第rn个分割符的位置+1
instr(',' || c2 || ',',',',1,b.rn + 1) - instr(',' || c2 || ',',',',1,b.rn) - 1 --取截取的长度 = 第rn+1个位置-第rn个位置
) AS c2
FROM t1,
(SELECT rownum rn FROM t1 connect BY rownum<10
) b
where LENGTH(c2)-LENGTH(REPLACE(c2,','))+1 >= b.rn
ORDER BY b.rn
--result
C2
-------------------------- 
101
1001
102
1002
103
1003
1041
1004
1051
1005