[Oracle]계층형 쿼리(START WITH ~ CONNECT BY PRIOR)

계층형 쿼리는 테이블 내의 데이터 관계가 계층적 관계를 가질 때 사용할 수 있습니다.

계층적 관계란 [메뉴1] - [메뉴1_서브메뉴1] - [메뉴1_서브메뉴1_서브서브메뉴1] 과 같이 서로 관계가 계층적으로 묶여있는 것을 말합니다. 이를 트리 형태라고도 합니다.

이미지 출처 : 홈텍스

 

위 이미지와 같이 조회/발급, 민원증명, ..., 세무대리인과 같은 대메뉴와 조회/발급 메뉴의 서브 메뉴로 전자세금계산서(서브 메뉴 - 발급,목록조회 등...), 현금영수증, 세금신고납부, 기타 조회 등과 같은 서브메뉴를 계층형 쿼리를 이용해 처리할 수 있습니다.

 

테이블을 하나 만들어 테스트 데이터를 입력했습니다.

홈텍스 사이트의 조회/발급 메뉴와 민원증명 메뉴를 계층 관계로 나타내보려 합니다.

 

SELECT *
FROM TESTTABLE
;

 

이제 이 데이터를 트리 형태(계층 구조)로 만들어 보겠습니다.

 

SELECT PARENTMENU
     , MENUCODE
     , MENUNAME
     , LPAD('ㄴ', 3*(LEVEL-1)) || MENUNAME AS TREEMENUNM
     , MENULVL
     , LEVEL AS TREELVL
 FROM TESTTABLE
START WITH PARENTMENU = 'ROOT' 
CONNECT BY PRIOR MENUCODE = PARENTMENU
;

먼저 MENULVL 은 해당 메뉴의 순번을 의미하며(전자세금계산서 -> 현금영수증 -> 세금신고납부), TREELVL 은 트리 구조에서 해당 계층의 레벨을 의미합니다.

 

START WITH PARENTMENU = 'ROOT' : 계층 관계가 시작되는 루트를 지정하는 구문입니다. 해당 구문은 생략이 가능하지만 생략하게 되면 모든 행을 루트 행으로 간주하고 계층관계를 검색하기 때문에 중복 행이 발생할 가능성이 있습니다. - EX1) 참고

PARENTMENU 필드가 'ROOT'인 행을 루트 행으로 간주하겠다는 의미로 사용되었습니다.

 

CONNECT BY PRIOR MENUCODE = PARENTMENU : 부모-자식 관계를 정의하기 위한 구문입니다.

조건이 여러 개인 경우 AND 조건절을 CONNECT BY 절에 넣으면 해당 조건을 이용해 트리를 구성합니다. - EX2) 참고

 

WHERE 절에 조건을 넣게 되면 전체 트리를 구성한 후 선택하기 때문에 트리 구성시에는 CONNECT BY 절에 조건을 넣는 것이 효과적입니다.

추가로 위 쿼리에는 없지만 ORDER SIBLINGS BY 절을 이용해 계층 쿼리에 정렬을 할 수도 있습니다. - EX3) 참고

 

정리해보면 PARENTMENU 필드가 'ROOT'인 행을 루트로 간주하여, MENUCODE 필드가 PARENTMENU 필드로 존재하는 자식을 찾아 관계를 맺는다는 의미가 됩니다.

 

EX1) 

SELECT PARENTMENU
     , MENUCODE
     , MENUNAME
     , LPAD('ㄴ', 3*(LEVEL-1)) || MENUNAME AS TREEMENUNM
     , MENULVL
     , LEVEL AS TREELVL
 FROM TESTTABLE
--START WITH PARENTMENU = 'ROOT' 
CONNECT BY PRIOR MENUCODE=PARENTMENU
;

 

EX2)

SELECT PARENTMENU
     , MENUCODE
     , MENUNAME
     , LPAD('ㄴ', 3*(LEVEL-1)) || MENUNAME AS TREEMENUNM
     , MENULVL
     , LEVEL AS TREELVL
 FROM TESTTABLE
START WITH PARENTMENU = 'ROOT' 
CONNECT BY PRIOR MENUCODE = PARENTMENU
             AND MENUCODE <> 'A0001_1'
;

 

EX3)

SELECT PARENTMENU
     , MENUCODE
     , MENUNAME
     , LPAD('ㄴ', 3*(LEVEL-1)) || MENUNAME AS TREEMENUNM
     , MENULVL
     , LEVEL AS TREELVL
 FROM TESTTABLE
START WITH PARENTMENU = 'ROOT' 
CONNECT BY PRIOR MENUCODE = PARENTMENU
ORDER SIBLINGS BY MENULVL DESC
;    

---

+피드백은 언제나 환영입니다 :)