oracle 遍历输出,oracle 数型遍历测试与介绍

CREATE TABLE treeTEST(master VARCHAR2(10),SUB VARCHAR2(10),SCALE NUMBER);

insert into treeTEST values 　('1', 　　 '2', 　 15);

insert into treeTEST values　　('1', 　　 '3', 　 20);

insert into treeTEST values 　　('2', 　　 '4', 　　5);

insert into treeTEST values 　　('2', 　　 '5', 　 10);

insert into treeTEST values 　　('3', 　　 '5', 　 30);

insert into treeTEST values 　　('3', 　　 '6', 　 40);

COMMIT;

SELECT * FROM TREETEST

MASTER SUB SCALE

1 2 15

1 3 20

2 4 5

2 5 10

3 5 30

3 6 40

如果用树型结构表示如下:

'1'

-'2'

--'4'

--'5'

'1'

-'3'

--'5'

--'6'

select *

from treeTEST　start with sub = '2' 　　　　　 --相当于普通sql的where条件

connect by prior master = sub;

MASTER SUB SCALE

1 2 15

select *

from treeTEST　start with sub = '2' 　　　　　 --相当于普通sql的where条件

connect by sub =prior master;

MASTER SUB SALES

1 2 15

select *

from treeTEST　start with sub = '6' 　　　　　 --相当于普通sql的where条件

connect by sub =prior master;

MASTER SUB SALES

3 6 40

1 3 20

select *

from treeTEST　start with sub = '5' 　　　　　 --相当于普通sql的where条件

connect by sub =prior master;

MASTER SUB SCALE

2 5 10

1 2 15

3 5 30

1 3 20

select * from treeTEST

start with master='2'

connect by prior master=sub; --sub往上遍历至根节点

MASTER SUB SCALE

2 4 5

1 2 15

2 5 10

1 2 15

select sys_connect_by_path(MASTER,'->') from treeTEST　 以->为分割符

start with master='2' --master表示我遍历的起点为master=2

connect by prior master=sub;

SYS_CONNECT_BY_PATH(MASTER,'->

->2

->2->1

->2

->2->1

select sub,master,sys_connect_by_path(MASTER,'->') from treetest

start with sub='5' --起点为sub='5'，向根便利

connect by prior master=sub;

SUB MASTER SYS_CONNECT_BY_PATH(MASTER,'->

5 2 ->2

2 1 ->2->1

5 3 ->3

3 1 ->3->1

从sub开始向主遍历：

select sub, master, sys_connect_by_path(MASTER, '->')

from treetest　　 　　start with sub is not null 　　--遍历的起点

connect by prior master = sub;

SUB MASTER SYS_CONNECT_BY_PATH(MASTER,'->

2 1 ->1

3 1 ->1

4 2 ->2

2 1 ->2->1

5 2 ->2

2 1 ->2->1

5 3 ->3

3 1 ->3->1

6 3 ->3

3 1 ->3->1

从主开始向sub遍历：

select master, sub, sys_connect_by_path(sub, '->')

from treetest　　 　　start with master is not null 　　--遍历的起点

connect by prior sub = master;

MASTER SUB SYS_CONNECT_BY_PATH(SUB,'->')

1 2 ->2

2 4 ->2->4

2 5 ->2->5

1 3 ->3

3 5 ->3->5

3 6 ->3->6

2 4 ->4

2 5 ->5

3 5 ->5

3 6 ->6[@more@]