[오라클] 계층구조 쿼리(start with. connect by. order siblings by)

일하다가 2010년 초반에 개발해 놓은 프로젝트에 추가 개발을 하게 되었는데.. 

소스가 아주 힘들다.. 여튼 계층구조 쿼리를 사용 하고 있어서.. 완벽히 모르는 바.. 찾아보게 되었다. 

seqtest( https://sqltest.net/)사이트는 사이트는 2020년6월 기준 oracle 11g, mysql5.1, mssql server 2019 를 online로 테스트 해볼수 있게 되어있다.

 

https://sqltest.net/

SQL Test

계층구조 쿼리란?

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태라 함.

ex) 회사에 부서, 학교의 학과등을 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말함 

오라클에서 계층형 쿼리는 start with .. connect by 절로 사용하며 오라클 8부터 지원함

 

계층구조 synctax

START WITH 

- 계층 질의의 부모로 사용될 행을 지정함 

- 서브쿼리를 사용도 가능함 

 

CONNECT BY 

- 상위 계층(부모)과 하위(자식)계층의 관계를 규정함

- PRIOR 연산자와 함께 사용하여 계층구조로 표현함 

- CONNECT BY PRIOR 자식 = 부모 

   부모에서 자식으로 트리구성 (TOP DOWN)

- CONNECT BY PRIOR 부모 = 자식 

   자식에서 부모로 트리구성 (BOTTOM UP)

- CONNECT BY NOCYCLE PRIOR 

   NOCYCLE 파라미터를 이용하여 무한루프 방지 (서브쿼리 사용 안됨)

 

LEVEL Pseudocolumn  

- LEVEL은 계층구조 쿼리에서 수행결과의 depth를 표현하는 컬럼

 

ORDER SIBLNGS BY 

- ORDER SIBLINGS BY 절을 사용하면 계층 구조 쿼리에서 편하게 정렬작업을 할수 있음.

 

CONNECT BY 실행순서

1) STARTY WITH 절 

2) CONNECT BY 절

3) WHERE 절 순서로 풀리게 됨

 

 

 

위같은 데이터를 계층형 쿼리로 예제로 나타내면 아래처럼 사용할 수 있다. 

-- 간단예제
-- level 컬럼으로 dept를 알수 있다.
SELECT     
        LEVEL
        , dept_name
        , dept_no
        , parent_cd
FROM DEP
WHERE START WITH parent_cd is NULL 
CONNECT BY PRIOR dept_no = parent_cd ;
 
-- level 활용
-- level Pseudocolumn을 활용하면 계층구조 쿼리를 좀 더 다양하게 활용 할 수 있다.
-- level 배율만큼 공백을 왼쪽으로 추가해서 표현 한 쿼리 
SELECT 
        LEVEL
        lpad(' ',4*(level-1)) || dept_name as dept_name
        , dept_no
        , parent_cd 
FROM DEP
WHERE START WITH parent_cd is NULL 
CONNECT BY PRIOR dept_no = parent_cd ; 
 
-- buttom up 조회
SELECT 
        LEVEL
        lpad(' ', 4*(level-1)) || dept_name as dept_name
        , dept_no
        , parent_cd 
FROM DEP
WHERE START WITH parent_cd = 106 -- 최하위 노드값
CONNECT BY PRIOR parent_cd = dept_no ;

 

* 참고사이트

http://www.gurubee.net/lecture/1300

계층구조 쿼리(Hierarchical Queries)란?

 

https://coding-factory.tistory.com/461

[Oracle] 오라클 계층형 쿼리(START WITH.. CONNECT BY)