[ORACLE] START WITH와 CONNECT BY 설명 및 예제

이번 시간에는 오라클 계층구조 쿼리를 보여주는 START WITH와 CONNECT BY에 대해서 알아보겠습니다.

용어 설명

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 SIBLINGS BY
- ORDER SIBLINGS BY절을 사용하면 계층구조 결과 쿼리에 대한 정리가 가능합니다.

CONNECT BY의 실행순서는 다음과 같다.
1. START WITH
2. CONNECT BY
3. WHERE

 

 

사전 준비

--테이블 생성
CREATE TABLE EMP
(
    EMPNO NUMBER
    ,ENAME VARCHAR2(50)
    ,SAL   FLOAT
    ,MGR   NUMBER
)

--데이터 삽입
INSERT INTO EMP VALUES (100,'A',999,'');
INSERT INTO EMP VALUES (400,'B',666,'300');
INSERT INTO EMP VALUES (300,'C',777,'200');
INSERT INTO EMP VALUES (500,'D',555,'200');
INSERT INTO EMP VALUES (200,'E',888,'100');
INSERT INTO EMP VALUES (600,'F',666,'400');
COMMIT;

--데이터 조회
SELECT * FROM EMP

 

예제 쿼리

정방향

--CONNECT BY PRIOR 자식컬럼 = 부모컬럼 (부모에서 자식으로 트리구성 (Top Down)) #1
SELECT LEVEL, EMPNO, ENAME,MGR FROM EMP
START WITH EMPNO='100'
CONNECT BY PRIOR EMPNO=MGR;

--EMPNO가 100인 기준으로 조회
--A의 관리자는 없다.
--E의 관지나는 A이다 그리고 C의 관리자는 E이다.
LEVEL EMPNO ENAME MGR
1     100     A
2     200     E     100
3     300     C     200
4     400     B     300
5     600     F     400
3     500     D     200

--CONNECT BY PRIOR 자식컬럼 = 부모컬럼 (부모에서 자식으로 트리구성 (Top Down)) #2
SELECT LEVEL, LPAD(' ', 5*(LEVEL-1)) || EMPNO, ENAME,MGR FROM EMP
START WITH EMPNO='200'
CONNECT BY PRIOR EMPNO=MGR;

--LEVEL을 계층으로 표시
--EMPNO가 200인 기준으로 조회했기 때문에 100번이 결과에 존재하지 않는다.
--E의 관지나는 A이다 그리고 C의 관리자는 E이다.
LEVEL	LPAD('',5*(LEVEL-1))||EMPNO	ENAME	MGR
1	    200	                        E       100
2	         300	                C       200
3	              400               B       300
4	                   600          F       400
2	         500	                D       200

 

 

역방향

-- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 (자식에서 부모로 트리 구성 (Bottom Up)) #1
SELECT LEVEL, EMPNO, ENAME,MGR FROM EMP
START WITH EMPNO='600'
CONNECT BY PRIOR MGR=EMPNO;

--EMPNO가 600인 기준으로 조회
--F는 B의 부하직원이고 B는 C의 부하직원이다.
LEVEL	EMPNO	ENAME	MGR
1       600     F       400
2       400     B       300
3       300     C       200
4       200     E       100
5       100     A       

-- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 (자식에서 부모로 트리 구성 (Bottom Up)) #2
SELECT LEVEL, EMPNO, ENAME,MGR FROM EMP
START WITH EMPNO='300'
CONNECT BY PRIOR MGR=EMPNO;

--MGR가 300인 기준으로 조회
--C는 E의 부하직원이고 E는 A의 부하직원이다.
LEVEL	EMPNO	ENAME	MGR
1       300     C       200
2       200     E       100
3       100     A