CSV String (欄位值) 2 Row (Table)

純粹CSV格式字串(如: A,B,C)轉換現行作法非常簡便，但若為資料表中資料，則處理上則稍具難度，查詢資料表中其他欄位資料值需配合CSV展開成多筆，MSSQL 2016版本起提供STRING_SPLIT函數可直接呼叫使用，其他版本或ORACLE則可利用XML進行展開，範例如下:

版本	SQL
MSSQL 2005	SELECT Id      , VAL      , ROW_NUMBER() OVER (ORDER BY GETDATE()) SEQ      , Split.Col.value('.', 'VARCHAR(100)') AS X FROM     (     SELECT D.Id, D.VAL           , CAST ('<S>'                    + REPLACE(D.VAL, ',', '</S><S>')                    + '</S>' AS XML        ) AS Val_List     FROM (VALUES (1, 'B,A')                      , (2, 'B,C')) AS D(Id, VAL)      ) D CROSS APPLY Val_List.nodes ('/S') AS Split(Col) 註: VALUES為2008指令
MSSQL 2016	SELECT Id, VAL       , value     FROM (VALUES(1, 'B,A')           , (2, 'B,C'))AS D(Id, VAL)      CROSS APPLY STRING_SPLIT(VAL, ',')
ORACLE 10g	WITH TT AS  ( SELECT 1 Id, 'B,A' VAL FROM DUAL UNION ALL SELECT 2 Id, 'B,C' VAL FROM DUAL ) SELECT Id      , VAL      , EXTRACT(VALUE(I), '//row/text()').getstringval () AS X FROM     (     SELECT Id, VAL            , XMLTYPE('<rows><row>'                         || REPLACE (Val, ',', '</row><row>')                         || '</row></rows>'                    ) AS XMLString     FROM TT     ) D, TABLE (XMLSEQUENCE(EXTRACT (D.XMLString, '/rows/row'))) I;

CSV String 2 Row(Table)

純粹CSV格式字串(如: A,B,C)，資料可能為使用者輸入或傳入參數等，現已有許多函數或基本作法處理上非常簡便，ORACLE可直接使用DBMS_DEBUG_VC2COLL或使用者自建Type等方法進行轉換，MSSQL於2016版本起提供STRING_SPLIT函數可直接呼叫使用。

版本	SQL
MSSQL 2005	SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) SEQ      , Split.Col.value('.', 'VARCHAR(100)') AS X FROM     (     SELECT CAST ('<S>'                    + REPLACE('A,B,C', ',', '</S><S>')                     + '</S>' AS XML                     ) AS Val_List     ) AS A CROSS APPLY Val_List.nodes ('//S') AS Split(Col)
MSSQL 2016	SELECT value --欄位名稱為value FROM STRING_SPLIT('A,B,C', ',')
ORACLE 10g	SELECT column_value FROM TABLE(SYS.DBMS_DEBUG_VC2COLL( 'A','B','C' ) ); SELECT column_value FROM TABLE( SYS.DBMS_DEBUG_VC2COLL( 1, 2, 3 )); 9i版本可使用自建TYPE即可 CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767) ; SELECT column_value FROM TABLE(SPLIT_TBL( 'A','B','C' ) );

一般表格運算式(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)。

找到含有『特定字串』的Stored Procedure

以下說明是找到含有『特定字串』的所有Stored Procedure，ORACLE可以查詢ALL_SOURCE，MSSQL則可查詢sys.procedures、sys.sql_modules及INFORMATION_SCHEMA.ROUTINES，在此只以sys.sql_modules進行說明。SQL如下：

SQL SERVER	ORACLE
SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE Definition LIKE '%DEL_FLAG%'     AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1	SELECT DISTINCT S.NAME, S.TYPE FROM ALL_SOURCE S WHERE TEXT LIKE '%WNTD_RATE%'

參考資料: sys.sql_modules (Transact-SQL), http://technet.microsoft.com/zh-tw/library/ms175081.aspx