[oracle]계층형 쿼리 예제

오라클 계층형 쿼리 예제

계층형 테이블 생성

CREATE TABLE DEPT (
	ID NUMBER ,
	PARENT_ID  NUMBER,
	NAME VARCHAR2(50)
);

최대한 간단하게 테이블 생성

데이터 입력

INSERT ALL
INTO DEPT(ID,PARENT_ID,NAME)VALUES(1,3,'디자인팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(2,3,'기획팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(3,6,'IT팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(4,3,'개발팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(5,6,'경영지원팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(6,0,'부서')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(7,6,'회계지원팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(8,7,'회계1')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(9,7,'회계2')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(10,6,'전략기획팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(11,10,'전략팀')
INTO DEPT(ID,PARENT_ID,NAME)VALUES(12,10,'기획팀')
SELECT 1 FROM DUAL;

조회

SELECT LEVEL, LPAD('	', 4 * (LEVEL -1)) || NAME AS NAME
FROM DEPT 
START WITH PARENT_ID = 0
CONNECT BY PRIOR ID = PARENT_ID;

PARENT_ID 값 위치로부터 뽑아낼수 있다.