Oracle PL/SQL使用WITH子句

前言

有時候SQL需要許多子查詢進行資料查詢，但若子查詢太多會造成可讀性降低，因此若可以將子查詢先分離出來，就比較好去一一觀看每一個子查詢的意圖，這樣不論在開發或維護上也較容易。

要達到上述的方式可以透過WITH子句達成，WITH子句可分為以下兩個部分。

定義查詢：可定義數個子查詢內容。
使用敘述：將定義好的每一個查詢，當作暫時性的Table一樣，可進行Join以及進行條件查詢等動作。

實作

WITH子句使用方法十分簡單，相關語法如下。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
WITH
  子查詢名稱1 [(別名1, 別名2, 別名3)]
  AS
  (SELECT 陳述)
  ,
  子查詢名稱2 [(別名1, 別名2, 別名3)]
  AS
  (SELECT 陳述)
  ,
  子查詢名稱3 [(別名1, 別名2, 別名3)]
  AS
  (SELECT 陳述)
  ....  -- 結尾無須加 ;

以下進行一個簡單的例子，會比較容易了解上述所說用法。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
DECLARE
  -- 宣告變數
  V_ID ARTICLE.ID%TYPE;
  V_TITLE ARTICLE.TITLE%TYPE;
  V_ACTION_TYPE ARTICLE_LOG.ACTION_TYPE%TYPE;
  -- 建立 CURSOR
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
   -- 第一部份 with 子句
    WITH
      MY_ARTICLE(ID, TITLE)  -- 子查詢-1
      AS
     (SELECT ID, TITLE
       FROM ARTICLE)
     ,
     MY_ARTICLE_LOG (ID, ACTION_TYPE) -- 子查詢-2
     AS
     (SELECT ID, ACTION_TYPE
       FROM ARTICLE_LOG)
    -- 第二部份 with 子句
     SELECT MA.ID, MA.TITLE, MAL.ACTION_TYPE  -- 實際查詢
       FROM MY_ARTICLE MA
         LEFT JOIN MY_ARTICLE_LOG MAL
           ON MA.ID = MAL.ID;

    -- 尋訪 CURSOR
    LOOP
        -- 提取資料
        FETCH V_CURSOR INTO V_ID, V_TITLE, V_ACTION_TYPE;
        -- 判斷是否有讀取到資料
        EXIT WHEN V_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || V_ID || ', TITLE: ' || V_TITLE || ', ACTION_TYPE: ' || V_ACTION_TYPE);
    END LOOP;

    -- 關閉游標
    CLOSE V_CURSOR;
END; 

執行結果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
ID: 7CC053852EB22EA3E050A8C0FD890841, TITLE: PHP 檔案檢查, ACTION_TYPE: INSERT
ID: 7CC22F3B18F3ABE9E050A8C0FD890925, TITLE: 批量寫入資料0, ACTION_TYPE: INSERT
ID: 7CC22F3B18F4ABE9E050A8C0FD890925, TITLE: 批量寫入資料1, ACTION_TYPE: INSERT
ID: 7CC22F3B18F5ABE9E050A8C0FD890925, TITLE: 批量寫入資料2, ACTION_TYPE: INSERT
ID: 7CC22F3B18F6ABE9E050A8C0FD890925, TITLE: 批量寫入資料3, ACTION_TYPE: INSERT
ID: 7CC22F3B18F7ABE9E050A8C0FD890925, TITLE: 批量寫入資料4, ACTION_TYPE: INSERT
ID: 7CC22F3B18F8ABE9E050A8C0FD890925, TITLE: 批量寫入資料5, ACTION_TYPE: INSERT
ID: 7CC22F3B18F9ABE9E050A8C0FD890925, TITLE: 批量寫入資料6, ACTION_TYPE: INSERT
ID: 7CC22F3B18FAABE9E050A8C0FD890925, TITLE: 批量寫入資料7, ACTION_TYPE: INSERT
ID: 7CC22F3B18FBABE9E050A8C0FD890925, TITLE: 批量寫入資料8, ACTION_TYPE: INSERT
ID: 7CC22F3B18FCABE9E050A8C0FD890925, TITLE: 批量寫入資料9, ACTION_TYPE: INSERT
ID: 7D2747D0D4E68A4DE050A8C0FD8908A3, TITLE: Spring JDBC Framework, ACTION_TYPE: INSERT
ID: 7CBEFA025154E146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 1, ACTION_TYPE: INSERT
ID: 7CBEFA025155E146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 2, ACTION_TYPE: INSERT
ID: 7CBEFA025156E146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 3, ACTION_TYPE: INSERT
ID: 7CBEFA025157E146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 4, ACTION_TYPE: INSERT
ID: 7CBEFA025158E146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 5, ACTION_TYPE: INSERT
ID: 7CBEFA025159E146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 6, ACTION_TYPE: INSERT
ID: 7CBEFA02515AE146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 7, ACTION_TYPE: INSERT
ID: 7CBEFA02515BE146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 8, ACTION_TYPE: INSERT
ID: 7CBEFA02515CE146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 9, ACTION_TYPE: INSERT
ID: 7CBEFA02515DE146E050A8C0FD89071A, TITLE: 楊藝的洋溢生活 - 10, ACTION_TYPE: INSERT
ID: 7CC17AB39F2E0B62E050A8C0FD8908CF, TITLE: JQuery AJAX 發送 Post 請求, ACTION_TYPE: INSERT