Oracle10g中层次查询简介(二)

select connect_by_root child,sys_connect_by_path(child,'/') path

from hier

start with parent is null

connect by prior child = parent;

CONNECT_BY_ROOT PATH

------------------------------ -------- 

Asia /Asia

Asia /Asia/China

Asia /Asia/China/Beijing

Asia /Asia/Japan

Asia /Asia/Japan/Osaka

Asia /Asia/Japan/Tokyo

Australia /Australia

Australia /Australia/New South Wales

Australia /Australia/New South Wales/Sydney

Europe /Europe

Europe /Europe/United Kingdom

Europe /Europe/United Kingdom/England

Europe /Europe/United Kingdom/England/London

North America /North America

North America /North America/Canada

North America /North America/Canada/Ontario

North America /North America/Canada/Ontario/Ottawa

North America /North America/Canada/Ontario/Toronto

North America /North America/USA

North America /North America/USA/California

North America /North America/USA/California/Redwood Shores 

create table hier2

(

parent number,

child number

);

insert into hier2 values(null,1);

insert into hier2 values(1,2);

insert into hier2 values(2,3);

insert into hier2 values(3,1);

select connect_by_iscycle,sys_connect_by_path(child,'/') path

from hier2

start with parent is null

connect by nocycle prior child = parent;

CONNECT_BY_ISCYCLE PATH

------------------ -------

0 /1

0 /1/2

1 /1/2/3