18.계층형 질의

SELECT 컬럼 FROM 테이블 START WITH 시작조건 -- ① 어디서 출발할까 (루트) CONNECT BY PRIOR 부모 = 자식; -- ② 어떻게 연결할까 (전개 규칙)

-- ============================================================ -- 【이해용 예제】 사장(KING)부터 조직 전체를 전개 ★ -- ============================================================ SELECT EMPNO, ENAME, MGR, LEVEL FROM EMP START WITH MGR IS NULL -- ① 관리자가 없는 사람(KING)부터 시작 CONNECT BY PRIOR EMPNO = MGR; -- ② 이전 행의 EMPNO 가 현재 행의 MGR 이면 연결 -- EMPNO ENAME MGR LEVEL -- ----- ------ ----- ----- -- 7839 KING (NULL) 1 ← 루트 (LEVEL 1) -- 7566 JONES 7839 2 ← KING 의 부하 -- 7788 SCOTT 7566 3 ← JONES 의 부하 -- 7876 ADAMS 7788 4 ← SCOTT 의 부하 -- 7902 FORD 7566 3 -- 7369 SMITH 7902 4 -- 7698 BLAKE 7839 2 -- ... → 14건 (전 사원이 계층에 배치됨) -- -- ⭐ START WITH MGR IS NULL → KING 이 루트가 된다 (05강: 최상위는 MGR이 NULL) -- ⭐ CONNECT BY PRIOR EMPNO = MGR → "이전 행(부모)의 사번 = 현재 행의 관리자번호"

-- ============================================================ -- 【시험용 예제】 순방향 : 위에서 아래로 (사장 → 부하) ★★ -- ============================================================ SELECT ENAME, LEVEL FROM EMP START WITH ENAME = 'JONES' -- JONES 에서 시작 CONNECT BY PRIOR EMPNO = MGR; -- PRIOR가 EMPNO 쪽 → 아래로 전개 -- ENAME LEVEL -- ------ ----- -- JONES 1 ← JONES 부터 -- SCOTT 2 ← JONES 의 부하 -- ADAMS 3 ← SCOTT 의 부하 -- FORD 2 ← JONES 의 부하 -- SMITH 3 ← FORD 의 부하 -- → JONES 와 그 아래 모든 부하 -- ============================================================ -- 【시험용 예제】 역방향 : 아래에서 위로 (부하 → 상사) ★★ -- ============================================================ SELECT ENAME, LEVEL FROM EMP START WITH ENAME = 'SMITH' -- SMITH 에서 시작 CONNECT BY PRIOR MGR = EMPNO; -- PRIOR가 MGR 쪽 → 위로 전개 -- ENAME LEVEL -- ------ ----- -- SMITH 1 ← SMITH 부터 -- FORD 2 ← SMITH 의 상사 -- JONES 3 ← FORD 의 상사 -- KING 4 ← JONES 의 상사 (최상위) -- → SMITH 의 상사 라인 전체 -- -- ⭐ 핵심: PRIOR 를 EMPNO 쪽에 두면 아래로, MGR 쪽에 두면 위로 전개된다. -- START WITH 만 바꾸는 게 아니라 PRIOR 위치도 방향에 맞게 바꿔야 한다.

-- ============================================================ -- 【이해용 예제】 LEVEL 로 조직도를 계단식으로 그리기 ★★ -- LPAD 로 LEVEL 만큼 공백을 넣어 들여쓰기 (09강 회수) -- ============================================================ SELECT LEVEL, LPAD(' ', (LEVEL-1) * 3) || ENAME AS 조직도 -- (LEVEL-1)*3 칸 들여쓰기 FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR; -- LEVEL 조직도 -- ----- --------------- -- 1 KING ← LEVEL 1 : 들여쓰기 없음 -- 2 JONES ← LEVEL 2 : 3칸 -- 3 SCOTT ← LEVEL 3 : 6칸 -- 4 ADAMS ← LEVEL 4 : 9칸 -- 3 FORD -- 4 SMITH -- 2 BLAKE -- 3 ALLEN -- 2 CLARK -- 3 MILLER -- -- ⭐ LEVEL 은 루트가 1, 한 단계 내려갈 때마다 1씩 증가한다. -- ⭐ LPAD 로 (LEVEL-1)*n 칸을 들여쓰면 조직도가 한눈에 보인다.

-- 2단계 관리자(부서장)만 조회 SELECT ENAME, LEVEL FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR WHERE LEVEL = 2; -- 전개 후 LEVEL 이 2인 행만 -- → JONES, BLAKE, CLARK (KING 의 직속 부하)

-- ============================================================ -- 【시험용 예제】 전용 요소 한 번에 보기 ★ -- ============================================================ SELECT ENAME, LEVEL, CONNECT_BY_ROOT ENAME AS 최상위, -- 이 계층의 루트 CONNECT_BY_ISLEAF AS 말단여부, -- 1이면 부하가 없는 말단 SYS_CONNECT_BY_PATH(ENAME,'/') AS 경로 -- 루트부터의 경로 FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR; -- ENAME LEVEL 최상위 말단여부 경로 -- ------ ----- ------ -------- -------------------- -- KING 1 KING 0 /KING -- JONES 2 KING 0 /KING/JONES -- SCOTT 3 KING 0 /KING/JONES/SCOTT -- ADAMS 4 KING 1 /KING/JONES/SCOTT/ADAMS ← 말단(부하 없음) -- ... -- -- ⭐ CONNECT_BY_ISLEAF = 1 인 사원은 '부하가 없는 최말단' 이다. -- ⭐ SYS_CONNECT_BY_PATH 로 "누구 밑의 누구의 누구" 경로를 문자열로 얻는다.

-- 말단 사원(부하가 없는 사원)만 조회 SELECT ENAME FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR WHERE CONNECT_BY_ISLEAF = 1; -- 말단만 -- → ADAMS, SMITH, ALLEN, WARD, MILLER, ... (부하가 없는 사원들)

-- ============================================================ -- 【시험용 예제】 계층을 깨지 않고 형제끼리만 정렬 ★★ -- ============================================================ -- ❌ 일반 ORDER BY 는 계층 구조를 무너뜨린다 SELECT LPAD(' ',(LEVEL-1)*3) || ENAME AS 조직도 FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR ORDER BY ENAME; -- 전체를 이름순으로 → 계층이 사라짐! ⚠️ -- ✅ ORDER SIBLINGS BY : 같은 부모를 둔 '형제' 끼리만 정렬 (계층 유지) SELECT LPAD(' ',(LEVEL-1)*3) || ENAME AS 조직도 FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR ORDER SIBLINGS BY ENAME; -- 계층은 유지하면서 형제만 이름순 -- 조직도 -- --------------- -- KING -- BLAKE ← KING 의 부하들(JONES/BLAKE/CLARK)을 이름순 정렬 -- ALLEN -- WARD -- CLARK -- MILLER -- JONES -- FORD -- SCOTT -- -- ⭐ 일반 ORDER BY 는 계층을 무시하고 전체를 정렬해 조직도가 깨진다. -- ORDER SIBLINGS BY 는 계층 구조를 유지한 채 형제 노드만 정렬한다.