oracle

http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=48 오라클 데이터베이스  scott유저의 emp테이블을 보면 empno와 mgr컬럼이 있습니다. mgr 컬럼의 데이터는 해당 사원의 관리자의 empno를 의미 합니다. 예를 들어서 아래의 데이터를 보면은..   EMPNO ENAME         SAL        MGR ------ --------- ------- ----------    7369     SMITH          800       7902    7902     FORD          3000       7566 empno 7369사원의  관리자는 7902의 empno를 가진 사원이며 empno 7902사원의  관리자는 7566의 empno를 가진 사원입니다. 이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 가져올 수 있습니다. 상품의 카테고리(대분류,중분류,소분류...)를 조회 할때 START WITH와 CONNECT BY를 이용하면 트리 구조로 편리하게 조회 할 수 있습니다. 게시판에서의 일반글과 답변글 과의 관계에서도 사용 할 수 있습니다. ⊙ START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로  조회할 수 있습니다.   ◈ START WITH - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다.. - 서브쿼리를 사용할 수도 있습니다. ◈ CONNECT BY - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다. - 보통 PRIOR 연산자를 많이 사용 합니다.. - 서브쿼리를 사용할 수 없습니다.. ◈ CONNECT BY의 실행순서는 다음과 같습니다. - 첫째 START WITH절 - 둘째 CONNECT BY 절 - 세째 WHERE 절 순서로 풀리게 되어있습니다.   -- 테스트를 위해서 scott유저로 접속을 합니다. SQLPLUS scott/tiger 예제1)상위계층과 하위 계층 관계의 순서대로 쿼리해 옴 -- LEVEL 예약어를 사용하여  depth를 표현 할 수 있습니다. -- 직업이 PRESIDENT을 기준으로 계층 구조로 조회합니다. SQL>SELECT LEVEL,empno,ename, mgr         FROM  emp         START WITH job = ’PRESIDENT’   -- 직업이 PRESIDENT를 기준으로         CONNECT BY PRIOR  empno = mgr; -- 사원(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         4       7369      SMITH                       7902         2       7698      BLAKE                       7839         3       7499      ALLEN                       7698         3       7521      WARD                       7698         3       7654      MARTIN                     7698         3       7844      TURNER                     7698         3       7900      JAMES                       7698         2       7782      CLARK                       7839         3       7934      MILLER                      7782 --  LEVEL컬럼은 depth를 나타냅니다. -- JONES의 관리자는 KING을 나타냅니다. --  SCOTT의 관리자는 JONES를 나타냅니다. --  예제와 같이 상/하의 계층 구조를 쉽게 조회 할 수 있습니다.   예제2) 사원성명을 계층 구조로 보여 줌 SQL>SET LINESIZE 100 SQL>SET PAGESIZE 100 SQL>COL ename FORMAT A20 -- 예제1의 결과를 가지고 LPAD함수를 이용해서 ename왼쪽에 공백을 추가 했습니다. SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job        FROM emp        START WITH job=’PRESIDENT’        CONNECT BY PRIOR empno=mgr; ENAME                     EMPNO        MGR JOB -------------------- ---------- ---------- --------- KING                        7839                   PRESIDENT     JONES                  7566       7839      MANAGER         SCOTT             7788       7566      ANALYST             ADAMS         7876       7788      CLERK         FORD               7902       7566      ANALYST             SMITH          7369       7902      CLERK     BLAKE                  7698       7839      MANAGER         ALLEN               7499       7698     SALESMAN         WARD               7521       7698     SALESMAN         MARTIN             7654       7698     SALESMAN         TURNER            7844       7698     SALESMAN         JAMES              7900       7698     CLERK     CLARK                  7782       7839     MANAGER         MILLER              7934       7782     CLERK 예제3) 레벨이 2까지만 쿼리해서 가져오는 예제    SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job         FROM emp         START WITH job=’PRESIDENT’         CONNECT BY PRIOR empno=mgr         AND LEVEL <=2 ;     ENAME                     EMPNO        MGR JOB -------------------- ---------- ---------- --------- KING                        7839                   PRESIDENT     JONES                  7566       7839      MANAGER     BLAKE                  7698       7839      MANAGER     CLARK                  7782       7839      MANAGER   예제4) 각 label별로 급여의 합과 인원수를 구하는 예제 SQL> SELECT LEVEL, SUM(sal) total,COUNT(empno) cnt         FROM emp         START WITH job=’PRESIDENT’         CONNECT BY PRIOR empno=mgr               GROUP BY LEVEL         ORDER BY LEVEL;      LEVEL      TOTAL        CNT ---------- ---------- ----------          1       5000          1          2       8275          3          3      13850          8          4       1900          2 ◈ 데이터가 많아질 경우.... - 첫째로 풀리는 START WITH job=’PRESIDENT’ job 컬럼에 index가 생성되어 있지 않는다면     속도를 보장할 수 없습니다. - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가    되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.    - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로    표현하기가 어렵 습니다. 아래 강좌에서 START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리를 해오는것을 살펴봤습니다. 이번에는 계층구조를 이용해서 역순으로 쿼리를 해오는것을 보겠습니다.   SQLPLUS scott/tiger -- 조금 깔끔하게 보기 위해서.. 셋팅 먼저 하고요.. SQL> SET LINESIZE 100 SQL> SET PAGESIZE 100 SQL> COL ename FORMAT A20 SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job         FROM emp         START WITH EMPNO=7839         CONNECT BY PRIOR empno=mgr; ENAME                     EMPNO        MGR JOB -------------------- ---------- ---------- --------- KING                         7839                            PRESIDENT     JONES                  7566          7839           MANAGER         SCOTT              7788          7566          ANALYST             ADAMS          7876          7788          CLERK         FORD               7902         7566           ANALYST             SMITH          7369          7902           CLERK                     위 SQL문은 EMPNO가 7839인 것을 기준으로 EMPNO와 MGR컬럼을 가지고 계층구조로 비교해서 결과값을 반환 합니다. 위 SQL문의 CONNECT BY PRIOR empno=mgr 이 부분을..한번 바꺼 볼까요.. 아래처럼.. CONNECT BY empno=PRIOR mgr 이렇게 바꺼서 한번 실행해 보세요.. 그럼..역순으로.. 쿼리를 해서 결과값을 반환 합니다             SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job         FROM emp         START WITH EMPNO=7369        CONNECT BY empno=PRIOR mgr; ENAME                     EMPNO        MGR JOB -------------------- ---------- ---------- --------- SMITH                      7369         7902   CLERK     FORD                   7902         7566   ANALYST         JONES              7566         7839   MANAGER             KING            7839                    PRESIDENT             위에 결과값과 비교해 보세요..   이 SQL문은 실제로..실무에서 카테고리의 Depth를 표현할때 아주 많이 사용 합니다.   컴퓨터/소프트웨어>모니터>일반CRT모니터   이런 카테고리 구조를 하나의 SQL문으로 쉽게 가져 올수가 있죠..