Oracle SQL 활용

SQL 활용

계층형 질의

계층형 질의는 순환관계를 SQL로 구현시 사용한다. 계층형 데이터란 동일테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. ex) 회사 조직도

구문

SELECT …

FROM 테이블 WHERE condition AND condition… START WITH conditon
CONNECT BY [NOCYCLE] PRIOR condition AND condition… [ORDER SIBLINGS BY column, column, …]

예시

SELECT level, empno, ename, mgr
FROM emp
WHERE MGR = 8000    // 후행조건**
START WITH MGR IS NULL          // root 지정
CONNECT BY PRIOR empno = mgr    // 자식 데이터를 지정 
ORDER SIBLINGS BY empno;        // 형제레벨 사이의 정렬

PRIOR이 안붙은 쪽이 상위컬럼이면 순방향.

가상컬럼

가상컬럼	설명
LEVEL	계층의 레벨을 나타내는 가상컬럼. ROOT는 1, 하위데이터마다 1씩 증가
CONNECT_BY_ISLEAF	해당 데이터가 leaf이면 1, 그렇지 않으면 0

leaf data란 자식이 없는 데이터를 말함.

예시

SELECT LEVEL, LPAD(' ', 4 * (LEVEL - 1)) || empno AS 사원,
    mgr AS 관리자, CONNECT_BY_ISLEAF AS ISLEAF
FROM emp
START WITH mgr is null
CONNECT BY PRIOR empno = mgr;

LPAD : 값을 총 문자 길이로 만든다.

LPAD(컬럼값, 총 길이, 채움문자)

LPAD(empno, 7, '0')     // 사번채우기

함수

함수	설명
SYS_CONNECT_BY_PATH	루트부터 현재 전개할 데이터까지 경로 표시
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터 표시

예시

SELECT CONNECT_BY_ROOT(empno) AS root,
        SYS_CONNECT_BY_PATH(empno,'/') AS path,
        empno, mgr
FROM emp
START WITH mgr is null
CONNECT BY PRIOR empno = mgr;

Self Join

계층구조를 갖고있는 동일 테이블 사이의 조인 시, OUTER JOIN을 사용한다. 예시를 참조하자.

SELECT E1.empno 사원, E1.mgr 관리자, E2.mgr 차상위관리자
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON E1.mgr = E2.empno
ORDER BY E2.mgr desc, E1.mgr, E1.empno;

GROUP Function

하나의 SQL로 테이블을 한 번만 일거엇 빠르게 원하는 리포트를 작성하게 해주는 함수로, 그룹간의 소계 등을 계산할때 주로 사용한다.

ex) 부서별 소계 구하기 등.

• ROLLUP : 소그룹 간의 소계를 계산

• GROUPING SETS : 특정항목에 대한 소계 계산 (예시참조)

• CUBE : 다차원적 소계 계산

WINDOW Function