[Oracle Database] 계층적 질의
오라클에서는 계층적인 데이터를 저장한 컬럼으로부터 데이터를 검색하여 계층적으로 출력할 수 있는 기능을 제공한다. SELECT 문에서 START WITH와 CONNECT BY 절을 이용하여 데이터를 계층적인 형태로 출력할 수 있다.
- START WITH: 절 계층적인 출력 형식을 표현하기 위한 최상위 행
- CONNECT BY: 절 계층관계의 데이터를 지정하는 컬럼
- PRIOR 연산자: CONNECT BY는 PRIOR 연산자와 함께 사용하여 부모 행을 확인할 수 있다. PRIOR 연산자의 위치에 따라 top-down 방식인지 bottom-up 방식인지를 결정한다. PRIOR 연산자가 붙은 쪽의 컬럼이 부모 행이 된다.
- WHERE 절: where 절이 JOIN을 포함하고 있을 경우 CONNECT BY 절을 처리하기 전에 JOIN 조건부를 적용하여 처리하고, JOIN을 포함하고 있지 않을 경우 CONNECT BY 절을 처리한 후에 WHERE 절의 조건을 처리한다.
- LEVEL: 계층적 질의문에서 검색된 결과에 대해 계층별로 레벨 번호 표시, 루트 노드는 1, 하위 레벨로 갈수록 1씩 증가
SELECT
emp 테이블에서 매니저 사번 확인
SELECT empno, ename, mgr FROM emp;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
...
7839 KING
14개의 행이 선택됨
emp테이블의 모든 직원은 매니저가 있음을 알 수 있다.
LEVEL을 이용하여 데이터 확인
SELECT LEVEL, 컬럼1, 컬럼2...
FROM 테이블
WHERE 조건
START WITH 계층의 시작점이 될 행을 구별하는 논리식
CONNECT BY 계층을 구성할 때 사용될 논리식
SELECT LEVEL, empno, ename, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr;
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
4 7876 ADAMS 7788
3 7902 FORD 7566
...
14개의 행이 선택됨
WHERE절에 앞서 START WITH와 CONNECT BY가 먼저 처리 된다. START WITH는 계층의 중심, 시작점을 의미하며 CONNECT BY는 상위 계층과 하위 계층의 행을 연결하는 규칙이며 생략은 불가능하다. PRIOR는 일종의 연산자이며 계층이 구성되는 방향을 결정한다.
LPAD 함수로 계층 표현
SELECT LPAD(' ', LEVEL*5, ' ')||empno||'_'||ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr;
->
LPAD('',LEVEL*5,'')||EMPNO||'_'||ENAME
------------------------------------------------------
7839_KING
7566_JONES
7788_SCOTT
7876_ADAMS
7902_FORD
7369_SMITH
7698_BLAKE
SELECT LPAD('.', LEVEL*4-4, '....') || empno AS empno, ename
FROM emp
START WITH ename ='ADAMS'
CONNECT BY empno = PRIOR mgr;
->
EMPNO ENAME
-----------------------------
7876 ADAMS
....7788 SCOTT
........7566 JONES
............7839 KING
PRIOR / SYS_CONNECT_BY_PATH
SELECT LPAD(empno, LEVEL*3, ' ') AS empno, ename,
PRIOR ename AS mgrname,
SYS_CONNECT_BY_PATH(ename, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr;
EMPNO ENAME MGRNAME PATH
----------------------------------------------------------------
783 KING /KING
7566 JONES KING /KING/JONES
7788 SCOTT JONES /KING/JONES/SCOTT
7876 ADAMS SCOTT /KING/JONES/SCOTT/ADAMS
7902 FORD JONES /KING/JONES/FORD
7369 SMITH FORD /KING/JONES/FORD/SMITH
7698 BLAKE KING /KING/BLAKE
7499 ALLEN BLAKE /KING/BLAKE/ALLEN
7521 WARD BLAKE /KING/BLAKE/WARD
7654 MARTIN BLAKE /KING/BLAKE/MARTIN
7844 TURNER BLAKE /KING/BLAKE/TURNER
7900 JAMES BLAKE /KING/BLAKE/JAMES
7782 CLARK KING /KING/CLARK
7934 MILLER CLARK /KING/CLARK/MILLER
반환되는 레코드가 많을 경우엔 PRIOR를 통해 출력한다.
SYS_CONNECT_BY_PATH(A, B): 연결된 계층의 위치를 확인할 때 사용
ORDER SIBLINGS BY를 이용한 계층간 정렬
SELECT LPAD(empno, LEVEL*4, ' ') AS empno, ename, sal,
PRIOR ename AS mgrname,
SYS_CONNECT_BY_PATH(ename, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr
ORDER SIBLINGS BY sal
위를 변형하여 답변형 게시판이나 부서관계도를 나타낼 때 사용한다.
SELECT 매니저사번, 매니저이름, 매니저근무지역,
매니저급여등급, 자신사번, 자신이름, 자신근무지역, 자신급여등급,
DECODE (f, 0, LEAD (자신사번) OVER (ORDER BY rnum)) AS 부사수사번,
DECODE (f, 0, LEAD (자신이름) OVER (ORDER BY rnum)) AS 부사수이름,
DECODE (f, 0, LEAD (자신근무지역) OVER (ORDER BY rnum)) AS 부사수근무지역,
DECODE (f, 0, LEAD (자신급여등급) OVER (ORDER BY rnum)) AS 부사수급여등급
FROM (
SELECT
PRIOR E.empno AS 매니저사번,
PRIOR E.ename AS 매니저이름,
PRIOR d.loc AS 매니저근무지역,
PRIOR s.grade AS 매니저급여등급,
E.empno AS 자신사번,
E.ename AS 자신이름,
d.loc AS 자신근무지역,
s.grade AS 자신급여등급,
CONNECT_BY_ISLEAF f,
ROWNUM rnum
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno(+)
AND e.sal BETWEEN s.losal AND s.hisal
START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno = e.mgr
ORDER SIBLINGS BY e.empno
)
ORDER BY 자신사번
아래는 잘못된 정렬방법:
SELECT LPAD(empno, LEVEL*3, ' ') AS empno, ename,
PRIOR ename AS mgrname,
SYS_CONNECT_BY_PATH(ename, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr
ORDER BY sal
DELETE
DELETE FROM 테이블
WHERE 컬럼a IN (
SELECT 컬럼A
FROM 테이블
START WITH 컬럼a = 삭제할번호
CONNECT BY PRIOR 컬럼a = 부모컬럼
)
DELETE FROM board
WHERE boardNum IN(
SELECT boardNum -- 코드
FROM board
START WITH boardNum = 삭제할번호 -- 지워야 할 1차 코드
CONNECT BY PRIOR boardNum = parent -- 부모 코드
)
example
CREATE TABLE exam (
num NUMBER PRIMARY KEY,
dname VARCHAR2(50) NOT NULL,
loc VARCHAR2(50),
parent NUMBER
);
INSERT INTO exam VALUES (10, '공과대학', NULL, NULL);
INSERT INTO exam VALUES (100, '정보미디어학부', NULL, 10);
INSERT INTO exam VALUES (200, '메카트로닉스학부', NULL, 10);
INSERT INTO exam VALUES (101, '컴퓨터공학과', '1호관', 100);
INSERT INTO exam VALUES (102, '멀티미디어학과', '2호관', 100);
INSERT INTO exam VALUES (201, '전자공학과', '3호관', 200);
INSERT INTO exam VALUES (202, '기계공학과', '4호관', 200);
COMMIT;
SELECT num, dname, loc, LEVEL, parent FROM exam
START WITH num=10 -- 출력 시작할 최상위 행
CONNECT BY PRIOR num=parent; -- 계층관계지정
-- (PRIOR 연산자가 붙은 쪽이 부모)
SELECT num, dname, loc, level, parent from exam
START WITH num=10 CONNECT by prior num=parent
SELECT LPAD(' ', (LEVEL-1)*4) || dname AS 조직도 FROM exam
START WITH num=10 CONNECT BY PRIOR num=parent;
SELECT num, dname, loc, LEVEL, parent FROM exam
START WITH num=102 CONNECT BY PRIOR parent=num;
-- 정보미디어 학부만 출력 하지 않음
SELECT num, dname, loc, LEVEL, parent FROM exam WHERE num != 100
START WITH num=10 CONNECT BY PRIOR num=parent;
-- 정보미디어학부 및 정보미디어학부의 과도 출력하지 않음
SELECT num, dname, loc, LEVEL, parent FROM exam
START WITH num=10 CONNECT by PRIOR num=parent AND num != 100;