오라클 계층형 쿼리를 이용한 조직도 (START WITH, CONNECT BY, Treant)

3줄 요약

한 테이블 내에 PK와 FK를 모두 잡아줌. PK는 자신의 번호, FK는 부모의 PK. 최상위 부모는 FK컬럼이 NULL
START WITH 최상위 포식자 선언, CONNECT BY 자식 = 부모, ORDER SIBLINGS BY + LEVEL 이용해 깊이 출력
Treant 라이브러리를 이용한 VIew 출력.

---

오늘의 핵심 내용은 다음 쿼리 문장이다.

/* LEVEL, START WITH, CONNECT BY 는 순환참조(재귀호출)을 이용한 계층조회에 이용됩니다*/
 
SELECT LEVEL, A.*                             /* LEVEL은 해당 요소의 깊이를 표현합니다 */
    FROM EMPLOYEE A
        
    START WITH UPPER_EMP_NO IS NULL         /* START WITH 뒤에 최상위 포식자를 지정합니다 */
    CONNECT BY PRIOR EMP_NO = UPPER_EMP_NO     /* CONNECT BY PRIOR 자식번호 = 부모번호 */
    
    ORDER SIBLINGS BY UPPER_EMP_NO            /* ORDER SIBLINGS BY 부모번호 */

 

---

1. ERD

순환 참조 구조

테이블은 하나다. 
사번을 나타내는 EMP_NO 컬럼이 PK이고
상사의 사번을 나타내는 upper_EMP_NO 컬럼이 FK이다
즉 상사의 사번을 같은 테이블의 상사의 PK값에서 가져온다는 것이다.
우선 이렇게 같은 테이블 내에서 PK, FK를 잡아두는 것이 계층형 조회를 위한 첫 단추다.

계층형 쿼리는 답변형 게시판, 쇼핑몰의 카테고리 등에서 자주 사용된다.

---

2. 쿼리 실행 결과

하나의 최상위 사장님 아래에 직속 부하 셋이 있고
각각의 사장님 직속 부하 하위에 4명, 1명, 3명의 부하가 있는 구조다.

보기 좋게 나타내면 아래와 같은 구조인 것이다

사장님
    상무님
        부장님
        차장님
        과장님
        대리님
    전무님
        대리님
    이사님
        대리님
        대리님
        대리님

여기서 내게 크게 다가온 포인트는 두 가지이다.

첫번째는 표현되어야 하는 순서대로 결과가 나온다는 것이다.
두번째는 LEVEL 이라는 임시의 컬럼을 추가해줌으로써 아주 간편하게 깊이를 확인할 수 있게 해준다는 것이다.

이것을 순환참조, 재귀호출 이라고 하며 계층적 구조를 나타내야 하는 여러 분야에서
아주 자주 쓰이는 문장이라고 한다. START WITH, CONNECT BY...

---

3. 특정 부서 이하만 출력하기

그렇다면 만약 이사님을 최상위로 해서 이사님의 부서만 출력하고자 하면 어떻게 해야 할까?
이사님을 최상위포식자를 지정하는 문장인 START WITH 뒤에 조건을 통해 지정해줘야 한다.
이사님의 사번은 9번이니까 그 내용을 적용해서 쿼리를 실행하면 다음과 같은 결과가 나온다.

이사님을 최상위 계층으로 설정한 쿼리문과 그 결과

이전 쿼리문에서와 달리 이번엔 이사님이 최상위이기 때문에 LEVEL 1, 
이사님의 직속 부하는 LEVEL 2로 깊이가 표현되고 있음을 알 수 있다.
의도한 결과대로 실행되었다.

---

4. Treant.js 라이브러리 활용

앞서와 같이 출력한 쿼리문을 RestController를 이용해 JSON 타입으로 리턴해주는 API를 설계한 뒤,
View단에서 해당 API에 Ajax로 통신해 결과를 가져온 뒤 그 결과를 Treant 라이브러리를 활용해
트리 구조로 보여주는 것이 목표다.

트리 구조를 나타내는 여러 라이브러리가 있었고, 유료도 있었지만
결국 선택한 건 Treant 였다. 무료이고 간결하고 디자인도 맘에 들었다.

Treant 라이브러리 사용방법을 간략하게 설명하자면 다음과 같다.
1. chart_config 배열 객체를 생성한다.
2. config 객체를 생성한 뒤 chart_config 배열 객체의 첫번째 요소로 push한다.
3. 1인분 인물에 대한 정보를 가진 객체의 정보를 설정한 뒤 chart_config 배열에 push한다.
4. new Treant(chart_config); 를 이용해 최종적으로 그린다.

위 내용에서 까다로운 부분은 3번이다.
1인분의 정보를 어떻게 설정할 것인가.
이는 예시로 들어있는 코드를 확인하며 그에 맞춰 갖고 있는 정보를 다듬어야 한다.

1인분 Object 안에 크게 세 가지 정도의 정보를 담아준다.
하나는 부모가 존재하는지 여부.
둘은 부모가 존재한다면 그 부모가 누구인지 chart_config[index] 또는 해당 객체 내 저장된 이름로 지칭
셋은 각종 개인정보를 담고 있는 객체. 이 객체 안에는 name, title 등등이 들어간다.

그래서 최종적으로 나온 결과는 다음과 같다.

계층형 쿼리와 Treant 라이브러리를 이용한 조직도

---

5. 소감

RestController 를 이용한 API와 Controller를 이용한 view 단의 설계에 대해 이해가 더해졌다.
라이브러리 위대하다.. 만든 걸 가져다 쓰는 걸 넘어서서 남에게 만들어주는 개발자가 되고 싶다.
계층형 쿼리와 오라클 정말 대단하다.