CONNECT BY의 신기한 기능들...

=== Oracle Level 구조===

SELECT

PARENT_KEY, CHILD_KEY , LEVEL LV

FROM TEST

START WITH PARENT_KEY='ROOT'

CONNECT BY PRIOR CHILD_KEY = PARENT_KEY

=== Oracle Level 구조 정렬===

SELECT

PARENT_KEY, CHILD_KEY , LEVEL LV

FROM TEST

START WITH PARENT_KEY='ROOT'

CONNECT BY PRIOR CHILD_KEY = PARENT_KEY

ORDER SIBLINGS BY VIEW_NO

=== Oracle Level 구조(ROOT KEY 구하기)===

SELECT

PARENT_KEY, CHILD_KEY,

CONNECT_BY_ROOT PARENT_KEY ROOT_KEY

FROM TEST

START WITH PARENT_KEY='ROOT'

CONNECT BY PRIOR CHILD_KEY = PARENT_KEY

=== Oracle Level 구조(서브레벨여부 구하기)===

SELECT

PARENT_KEY, CHILD_KEY,

CONNECT_BY_ISLEAF ISLEAF

FROM TEST

START WITH PARENT_KEY='ROOT'

CONNECT BY PRIOR CHILD_KEY = PARENT_KEY

=== Oracle Level 구조(PATH 구하기)===

SELECT

PARENT_KEY, CHILD_KEY,

SYS_CONNECT_BY_PATH(CHILD_NAME, '/') PH

FROM TEST

START WITH PARENT_KEY='ROOT'

CONNECT BY PRIOR CHILD_KEY = PARENT_KEY

[출처] CONNECT BY의 신기한 것들... 오라클 정말 대단.....|작성자 시골사람