ORACLE 계층구조 쿼리의 활용

2014.01.02 20:12

계층구조 쿼리의 활용

[2006-04-12] - 김정식 (46,326:Lv60)

[출처] http://www.oracleclub.com/lecture/1903

Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보자

CONNECT_BY_ROOT

계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.

SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ROOT  empno "Root empno", level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
 
ENAME                    EMPNO  Root empno     LEVEL
------------------     ------- -----------   -------
KING                      7839    7839           1
    JONES                 7566    7839           2
        SCOTT             7788    7839           3
            ADAMS         7876    7839           4
        FORD              7902    7839           3
            SMITH         7369    7839           4 

CONNECT_BY_ISLEAF

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하이 레벨이면 1, 아니면 0

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
       CONNECT_BY_ISLEAF "leaf", level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr;
 
 
ENAME                     EMPNO       leaf      LEVEL
-------------------- ---------- ---------- ----------
KING                       7839          0          1
    JONES                  7566          0          2
        SCOTT              7788          0          3
            ADAMS          7876          1          4
        FORD               7902          1          3
    BLAKE                  7698          0          2
        MARTIN             7654          1          3
        TURNER             7844          1          3
        JAMES              7900          1          3
    CLARK                  7782          0          2
        MILLER             7934          1          3

SYS_CONNECT_BY_PATH

계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL PATH FORMAT A40 
 
 
-- SYS_CONNECT_BY_PATH 예제
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
       SYS_CONNECT_BY_PATH(ename, '/') "PATH"
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
 
 
ENAME                     EMPNO PATH
-------------------- ---------- ---------------------------
KING                       7839 /KING
    JONES                  7566 /KING/JONES
        SCOTT              7788 /KING/JONES/SCOTT
            ADAMS          7876 /KING/JONES/SCOTT/ADAMS
        FORD               7902 /KING/JONES/FORD
    BLAKE                  7698 /KING/BLAKE
        MARTIN             7654 /KING/BLAKE/MARTIN
        TURNER             7844 /KING/BLAKE/TURNER
        JAMES              7900 /KING/BLAKE/JAMES
    CLARK                  7782 /KING/CLARK
        MILLER             7934 /KING/CLARK/MILLER

아래와 같이 Leaf Node만 전체 PATH 정보가 나오도록 작성 할 수 있다

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL PATH FORMAT A40 
 
 
SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path
  FROM emp
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
 
 
    LEVEL PATH
--------- -------------------------
        4 KING,JONES,SCOTT,ADAMS
        3 KING,JONES,FORD
        3 KING,BLAKE,MARTIN
        3 KING,BLAKE,TURNER
        3 KING,BLAKE,JAMES
        3 KING,CLARK,MILLER

ORDER SIBLINGS BY

계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A25 
COL ename2 FORMAT A10
 
 
-- ORDER SIBLINGS BY 예
-- 정렬이 정상적으로 수행된 것을 확인 할 수 있다.
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       ename ename2, empno, level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr
 ORDER SIBLINGS BY ename2;
 
 
ENAME                ENAME2          EMPNO      LEVEL
-------------------- ---------- ---------- ----------
KING                 KING             7839          1
    BLAKE            BLAKE            7698          2
        JAMES        JAMES            7900          3
        MARTIN       MARTIN           7654          3
        TURNER       TURNER           7844          3
    CLARK            CLARK            7782          2
        MILLER       MILLER           7934          3
    JONES            JONES            7566          2
        FORD         FORD             7902          3
        SCOTT        SCOTT            7788          3
            ADAMS    ADAMS            7876          4
 
 
 
 
-- ORDER BY 예
-- 정렬이 이상하게 수행된 것을 확인 할 수 있다.
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       ename ename2, empno, level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr
 ORDER BY ename2;
 
 
ENAME                ENAME2          EMPNO      LEVEL
-------------------- ---------- ---------- ----------
            ADAMS    ADAMS            7876          4
    BLAKE            BLAKE            7698          2
    CLARK            CLARK            7782          2
        FORD         FORD             7902          3
        JAMES        JAMES            7900          3
    JONES            JONES            7566          2
KING                 KING             7839          1
        MARTIN       MARTIN           7654          3
        MILLER       MILLER           7934          3
        SCOTT        SCOTT            7788          3
        TURNER       TURNER           7844          3 

문서에 대하여

- 강좌 URL : http://www.oracleclub.com/lecture/1903
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 오라클클럽의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.

계층구조 쿼리의 활용, 이 게시물을

번호	제목	글쓴이	날짜	조회 수
공지	오라클 기본 샘플 데이터베이스	졸리운_곰	2014.01.02	87059
공지	[SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE	가을의 곰을...	2013.02.10	79290
공지	[G_SQL] Sample Database	가을의 곰을...	2012.05.20	96023
18	TensorFlow Lite 101 - MoblieNet 맛보기	졸리운_곰	2018.05.30	1093
17	Apache MXNet에서 사전 트레이닝된 모델을 사용해 보세요.	졸리운_곰	2018.05.30	997
16	MXNet을 활용한 이미지 분류 앱 개발하기	졸리운_곰	2018.05.30	1430
15	세상에 있는 (거의) 모든 머신러닝 문제 공략법	졸리운_곰	2018.05.30	1121
14	sklearn 내부의 pickle lib 를 통해 모델을 저장하고 다시 로드하여 재사용할 수 있다.	졸리운_곰	2018.05.30	1641
13	텐서플로우 기반 딥러닝 훈련 모델 파일 저장, 로딩 및 재활용	졸리운_곰	2018.05.30	1773
12	TensorFlow 모델을 저장하고 불러오기 (save and restore)	졸리운_곰	2018.05.30	1641
11	텐서플로우(TensorFlow)를 이용해서 글자 생성(Text Generation) 해보기 – Recurrent Neural Networks(RNNs) 예제 – Char-RNN	졸리운_곰	2018.05.13	1125
10	외장형 그래픽카드로 우분투에서 텐서플로우 사용 How to setup an eGPU on Ubuntu for TensorFlow	졸리운_곰	2018.05.09	1429
9	Keras and NLTK 케라스를 이용한 NLTK 자연어처리	졸리운_곰	2018.05.08	1611
8	Windows7에서 "처음 심층 학습 프로그램」을 사경 보면 (1-2) 제 1 장 후반	졸리운_곰	2018.05.08	1143
7	CSLAIER CSLAIER에 의한 LSTM	졸리운_곰	2018.05.08	1668
6	Tensorboard 사용하기 1	졸리운_곰	2018.05.08	1419
5	텐서보드 사용법	졸리운_곰	2018.05.08	1416
4	Ubuntu 18.04 Settings for TensorFlow 설치	졸리운_곰	2018.05.08	1186
3	딥러닝용 서버 설치기	졸리운_곰	2018.05.07	1399
2	Installing Tensorflow GPU on Ubuntu 18.04 LTS	졸리운_곰	2018.05.06	1217
1	TensorFlow Lite 101 - MoblieNet 맛보기	졸리운_곰	2018.04.07	1276

첫 페이지 1 2 3 4 5 6 7 8 끝 페이지

쓰기

태그