[DataBase]계층형 조회(Connect by)를 사용하는 법을 알아보자!

1. 계층형 조회란?

◎ Oracle Database에서 지원한다.

◎ 계층형으로 데이터를 조회하는 것이다. 

 즉, 초등학교 학년으로 하면 6학년에서 5학년, 5학년에서 4학년 순으로 트리 형태의 구조를 탐색하며 조회하는 것이다.

◎ 역방향도 가능하다

2. 계층형 조회 실습

◎ 테스트 테이블

◎ 계층 level 구하기

  -- MAX(level)을 사용하여 트리구조의 최대 깊이를 구합니다.

  -- START WITH은 시작조건을 의미한다. 즉 mgr이 null인것부터 시작한다는 뜻입니다.

  -- CONNECT BY PRIOR는 조인조건을 의미한다. 즉 empno와 mgr이 같은것을 조인하는 것.

◎ 계층 구조 조회하기

  -- LEVEL을 사용해 계층을 나타내었습니다.

  -- 계층을 조금 더 명확히 보기 위해  LPAD함수를 사용합니다.

  -- LPAD를 사용해 레벨에 따라 띄어쓰기를 하게 해주었습니다.

  -- 이것을 그림으로 나타내자면 이러한 구조입니다.

-- 위에 CONNECT_BY_ISLEAF 는 계층구조에서 가장 최하위(자식이 없는)를 1로 표시해줍니다.

3. CONNECT BY 키워드

◎ CONNECT BY 키워드에는 여러가지가 있습니다.

1. LEVEL

  - 위에 사용했던것 처럼 검색 항목의 깊이를 의미합니다. 최상위 레벨값은 1입니다.

2. CONNECT_BY_ROOT

  - 계층구조에서 가장 최상위 값을 표시합니다.

3. CONNECT_BY_ISLEAF

  - 계층구조에서 가장 최하위를 표시합니다. 최하위이면 1 아니면 0을 나타냅니다.

4. SYS_CONNECT_BY_PATH

  - 계층구조의 전체 전개 경로를 표시합니다.

  - SYS_CONNECT_BY_PATH('컬럼', '컬럼 사이에 표시할 문자') 형태로 사용합니다.

  - 표시를 깔끔하게 하기 위해 SUBSTR으로 2번째부터 보이게 하였고, DECODE를 사용하여 mgr이 null이면 'ROOT'를 나타나게 하였습니다.

5. NOCYCLE

  - 순환구조가 발생지점까지만 전개됩니다.

     - empno 1000번은 최상위인데 mgr를 1001로 바꾸게 된다면 1001의 부모가 1000이 되고 1000의 부모가 1001이 되는 루프가 발생하게 됩니다.

 이러한 오류가 발생하게 되었을 때 순환발생지점까지만 나타나게 하는 것이 NOCYCLE입니다.

6. CONNECT_BY_ISCYCLE

  - 순환구조 발생 지점을 표시합니다. 순환구조가 발생한 곳을 1로 표시합니다.

  - CONNECT_BY_ISCYCLE은 NOCYCLE과 같이 사용합니다.

7. SIBLINGS

  - 계층구조에서 상관관계를 유지하며 정렬을 해줍니다.

  - 기존 ORDER BY를 사용하여 정렬하였을 때는 아래와 같이 관계가 깨집니다.

  

  - ORDER BY 사이에 SIBLINGS를 넣어 정렬을 하게 되면 아래처럼 관계가 유지되며 정렬됩니다.