Oracle:遍历表的查询或过程

Question

我有一个这样做的表：

//tr>//tr>//tr>//tr>//tr>

COLUMN_A	COLUMN_B	COLUMN_C
3510837	B0110555
3510837	E15627104
3510838	E15627104
800384	L0309599	18181
800384	E1359191
800385	B0110556
800385	E1359191
103137	L0309597	118181
103137	E1458050
103138	B0110556
103138	E1458050
2042225	B0110555
2042225	E1896214
2042227	B0110555
2042227	E1896215
2055249	B0110555
2055249	E292177
2055250	B0110556
2055250	E1900966
2055251	E1900966
2055251	E292177
25908098	E1896215
25908099	E1896214

您可以在column_a和column_b:上使用分层查询和交替匹配

SELECT t.*,
LEVEL,
SYS_CONNECT_BY_PATH(
CASE MOD(LEVEL, 2)
WHEN 1
THEN TO_CHAR(column_a)
ELSE column_b
END,
'->'
) AS path
FROM   table_name t
START WITH column_a = 35108338
CONNECT BY NOCYCLE
(MOD(LEVEL, 2) = 0 AND PRIOR column_b = column_b)
OR         (MOD(LEVEL, 2) = 1 AND PRIOR column_a = column_a)
ORDER SIBLINGS BY column_a, column_b

对于样本数据：

CREATE TABLE table_name (COLUMN_A, COLUMN_B, COLUMN_C) AS
SELECT 35108337, 'B0110555',  NULL FROM DUAL UNION ALL
SELECT 35108337, 'E15627104', NULL FROM DUAL UNION ALL
SELECT 35108338, 'E15627104', NULL FROM DUAL UNION ALL
SELECT 800384,   'L0309599', 18181 FROM DUAL UNION ALL
SELECT 800384,   'E1359191', NULL FROM DUAL UNION ALL
SELECT 800385,   'B0110556', NULL FROM DUAL UNION ALL
SELECT 800385,   'E1359191', NULL FROM DUAL UNION ALL
SELECT 1030137,  'L0309597', 18181 FROM DUAL UNION ALL
SELECT 1030137,  'E1458050', NULL FROM DUAL UNION ALL
SELECT 1030138,  'B0110556', NULL FROM DUAL UNION ALL
SELECT 1030138,  'E1458050', NULL FROM DUAL UNION ALL
SELECT 2042225,  'B0110555', NULL FROM DUAL UNION ALL
SELECT 2042225,  'E1896214', NULL FROM DUAL UNION ALL
SELECT 2042227,  'B0110555', NULL FROM DUAL UNION ALL
SELECT 2042227,  'E1896215', NULL FROM DUAL UNION ALL
SELECT 2055249,  'B0110555', NULL FROM DUAL UNION ALL
SELECT 2055249,  'E292177',  NULL FROM DUAL UNION ALL
SELECT 2055250,  'B0110556', NULL FROM DUAL UNION ALL
SELECT 2055250,  'E1900966', NULL FROM DUAL UNION ALL
SELECT 2055251,  'E1900966', NULL FROM DUAL UNION ALL
SELECT 2055251,  'E292177',  NULL FROM DUAL UNION ALL
SELECT 25908098, 'E1896215', NULL FROM DUAL UNION ALL
SELECT 25908099, 'E1896214', NULL FROM DUAL;

输出：

>><1>>E1896214；35108383->E15627104->3510837->B0110555->2042225->E1896214；35108383->E15627104->3510837->B0110555->2055249->E292177->2055251；35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966>B0110556；35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966->2055250->B0110556<11>>35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966->2055250->B0110556->8003852><10>>35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966->2055250->B0110556E1458050；35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966->2055250->B0110556->1030138<12>>35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966->2055250->B0110556->1030138->E1458050<13>>

COLUMN_A	COLUMN_B	COLUMN_C	LEVEL	PATH
35103838	E15627104	null
3510837	E15627104
3510837	B0110555
2042225	B0110555
2042225	E1896214
25908099
2042227	B0110555
2042227	E1896215
25908098	E1896215
22055249	B0110555
22055249	E292177
22055251	E292177
22055251	E1900966	null
22055250	E1900966	null
22055250	B0110556
800385
800385	E1359191	null
800384	E1359191	null
800384	L0309599
103138	B0110556	null
103137	E1458050	null
103137	L0309597	18181	->35108383->E15627104->3510837->B0110555->2055249->E292177->2055251->E1900966->2055250->B0110556->1030138->E1458050->1030137