ORACLE DB 的學習者們

2018年1月15日 星期一

ORACLE WITH 的用法

ORACLE 的 WITH 語法，可以將複雜的 SQL 語句，以 WITH 語法並給予其名稱，在其後的查詢中，透過呼叫該名稱，可反覆使用。

參考文章：其一、 其二 。

當查詢很複雜，又需要反覆使用子查詢的結果時，WITH便很方便。如果想查出各部門中，哪一部門的總薪資，高於所有部門的平均薪資，那總平均薪資就屬於反覆使用的子查詢。透過WITH語法，將查詢語句包圍在一個WITH語句中，透過反覆呼叫該語句，便可以達成子查詢。

下面的 SQL 想查詢在每個部門裡有多少人，原來的SQL如下 

SELECT e.first_name,e.last_name AS employee_name,
       dc.department_id, dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc
WHERE  e.department_id = dc.department_id;
改用WITH語法後的SQL如下 

WITH dept_count AS (
  SELECT department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT e.first_name,e.last_name AS employee_name,
       dc.department_id,dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.department_id = dc.department_id;
從上面的語法，以WITH語法定義了一段子查詢，命名為 DEPT_COUNT，用在其後的SQL當中，如下圖所示。
要從本身具備階層關係的表格中，把部門員工和所屬主管及各部門人數拉出來，若用傳統SELF-JOIN的方式處理，其SQL如下： 

SELECT e.last_name AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.last_name AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc1,
       emp m,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc2
WHERE  e.department_id = dc1.department_id
AND    e.manager_id = m.employee_id
AND    m.department_id = dc2.department_id;
上述SQL的執行結果如下圖所示。

相同的查詢，若改以 WITH 的語法，看起來會較有結構。 

WITH dept_count AS (
  SELECT department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT e.last_name AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.last_name AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.department_id = dc1.department_id
AND    e.manager_id = m.employee_id
AND    m.department_id = dc2.department_id;
上述的語法可以結合兩種編譯註解 (HINTS) ，MATERIALIZE 使其以 global temporary table 的方式處理 WITH 的語句，INLINE 則是以內嵌的方式處理 
WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT ............ ;

WITH dept_count AS (
  SELECT /*+ INLINE */ department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT ..............;
WITH 語法還可以讓重複使用的查詢，以代稱命名後，反覆呼叫使用。以下範例，透過 WITH 建立兩個子查詢，分別查詢部門總薪資(部門總成本或總薪資)，及總平均薪資，簡化後的 SQL ，主體SQL變得非常簡潔。 
WITH 
  dept_costs AS (
    SELECT department_name, SUM(salary) dept_total
    FROM   emp 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;
上述查詢得到銷售部門總薪資高於全體平均薪資。
張貼者： 於