[Oracle] WITH clause in SELECT statement

SELECT 구문에서 WITH를 사용하여 subquery에 alias를 지정함으로써, 이 alias를 다른 select 구문에서 사용가능하다.

(from "Oracle® Database SQL Language Reference")

WITH 
dept_costs AS (
      SELECT department_name, SUM(salary) dept_total
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
      GROUP BY department_name),
avg_cost AS (
      SELECT SUM(dept_total)/COUNT(*) avg
      FROM dept_costs)
SELECT * FROM dept_costs
   WHERE dept_total >
      (SELECT avg FROM avg_cost)
      ORDER BY department_name;

위 구문처럼 두 개의 subquery를 dept_consts와 avg_cost로 alias를 부여함으로써, 하위의 select 구문에서 alias를 사용할 수 있다.

Oracle은 이 두개의 alias들을 inline view 또는 temporary table로 취급한다.

 

아래는 WITH clause를 사용한 또 다른 예이다.

WITH TEMP_SEA AS (     
        SELECT O.REG_DT, O.USER_ID
        FROM USER O 
        WHERE O.REG_DT BETWEEN TO_DATE (:FROM_DATE, 'YYYY-MM-DD HH24:MI') 
                   AND TO_DATE (:TO_DATE, 'YYYY-MM-DD HH24:MI'))
SELECT TO_CHAR(T.REG_DT, 'YYYY-MM-DD HH24:MI:SS'), 
       W.ORG_CODE, W.NOTICE_PRD, T.USER_ID
FROM ORG W
         INNER JOIN VW_USER G 
         ON (G.ORG_CODE = W.ORG_CODE AND G.GRP_CODE = 'A01'),
   TEMP_SEA T
WHERE W.GRP_CODE = 'A01' AND W.NOTICE_PRD = :NOTICE_PRD
      AND T.USER_ID = W.USER_ID;