CTE,Cursor,Table Variable,Temporary Table,Stored Procedure

Conetent

分別介紹 CTE,Cursor,Table Variable,Temporary Table,Stored Procedure 這幾項。

一般資料表運算式(Common Table Expressions)

或稱通用資料表運算式；簡單來說，就是建立一個暫存資料表給予查詢使用，此次查詢使用之後，將會自動釋放。另外 CTE 是 SQLServer 2005 才開始支援

語法

WITH expression_name [ ( column_name [,...n] ) ]
AS
( 
     --CTE_query_definition 
)

範例

WITH tmpOrders
AS
(SELECT
        EmployeeID
       ,COUNT(*) AS OrderCounts
    FROM Orders
    GROUP BY EmployeeID)
SELECT
    *
FROM tmpOrders;

• expression_name

  CTE 名稱

• column_name

  在一般資料表運算式中，指定資料行名稱。在單一 CTE 定義內，名稱不能重複。指定的資料行名稱數目必須符合CTE_query_definition 的結果集資料行數目。

• CTE_query_definition

  不能使用下列子句：

  • COMPUTE 或 COMPUTE BY
  • ORDER BY (除非指定了 TOP 子句)
  • INTO
  • 含有查詢提示的 OPTION 子句
  • FOR XML
  • FOR BROWSE

遞迴 CTE

其實我是覺得支援遞迴查詢這部份真的便利許多，雖然還是有些限制錨點部分為遞迴查詢的初始資料，使用 UNION ALL 和遞迴部分結合遞迴部分為需要反覆執行的部分

語法

WITH expression_name AS [(ColName[,...n])]
(
    --CTE_query_definition
    -- 錨點部分
    UNION ALL
    -- 遞迴部分
)

使用規定可以參考 MSDN 比較需要注意的是不允許使用的項目，遞迴成員的 CTE_query_definition 不允許使用下列項目：

• SELECT DISTINCT
• GROUP BY
• HAVING
• 純量彙總
• TOP
• LEFT、RIGHT、OUTER JOIN (允許 INNER JOIN)
• 子查詢
• 適用於 CTE_query_definition 內 CTE 之遞迴參考的提示。

若遞迴成員查詢定義，對父資料行與子資料行傳回相同的值，就會建立無限迴圈。若要防止無限迴圈，可以透過 OPTION 子句中，設定 MAXRECURSION 屬性( 0 和 32767 之間的值)，來限制遞迴層級數目。資料庫的遞迴預設值是 100。 指定 0 時，不限制遞迴層級。此外 WITH 這個關鍵字用在許多表達式中，所以若一個 CTE 表達式的第一行不是由 WITH 開始，就必須加上分號(semicolon)來斷行。

範例

-- 遞迴 Recursive CTE's
;
WITH EmpCTE (EmployeeID, EmployeeFirstName, EmployeeLastName, MgrID, SalesLevel)
AS
(
    -- 錨點部分 Anchor Member (AM) 
    SELECT
        EmployeeID
       ,FirstName
       ,LastName
       ,ReportsTo
       ,0
    FROM Employees
    WHERE EmployeeID = 5

    UNION ALL

    -- 遞迴部分 Recursive Member (RM)
    SELECT
        e.EmployeeID
       ,e.FirstName
       ,e.LastName
       ,e.ReportsTo
       ,m.SalesLevel + 1
    FROM Employees AS e
    INNER JOIN EmpCTE m
        ON e.ReportsTo = m.EmployeeID)

-- 設定 MAXRECURSION 遞迴層次為 5
SELECT
    *
FROM EmpCTE
OPTION (MAXRECURSION 5)

參考資料

• MSDN_WITH common_table_expression
• TechNet_Common Table Expressions
• MSDN Magazine
• 黑大_Common Table Expression
• 介紹的蠻詳細，大力推薦 VITO の SQL 學習筆記
• KingKong Bruce記事
• 張小呆的碎碎唸
• 旗標知識網
• 涛哥博客
• Microsoft REPLICATE
• Microsoft OPTION

---

Cursor

詳細說明參照德瑞克大，這邊主要紀錄一下 DECLARE CURSOR 的使用。不過很多文章都說盡量避免使用 Cursor，以免造成效能損耗，詳細原因的話可以參照RiCo技術農場與德瑞克大。

範例

 -- 建立一個暫存表  等下查看
CREATE Table #tmp
(
  id  int IDENTITY(1,1) NOT NULL,
 EpTitle nvarchar(30)
);

-- 建立Cursor 並啟用了效能最佳化的 FORWARD_ONLY、READ_ONLY 資料指標
DECLARE tmpCursor CURSOR  FAST_FORWARD  FOR 
        (select EmployeeID,Title
        from Employees);
        
-- 宣告 Cursor 等下使用的變數
DECLARE @eid int,@title nvarchar(30);
 
OPEN tmpCursor--開啟Cursor

--將第一筆資料填入變數
FETCH NEXT FROM tmpCursor INTO @eid,@title

--  @@FETCH_STATUS 傳回值
--   0  FETCH 陳述式成功。
--  -1 FETCH 陳述式失敗，或資料列已超出結果集。
--  -2 遺漏提取的資料列。
WHILE @@FETCH_STATUS = 0
BEGIN
     -- 進行想要處理的動作 
    INSERT INTO #tmp (EpTitle) VALUES(@title);
 
    -- 將下一筆資料填入變數 
    FETCH NEXT FROM tmpCursor INTO @eid,@title
END      

--關閉Cursor
CLOSE tmpCursor
--釋放Cursor
DEALLOCATE tmpCursor

-- 看一下剛剛處理後的結果
select * from #tmp

--DROP TABLE #tmp

參考資料

• TechNet_CURSOR
• TechNet_FETCH_STATUS
• TechNet_DEALLOCATE
• TechNet_資料指標
• MSDN_DECLARE CURSOR
• MSDN_FETCH_STATUS
• 德瑞克_初探Cursors(資料指標) 與資料列集(Rowsets)
• RiCo_盡量避免使用Cursor

---

Table Variable 與 Temporary Table

暫存表(Temporary Tables)

暫存表名稱使用 # 開頭，後面接上自訂命名，當連線關閉或中斷後，此暫存表會刪除。暫存表建立後會存在於 tempdb 資料庫。建立方式如下:

CREATE TABLE #TableName
(
    columnName bigint
)

除了 Create 暫存表外，還可以直接使用 SELECT … INTO … 這方式建立暫存表，資料庫會自動按照資料來源 Table 的欄位建立該暫存表。

範例

SELECT *  INTO #temp FROM Northwind.dbo.Employees

全域暫存表(Global Temporary Tables)

暫存表名稱使用 ## 開頭，表示這是全域暫存表，可以被所有連線使用者使用。一般的暫存表僅供建立該表的連線使用。至於全域暫存表的釋放與暫存表相同，是當建立此全域暫存表的連線中斷後，才會釋放。

範例

產生兩個暫存資料表

-- 全域暫存表 GTT
SELECT *  INTO ##temp FROM Northwind.dbo.Employees

-- 暫存表 TT
SELECT *  INTO #temp FROM Northwind.dbo.Employees

在同一連線下 SELECT 資料，暫存表皆可讀取

開一個新連線，一般的暫存表僅供建立該表的連線使用

在新連線中，GTT 可以使用

表格變數(Table Variables)

引用黑暗執行緒大分析的優缺點

優點

• 用於 Stored Procedure 時，不需要每次Recompile，速度較快
• 比照 Local Variable，Scope 定義明確，在不需要時就立刻會被清除
• Transaction Lock 存在時間短，也不影響實體資料庫，資料的更新操作更有效率

  缺點

• 只支援 PK 及 UNIQUE KEY，不能建立 Non-Cluster Index，也沒有資料分佈統計機制，不利於大量資料或複雜的查詢。
• 基於 Local Variable 的限制，使用 sp_executesql 時無法存取
• 不支援 SELECT INTO、INSERT EXEC

結論就是，當暫存的資料筆數較小時，可使用表格變數，如果資料筆數多可使用暫存表。

參考資料

• TechNet_特殊的資料表類型
• 黑大的KB-SQL 2000的資料庫變數(Table Variable)
• 亂馬客_Table-Variable in Transaction
• SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth
• Yet Another Temp Tables Vs Table Variables Article
• 暫存表(Temporary Tables)的使用簡介
• Microsoft技術支援_常見問題集-資料表變數
• Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

---

預存程序(Stored Procedure)

當遇到一些冗長的查詢語句，建議可以使用預存程序來處理，降低網路的傳輸量。預存程序資料傳回方式：

• 使用 SELECT 以表格方式傳回。
• 設定 Output Parameter 以參數方式傳回。
• 使用 RETURN 傳回 1 個整數型別的資料。

範例

搭配此篇文章主題，展示迴圈設計的範例。首先展示 WHILE 迴圈，此方式是以資料筆數的 Count 來進行迴圈設計

--CREATE PROCEDURE dbo.SPRowIndexSample
--AS
-- 宣告 table 變數，此 @temp 資料為迴圈逐筆進行之資料

DECLARE @temp TABLE
(
    ID          INT ,  --ROW序號
    OrderID     INT,
    Employee    NVARCHAR(35)
);

-- @OutTable 為輸出暫存表
DECLARE @OutTable Table
(
  OrderID       INT,
    Employee    NVARCHAR(35)
);

-- 把資料塞到 @temp 變數裡面
INSERT INTO @temp (ID
, OrderID
, Employee)
    (SELECT
        ROW_NUMBER() OVER (ORDER BY OrderID) AS ID,
        OrderID,
        e.FirstName + '-' + e.LastName AS 'Employee'
    FROM Orders o
    INNER JOIN Employees e
        ON o.EmployeeID = e.EmployeeID
    WHERE o.OrderDate > '1998/5/1'
    )

-- 宣告變數
DECLARE
      -- @tmep 資料總筆數 
      @tempCount INT = ( SELECT
    COUNT(ID)
FROM @temp)
-- while 迴圈的逐筆資料 index
, @rowIndex int = 1
, @OrderId int
, @Employee nvarchar(35);

WHILE @rowIndex <= @tempCount
BEGIN

--透過 @tempCount ID 取得欲使用的欄位值
SELECT
    @OrderId = OrderID,
    @Employee = Employee
FROM @temp
WHERE ID = @rowIndex

-- 想要處理的動作 Start --
INSERT INTO @OutTable (OrderID
, Employee)
    VALUES (@OrderId, @Employee)
-- 想要處理的動作 End --

-- 設定 while 條件，跑下一筆
SET @rowIndex = @rowIndex + 1

END

-- 取出
SELECT
    OrderID,
    Employee
FROM @OutTable
--GO

接著是使用字串分割方式進行迴圈設計，不過這方式在字串變數長度小於要進迴圈的資料長度時，很容易會因為資料截斷產生誤差。

--CREATE PROCEDURE dbo.SPStrSplitSample
--AS

-- 宣告字串變數，@strID 為資料來源的 OrderID 組合字串
-- @CurId 為迴圈進行的當下 OrderID
DECLARE @strOID NVARCHAR(2000), @CurOId NVARCHAR(10);

SET @strOID = '';
SELECT @strOID = @strOID + convert(VARCHAR, OrderID) + ','
FROM
  Orders
WHERE
  OrderDate > '1998/5/1';

-- @OutTable 為輸出暫存表
DECLARE @OutTable Table
(
  OrderID       INT,
    Employee    NVARCHAR(35)
);

-- 透過字串長度進行迴圈
WHILE LEN(@strOID) > 0
BEGIN

-- 擷取 OrderID
SET @CurOId = substring(@strOID, 1, charindex(',', @strOID, 1) - 1);
-- 想要處理的動作 Start --
INSERT INTO @OutTable (OrderID
                     , Employee)
SELECT OrderID
     , e.FirstName + '-' + e.LastName AS 'Employee'
FROM
  Orders o
  INNER JOIN Employees e
    ON o.EmployeeID = e.EmployeeID
WHERE
  OrderID = @CurOId;
-- 想要處理的動作 End --

-- 剃除完成的 OrederID，讓迴圈跑下一筆
SET @strOID = right(@strOID, len(@strOID) - charindex(',', @strOID, 1));

END

-- 取出
SELECT OrderID
     , Employee
FROM
  @OutTable
--GO

最後展示接收預存程序的回傳值與回傳參數，如果預存程序使用表格方式輸出結果，那就要用表格參數或是暫存表去接收。

 -- 接收預存程序回傳值
CREATE PROCEDURE SPDemo
(
    @Param1    int
   ,@Param2    varchar(100) OUTPUT  -- 回傳參數設定 OUTPUT
)
AS

IF ISNULL(@Param1,0)> 5
    BEGIN
SET @Param2 = 'the value is greather then 5 '
    END
ELSE
    BEGIN
SET @Param2 = 'the value is less then or equal to 5 '
    END

-- 回傳值
RETURN @Param1
GO
--==============================================
-- @OutputParameter 回傳參數
-- @ReturnValue 回傳值(僅能整數型別)
DECLARE @OutputParameter  varchar(100)
       ,@ReturnValue      int

EXEC @ReturnValue = SPDemo 8,@OutputParameter OUTPUT
PRINT @ReturnValue
PRINT @OutputParameter

參考資料

• TechNet_Stored Procedures 事件類別目錄
• walter 心得筆記_接收 Store Procedure 的傳回值
• RiCo技術農場_撰寫Stored Procedure小細節
• 小信豬的原始部落_Stored Procedures
• 如意網站 SQL 教學網_預存程序
• 如意網站 SQL 教學網_預存程序

備註

• 範例皆使用 SQL SERVER 2008 R2 做為測試