Oracle Connect By Prior 계층형 쿼리 간단설명

Oracle에서 Start With, Connect By Prior 구문으로 계층형 쿼리를 간단하게 만들 수 있습니다

계층형 쿼리가 뭐냐??

트리 형태의 구조를 쿼리 즉 Oracle SQL로 나타낸거죠

일단 계층형 쿼리를 사용할 수 있는 간단한 테이블을 만들어줍니다

이런 형태의 테이블을 만들거고 

Material에는 자신의 이름, Parent_mat에는 부모 노드 즉 상위 명칭이 들어갑니다 

CREATE TABLE TREE_TAB_TMP (
    MATERIAL            VARCHAR2(10) PRIMARY KEY,
    PARENT_MAT       VARCHAR2(10)
);

INSERT INTO TREE_TAB_TMP VALUES ('M1',NULL);
INSERT INTO TREE_TAB_TMP VALUES ('M2','M1');
INSERT INTO TREE_TAB_TMP VALUES ('M3','M1');
INSERT INTO TREE_TAB_TMP VALUES ('M4','M2');
INSERT INTO TREE_TAB_TMP VALUES ('M5','M2');

COMMIT;

쿼리는 이렇게 되겠죠 Commit도 잊지 말고 해주시고

어차피 계층형 쿼리만 설명할거라 자잘한건 다 뗐습니다

더하기 빼기 설명하는데 굳이 복소수 실수 나올 필요 없잖아요

여튼 이렇게 만든 테이블을 계층형 쿼리로 본격적으로 Oracle에서 조회해봅시다

SELECT MATERIAL, PARENT_MAT,LEVEL
FROM TREE_TAB_TMP
START WITH PARENT_MAT IS NULL
CONNECT BY PRIOR MATERIAL = PARENT_MAT
;

여기서 START WITH에 들어가는건 최상위 노드를 찾아주는겁니다

당연히 이 테이블에서는 PARENT_MAT 즉 상위노드가 NULL인게 최상위 노드겠죠

여기서 시작한다는 뜻으로 START WITH 가 들어갑니다

CONNECT BY PRIOR 절에서는 이어주는 요소를 만들어줍니다

단, 순서가 중요합니다 상위 노드의 컬럼 = 하위 노드의 컬럼 으로 들어가야지 반대로 넣으면 최상위 노드만 나오게 됩니다

출력해주면 이렇게 나옵니다

여기서 들여쓰기로 좀 더 트리형태로 보이고싶다면 LPAD로 왼쪽에 공백을 레벨 수만큼 추가해줄 수 있죠

SELECT LPAD(' ', LEVEL)||MATERIAL, PARENT_MAT,LEVEL
FROM TREE_TAB_TMP
START WITH PARENT_MAT IS NULL
CONNECT BY PRIOR MATERIAL = PARENT_MAT
;

이렇게 출력되는 포맷을 약간 수정해주면

이렇게 성공적인 Oracle 계층형 쿼리가 완성됩니다

내부에서 계층형 쿼리의 자식노드간에 순서를 설정해줄 수 있는 Order Siblings By 라는 구문도 넣어줄 수 있는데

SELECT LPAD(' ', LEVEL)||MATERIAL, PARENT_MAT,LEVEL
FROM TREE_TAB_TMP
START WITH PARENT_MAT IS NULL
CONNECT BY PRIOR MATERIAL = PARENT_MAT
ORDER SIBLINGS BY MATERIAL DESC
;

어센딩으로 정렬하면 어차피 같은 결과라 DESC로 정렬했습니다

이렇게 하면

계층형쿼리에서 같은 레벨의 자식 노드간에 MATERIAL이 DESC로 정렬된걸 볼 수 있습니다