一般表格運算式(Common Table Expression: CTE)

本文將探討一般表格運算式（Common Table Expression；CTE）的應用，MSSQL及ORACLE遵循SQL-99規範中所提供一種新的語法WITH，以是否為遞迴（Recursive）運算區分成「查詢暫存」及「遞迴呼叫」兩種。

查詢暫存（非遞迴的運用）

過去需要執行較為複雜的SQL指令時，或局部相同SQL段落需多次被使用等狀況，前述情況下會考量效能的問題，而將某些局部運算的結果先暫存於資料庫的暫存表格中，以供後續再使用或重複使用， CTE可針對查詢指令予以命名及定義如同區域變數—般，以供同一命令中後續查詢參考使用。語法如下：

WITH <subquery_name> AS      (      < SQL statement>      ) SELECT <Column-List> FROM <subquery_name>

以下將取得訂單資料中各種書籍訂購數量超過平均值的書籍資料，SQL語法及結果如下所示：

MSSQL可於WITH後進行UPDATE或DELETE運算，先前文章中應用於刪除重複資料使用；ORACLE若於WITH運算後使用UPDATE或DELETE指令，則將出現「ORA-00928:遺漏SELECT關鍵字錯誤」。

非遞迴式的WITH指令與衍生資料表（Derived Table）相當類似，可供後續參照使用，但與衍生表格不同之處在於每次使用查詢語句時無需重複輸入。在主查詢進行前，先以WITH宣告暫時性表格的名稱及內容，接著進行主查詢則以WITH名稱代替，並可重複出現，此與傳統的衍生資料表夾雜於主查詢中的語法相比更為簡潔易讀。

遞迴的運用

MSSQL 2005（含）以上提供遞迴查詢，ORACLE雖於9i R2提供WITH指令，但於11g R2（含）以上版本才提供遞迴功能。本節將只針對遞迴運算部分介紹，以下分別介紹ORACLE及SQL SERVER在WITH上的使用，兩者語法極為類似，後續範例只需針對資料庫函數進行局部修改即可。

WITH cte_name ( column_name [,...n] ) AS ( CTE_query_definition --遞迴起點(或稱錨點) UNION ALL CTE_query_definition--遞迴呼叫區塊，將參照本身即cte_name一次 )

遞迴WITH與一般程式語言撰寫遞迴呼叫基概念雷同，簡單來說，當一個函數呼叫自己時，此方式即可稱為遞迴呼叫，以下為遞迴WITH之

l   遞迴WITH定義必須包含遞迴起點（或稱錨點）和遞迴呼叫區塊兩部分。

l   遞迴起點（或稱錨點）和遞迴呼叫區塊必須使用UNION ALL連結。

兩者是採用UNION ALL連結，UNION ALL使用上要求兩者資料的欄位數目相符及資料型態需為同一類型，在此要求更為嚴格資料型態及欄位長度（精準度）也必須完全相同。

l   遞迴呼叫中的FROM子句中，只能呼叫參考CTE expression_name一次。

l   確認終止條件：對於遞迴呼叫需特別注意遞迴結束條件，為了避免開發期間遞迴結束條件遺漏或不當使用，可設定最大遞迴深度以限制其展開深度。

l   遞迴呼叫中的FROM子句中，MSSQL僅可使用Inner Join不可使用Outer Join，ORACLE則無此限。

首先，應用遞迴WITH指令產生連續的數值數列，為避免無窮迴圈對系統效能的影響，SQL SERVER預設最大遞迴深度為100，可採用MAXRECURSION設定最大遞迴深度，為動態產生1至10000序號，將設定最大遞迴深度為10000。第二個範例產生日期序列，並用以產生科學園區四二輪週報。SQL語法分別說明如下：

數值序列（1至10000）

WITH Tally(N) AS   (   --1.錨點(Anchor)   SELECT 1 N        --起始條件為   UNION ALL        --用UNION ALL串連兩區塊   --2.遞迴區塊   SELECT N+1       --遞迴條件為累加   FROM Tally        --呼叫自已   WHERE N<=10000  --結束條件   )   SELECT N FROM TALLY OPTION (MAXRECURSION 10000)  --SQL SERVER(設定最大深度)

註：以上SQL為MSSQL，ORACLE則需增加FROM DUAL以及將OPTION部分移除。

前述SQL是利用Recursive產生數值序列，虛擬資料表欄位僅有一個數值型態的欄位欄位名稱為N，數列自1開始至10000，重點說明如下：

n   遞迴起點（或稱錨點）

SELECT 1 N

此例為自建數值序列並無任何參照資料表，將以自建方式產錨點，欄位僅有一個名稱為N，型態則由欄位資料值1所決定，資料判別其型態為INT。

n   遞迴區塊

SELECT N+1       --遞迴條件為累加

FROM Tally       --呼叫自已

WHERE N<=10000  --結束條件

上述FROM子句所指定的Tally即為遞迴本身名稱，由此可達成自己呼叫自己的遞迴運算結構。WHERE子句指定的即為遞迴結束條件。SELECT子句則產生累加數值序列，前述省略欄位別名修改為N+1 AS N，資料型態同樣為以自行判別方式。遞迴起點將以UNION ALL將兩者連結。

前述兩項重點即可決定整個遞迴呼叫，由於SQL SERVER預設遞迴最大深度為100，為產生10000個序列則必須以指定方式變更最大遞迴上限為10000，語法如下：OPTION (MAXRECURSION 10000)。