오라클 계층형 쿼리

1. 개념

• 부모, 자식 간의 수직관계를 트리 구조 형태로 보여주는 쿼리임
• START WITH: 트리 구조의 최상위 행을 지정함, 시작 조건을 찾음
• CONNECT BY: 부모, 자식의 관계를 지정함, 연결 조건을 찾음
• PRIOR: CONNECT BY절에 사용되어 순방향, 역방향 전개를 결정함
• CONNECT BY PRIOR 자식컬럼 = 부모컬럼 => 부모 → 자식 순방향 전개
• CONNECT BY PRIOR 부모컬럼 = 자식컬럼 => 자식 → 부모 역방향 전개
• ORDER SIBLINGS: 계층형 테이블의 데이터를 계층형으로 정렬함

 

 

2. 사용이유

• 동일한 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 조회하기 위해 사용함. 예를들어, 조직, 사원, 메뉴 등의 데이터에서 사용함
• 이상은 기본 SQL로는 불가능하고 재귀 PL/SQL을 사용해야 가능하나 처리 과정에 많은 시간이 소요되고 변경 과정이 복잡하므로 오라클에서 CONNECT BY 라는 확장된 SELECT 구문을 지원함
•  

 

 

3. 장단점

3.1. 장점

• 재귀 PL/SQL문을 사용하는 것보다 간단하게 계층형 데이터를 조회할 수 있음

3.2. 단점

• 사이클이 발생하면 런타임 오류가 발생함. NOCYCLE 옵션을 추가해야 함

 

 

4. 사용방법

SELECT [ALIAS명]

FROM [테이블명]

WHERE [조건절]

START WITH [조건식]

CONNECT BY [PRIOR, NOCYCLE] 조건식 AND 조건식

ORDER SIBLINGS BY [컬럼명1, 컬럼명2 … ]

 

4.1. 부모 → 자식 순방향 전개

SELECT parant_c p, child_c c, level

FROM tab1

START WITH parenct_c = ‘data1’

CONNECT BY PRIOR child_c = parent_c;

⇒ 첫번째 부모행을 시작으로 자식을 탐색함. 첫번째 부모행의 모든 자식행을 탐색한 후에 두번째 부모행의 자식행을 탐색함

 

4.2. 자식 → 부모 역방향 전개

SELECT parant_c p, child_c c, level

FROM tab1

START WITH parenct_c = ‘data1’

CONNECT BY PRIOR parent_c = child_c ;

 

 

5. 주의사항

• START WITH -> CONNECT BY -> WHERE 순으로 실행됨
• 계층형 구조 테이블에서 LPAD함수를 사용해 레벨별로 들여쓰기를 해서 조회하기도 함 → LPAD(‘ ‘, 2*(LEVEL-1)) …
• CONNECT BY ROOT 컬럼명 → 현재 조회된 행의 최상위 정보를 보여줌
• CONNECT BY ISLEAF 컬럼명 → 현재 행이 마지막 계층의 데이터인지 확인함
• CONNECT BY ISCYCLE → 현재 행의 조상이기도 한 자식이 있는 경우 1을 반환함
• SYS_CONNECT_BY_PATH(컬럼, 구분자) → 루트 노드부터 입력한 행까지의 경로를 컬럼을 기준으로 구분자를 사용해서 보여줌 
• START WITH 은 없어도 되지만, CONNECT BY 절은 필수로 있어야만 쿼리가 실행됨

 

참고 출처

https://hoon93.tistory.com/29 

 

https://tragramming.tistory.com/82