[Oracle] With Clause (with as) 사용 방법 예제 자료

With Clause(with as)

with절은 인라인뷰 또는 임시테이블 역할을 수행할 수 있는 기능을 제공합니다.

특히 사용을 고려할만한 곳은 반복되는 서브쿼리 구문이 많은 뷰입니다.

예를 들면 EMP 테이블과 DEPT 테이블을 조인한 결과를 여러번 사용한다고 가정했을 때입니다. 다음과 같은 예제를 보여드리겠습니다.

WITH AS 예시 테이블

with 절 없이 예시 자료를 union all로 구현한다면 다음과 같을 것입니다.

뷰의 중복을 확인하실 수 있습니다.

SELECT EMPNO,
       ENAME,
       JOB,
       EMP.DEPTNO,
       DNAME,
       LOC
  FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND EMP.DEPTNO = '10'
UNION ALL
SELECT EMPNO,
       ENAME,
       JOB,
       EMP.DEPTNO,
       DNAME,
       LOC
  FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'CHICAGO'
UNION ALL
SELECT EMPNO,
       ENAME,
       JOB,
       EMP.DEPTNO,
       DNAME,
       LOC
  FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND DNAME = 'RESEARCH';

중복되는 뷰를 WITH AS로 다음과 같이 통합할 수 있습니다.

WITH TAB
     AS (SELECT EMPNO,
                ENAME,
                JOB,
                EMP.DEPTNO,
                DNAME,
                LOC
           FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO)
SELECT *
  FROM TAB
 WHERE DEPTNO = '10'
UNION ALL
SELECT *
  FROM TAB
 WHERE LOC = 'CHICAGO'
UNION ALL
SELECT *
  FROM TAB
 WHERE DNAME = 'RESEARCH';

with as 에서 뷰를 단수로 사용한다면 다음과 같이 사용합니다.

WITH TA AS (SELECT * FROM EMP)
SELECT *
  FROM TA

with as 에서 뷰를 복수로 사용하면 콤마(,)로 구분하시면 됩니다.

WITH TA AS (SELECT * FROM EMP),
     TB AS (SELECT * FROM DEPT)
SELECT *
  FROM TA LEFT JOIN TB ON TB.DEPTNO = TA.DEPTNO

다음은 오라클 테스트 테이블(demobld.sq)을 with as로 조회해 보는 예시입니다.

WITH EMP
     AS (SELECT 7369 EMPNO, 'SMITH' ENAME, 'CLERK' JOB, 7902 MGR, TO_DATE ('19801217') HIREDATE, 800 SAL, NULL COMM, 20 DEPTNO FROM DUAL UNION ALL
         SELECT 7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE ('19810220'), 1600, 300, 30 FROM DUAL UNION ALL
         SELECT 7521, 'WARD', 'SALESMAN', 7698, TO_DATE ('19810222'), 1250, 500, 30 FROM DUAL UNION ALL
         SELECT 7566, 'JONES', 'MANAGER', 7839, TO_DATE ('19810402'), 2975, NULL, 20 FROM DUAL UNION ALL
         SELECT 7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE ('19810928'), 1250, 1400, 30 FROM DUAL UNION ALL
         SELECT 7698, 'BLAKE', 'MANAGER', 7839, TO_DATE ('19810501'), 2850, NULL, 30 FROM DUAL UNION ALL
         SELECT 7782, 'CLARK', 'MANAGER', 7839, TO_DATE ('19810609'), 2450, NULL, 10 FROM DUAL UNION ALL
         SELECT 7788, 'SCOTT', 'ANALYST', 7566, TO_DATE ('19821209'), 3000, NULL, 20 FROM DUAL UNION ALL
         SELECT 7839, 'KING', 'PRESIDENT', NULL, TO_DATE ('19811117'), 5000, NULL, 10 FROM DUAL UNION ALL
         SELECT 7844, 'TURNER', 'SALESMAN', 7698, TO_DATE ('19810908'), 1500, 0, 30 FROM DUAL UNION ALL
         SELECT 7876, 'ADAMS', 'CLERK', 7788, TO_DATE ('19830112'), 1100, NULL, 20 FROM DUAL UNION ALL
         SELECT 7900, 'JAMES', 'CLERK', 7698, TO_DATE ('19811203'), 950, NULL, 30 FROM DUAL UNION ALL
         SELECT 7902, 'FORD', 'ANALYST', 7566, TO_DATE ('19811203'), 3000, NULL, 20 FROM DUAL UNION ALL
         SELECT 7934, 'MILLER', 'CLERK', 7782, TO_DATE ('19820123'), 1300, NULL, 10 FROM DUAL),
     DEPT
     AS (SELECT 10 DEPTNO, 'ACCOUNTING' DNAME, 'NEW YORK' LOC FROM DUAL UNION ALL
         SELECT 20, 'RESEARCH', 'DALLAS' FROM DUAL UNION ALL
         SELECT 30, 'SALES', 'CHICAGO' FROM DUAL UNION ALL
         SELECT 40, 'OPERATIONS', 'BOSTON' FROM DUAL),
     SALGRADE
     AS (SELECT 1 GRADE, 700 LOSAL, 1200 HISAL FROM DUAL UNION ALL
         SELECT 2, 1201, 1400 FROM DUAL UNION ALL
         SELECT 3, 1401, 2000 FROM DUAL UNION ALL
         SELECT 4, 2001, 3000 FROM DUAL UNION ALL
         SELECT 5, 3001, 9999 FROM DUAL)
SELECT * FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO;

With As + Insert

INSERT INTO EMP (EMPNO, ENAME)
   WITH TAB AS (SELECT '9999', 'NAME' FROM DUAL)
   SELECT *
     FROM TAB;

with 절 다음에는 select 키워드가 따라와야 합니다. 그리하여 with as + update 는 대상 테이블의 컬럼을 지정하거나 뷰를 가공하여 활용합니다.

대신 insert 구문은 손쉽게 insert into [statement] with as 로 간단히 사용할 수 있습니다.

Oracle With As Example(EMP TABLE)

예제 생성 시 활용자료로 이용하려 만들어 보았습니다.

with as hint, /*+ MATERIALIZE */ /*+ INLINE */

가끔 보실 수 있는 힌트입니다.

materialize 힌트는 글로벌 템프 테이블을 만들라는 의미이고, inline 힌트는 인라인뷰에서 실행하라는 의미입니다.

오라클데이터베이스가 발전할수록 굳이 지정할 필요 없이, 알아서 퍼포먼스를 관리하기 때문에 별도로 지정하실 필요는 없어보입니다. 이러한 힌트도 있다는 것만 보고 넘어가시면 돼요.

댓글