학습목표
1. 계층적 질의를 살펴보고 사용할 수 있다.
- 계층적 질의(Hierarchial Query)란?
- 테이블의 행 사이의 계층적 관련성을 바탕으로 데이터를 검색하는 질의
- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다.
- 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
- 형식
SELECT [LEVEL], COLUMN1, COLUMN2...
FROM TABLE ---> 무조건 1개의 테이블만 사용 가능(조인 사용 불가)
WHERE CONDITIONS
[START WITH condition] ---> LEVEL의 의사열을 가진다. ROOT의 계층 시작점을 지정 (서브쿼리 사용 가능)
[CONNECT BY PRIOR condition] ---> 계층의 상 하위에 대한 LEVEL을 관리한다. PRIOR 바로 다음에 오는 행부터 검색되며, 서브쿼리를 사용할 수 없다.- 방법
- START WITH 절을 이용한 시작점 지정
- ex) START WITH COLUMN1 = VALUE " 시작 행의 위치를 지정할 수 있다.
- 만일 명시하지 않으면 테이블의 모든 ROOT가 NODE 1(LEVEL 1)이 된다.
- CONNECT BY절을 사용한 트리 진행 방향 절정
- LEVEL 의사열의 활용
- PRIOR키워드의 활용
- WHERE 절 조건을 이용한 데이터 방법
- CONNECT BY 절을 이용한 데이터 제거
- 계층적 질의는 START WITH 와 CONNECT BY에 존재 여부에 따라서 식별된다.
- START WITH 절을 이용한 시작점 지정
- 사용 시점
- 관계형 데이터베이스는 레코드를 계층적인 방법으로 저장하지 않는다.
- 그러나, 계층적 관련성이 단일 테이블의 행 사이에 존재하며 계층적 질의는 하나의 테이블에 있는 행들 사이에 어떤 관련성이 존재할 때 가능하다.
- ex) 매니저 직책에 있는 사원이 킹에게 보고할 때 사용
- ANSI-SQL에서는 계층관계를 표현하는 Hierarchy Query를 사용하는 것은 불가능하다.
- 확장된 형태의 recursive PL/SQL이나 CONNECT BY를 이용하여 Hierarchial Query(계층쿼리) 표현이 가능하다.
- 오라클에서는 CONNECT BY라는 확장된 구문을 통해 계층쿼리를 지원한다. 조직구조, (답글게시판), (디렉토리구조) 등에 주로 이용된다.
EX) CONNECT BY PRIOR 탑 키 = 하위 키 : TOP DOWN 방향
CONNECT BY PRIOR 하위 키 = 탑 키 : BOTTOM UP 방향 - 상위를 부모, 하위를 자식으로 연결시켜서 열 위치에 의해 의사결정을 사용한다.
- 항상 현재 부모 행에 관련된 CONNECT BY 조건에 의해 자식을 선택한다.
-
실습 예제
- Q1. 계층적 쿼리를 이용하여 위에서 아래로 사원의 이름과 관리자 이름을 조회하자. [TOP-DOWN]
<결과>
WALK
---------------------
KING의 상사
JONES의 상사 KING
FORD의 상사 JONES
SMITH의 상사 FORD
BLAKE의 상사 KING
ALLEN의 상사 BLAKE
WARD의 상사 BLAKE
MARTIN의 상사 BLAKE
TURNER의 상사 BLAKE
JAMES의 상사 BLAKE
CLARK의 상사 KING
MILLER의 상사 CLARK
12 rows selected.더보기
SQL> SELECT ENAME || '의 상사 ' || PRIOR ENAME "WALK"
2 FROM EMP
3 START WITH ENAME = 'KING'
4 CONNECT BY PRIOR EMPNO = MGR;- Q2. 'SMITH'부터 시작하여 아래에서 위로 해당 사원의 매니저 정보가 나오도록 출력하자. [BOTTOM-UP]
<결과>
ENAME JOB MGR
-------------------- ------------------ ----------
SMITH CLERK 7902
FORD ANALYST 7566
JONES MANAGER 7839
KING PRESIDENT더보기
SQL> SELECT ENAME, JOB, MGR
2 FROM EMP
3 START WITH ENAME = 'SMITH'
4 CONNECT BY PRIOR MGR = EMPNO;-> LEVEL로 계층구조를 표현하기
KING (LEVEL1: ROOT)
CLARK JONES BLAKE (LEVEL2: PARENT/CHILD)
MILLER FORD ALLEN WARD MARTIN TUNER JAMES (LEVE3: PARENT/CHILD/LEAF)
SMITH
- ROOT : 최상위 레벨
- PARENT/CHILD : 부모이면서, 동시에 자식
- LEAF : 최하위 레벨 (가장 마지막 노드를 의미)
Q3. 가장 높은 레벨을 시작으로, 가장 낮은 레벨의 다음 레벨들을 들여쓰기로 출력하자. [LEVEL]
| 가상 칼럼 | 설명 |
| LEVEL | 루트 데이터이면 1, 그 하위 데이터이면 2이다. 리프(Leaf) 데이터까지 1씩 증가한다. |
| CONNECT_BY_ISLEAF | 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0이다. |
| CONNECT_BY_ISCYCLE | 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용할 수 있다. |
SQL> SELECT LPAD(' ',4*(LEVEL-1)) || ENAME RES, LEVEL, EMPNO, MGR, DEPTNO #(4*LEVEL -4)와 동일
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR;
Q4. 전체 노드 중에서 'CLARK'를 삭제해보자.
더보기
# 풀이 1
SQL> SELECT LPAD(' ',4*LEVEL-4) || ENAME RES, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 WHERE ENAME != 'CLARK'
4 START WITH ENAME = 'KING'
5 CONNECT BY PRIOR EMPNO = MGR;
# 풀이 2
SQL> SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
2 FROM EMP
3 WHERE ENAME != 'CLARK'
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
DEPTNO EMPNO ENAME JOB SAL
---------- ---------- -------------------- ------------------ ----------
10 7839 KING PRESIDENT 5000
20 7566 JONES MANAGER 2975
20 7902 FORD ANALYST 3000
20 7369 SMITH CLERK 800
30 7698 BLAKE MANAGER 2850
30 7499 ALLEN SALESMAN 1600
30 7521 WARD SALESMAN 1250
30 7654 MARTIN SALESMAN 1250
30 7844 TURNER SALESMAN 1500
30 7900 JAMES CLERK 950
10 7934 MILLER CLERK 1300
11 rows selected.
Q5.BLAKE와 그의 (하위) 직원들을 모두 삭제해보자.
<결과>
DEPTNO EMPNO ENAME JOB SAL
---------- ---------- -------------------- ------------------ ----------
10 1
10 7839 KING PRESIDENT 5000
20 7566 JONES MANAGER 2975
20 7902 FORD ANALYST 3000
20 7369 SMITH CLERK 800
10 7782 CLARK MANAGER 2450
10 7934 MILLER CLERK 1300
7 rows selected.더보기
SQL> SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'BLAKE';
SQL> SELECT LPAD('',4*LEVEL-4) || ENAME RES, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'BLAKE';
Q6. LEVEL를 정렬을 해보자.
< 결과 >
RES LEVEL EMPNO MGR DEPTNO
---------- ---------- ---------- ---------- ----------
ALLEN 3 7499 7698 30
BLAKE 2 7698 7839 30
CLARK 2 7782 7839 10
FORD 3 7902 7566 20
JAMES 3 7900 7698 30
JONES 2 7566 7839 20
KING 1 7839 10
MARTIN 3 7654 7698 30
MILLER 3 7934 7782 10
SMITH 4 7369 7902 20
TURNER 3 7844 7698 30
RES LEVEL EMPNO MGR DEPTNO
---------- ---------- ---------- ---------- ----------
WARD 3 7521 7698 30
1 1 10
13 rows selected.더보기
SQL> SELECT LPAD('',4*LEVEL-4) || ENAME RES, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR
5 ORDER BY ENAME;
Q7. 06의 쿼리를 실행하게 되면 TREE 구조로 만들어 정렬하기 때문에, '계층이 틀어진다'라고 본다. TREE 구조를 만들어서 계층을 두고 대상을 정렬을 하려면 ORDER SIBLINGS BY 라는 키워드를 사용한다.
< 결과 >
RES LEVEL EMPNO MGR DEPTNO
---------- ---------- ---------- ---------- ----------
KING 1 7839 10
BLAKE 2 7698 7839 30
ALLEN 3 7499 7698 30
JAMES 3 7900 7698 30
MARTIN 3 7654 7698 30
TURNER 3 7844 7698 30
WARD 3 7521 7698 30
CLARK 2 7782 7839 10
MILLER 3 7934 7782 10
JONES 2 7566 7839 20
FORD 3 7902 7566 20
RES LEVEL EMPNO MGR DEPTNO
---------- ---------- ---------- ---------- ----------
SMITH 4 7369 7902 20
1 1 10더보기
SQL> SELECT LPAD('',4*LEVEL-4) || ENAME RES, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR
5 ORDER SIBLINGS BY ENAME;Q8. SYS_CONNECT_BY_OATH(대상,'구분자')를 이용해서 EMP의 TREE를 출력해보자.
RES JOB RES02
---------- ------------------ --------------------
/
KING PRESIDENT /KING
JONES MANAGER /KING/JONES
FORD ANALYST /KING/JONES/FORD
SMITH CLERK /KING/JONES/FORD/SMI
TH
BLAKE MANAGER /KING/BLAKE
ALLEN SALESMAN /KING/BLAKE/ALLEN
WARD SALESMAN /KING/BLAKE/WARD
MARTIN SALESMAN /KING/BLAKE/MARTIN
RES JOB RES02
---------- ------------------ --------------------
TURNER SALESMAN /KING/BLAKE/TURNER
JAMES CLERK /KING/BLAKE/JAMES
CLARK MANAGER /KING/CLARK
MILLER CLERK /KING/CLARK/MILLER
13 rows selected.더보기
SQL> COL RES02 FORMAT A20; # RES02라는 칼럼의 길이 조정 - 잘 출력하기 위해서
SQL> SELECT LPAD('',4*LEVEL-4) || ENAME RES, JOB,
2 SYS_CONNECT_BY_PATH(ENAME,'/') RES02
3 FROM EMP
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;Q9. CONNECT_BY_ISLEAF : 마지막 노드의 유무를 찾을 수 있다.
- CONNECT_BY_ISLEAF = 0 : 현재 ROW가 자식노드를 가지고 있을 때 LEAF NOTE가 아니다.
- CONNECT_BY_ISLEAF = 1 : 현재 ROW가 자식노드를 가지고 있지 않을 때 LEAF NOTE이다.
RES JOB RES02 CONNECT_BY_ISLEAF
---------- ------------------ -------------------- -----------------
/ 1
KING PRESIDENT /KING 0
JONES MANAGER /KING/JONES 0
FORD ANALYST /KING/JONES/FORD 0
SMITH CLERK /KING/JONES/FORD/SMI 1
TH
BLAKE MANAGER /KING/BLAKE 0
ALLEN SALESMAN /KING/BLAKE/ALLEN 1
WARD SALESMAN /KING/BLAKE/WARD 1
MARTIN SALESMAN /KING/BLAKE/MARTIN 1
RES JOB RES02 CONNECT_BY_ISLEAF
---------- ------------------ -------------------- -----------------
TURNER SALESMAN /KING/BLAKE/TURNER 1
JAMES CLERK /KING/BLAKE/JAMES 1
CLARK MANAGER /KING/CLARK 0
MILLER CLERK /KING/CLARK/MILLER 1
13 rows selected.더보기
SQL> SELECT LPAD('',4*LEVEL-4) || ENAME RES, JOB,
2 SYS_CONNECT_BY_PATH(ENAME,'/') RES02, CONNECT_BY_ISLEAF
3 FROM EMP
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;Q10. 사원테이블의 계층 구조에서 LEAF 노드만 보자.
RES JOB
---------- ------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
JAMES CLERK
MILLER CLERK
8 rows selected.더보기
SQL> SELECT LPAD('',4*LEVEL-4) || ENAME RES, JOB
2 FROM EMP
3 WHERE CONNECT_BY_ISLEAF = 1
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
다음 포스팅에서는 PL/SQL의 기초 및 개념에 대해서 알아보도록 하겠습니다 :)
'학습 과정 공유 및 후기 > SQLD' 카테고리의 다른 글
| [ SQLD ] Oracle SQL (13) - PL/SQL 기초 및 개념 (0) | 2020.05.29 |
|---|---|
| [ SQLD ] Oracle SQL (12) - MERGE, TRANSACTION (0) | 2020.05.29 |
| [ SQLD ] Oracle SQL (10) - View (0) | 2020.05.28 |
| [ SQLD ] 데이터 모델링의 이해 (2) - 데이터 모델의 성능 (0) | 2020.05.28 |
| [ SQLD ] Oracle SQL (9) - JOIN (INNER, OUTER) (0) | 2020.05.28 |
