Theme NexT works best with JavaScript enabled

ShunNien's Blog

不積跬步,無以致千里;不積小流,無以成江海。

0%

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

Conetent

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

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

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

語法

1
2
3
4
5
WITH expression_name [ ( column_name [,...n] ) ]
AS
(
--CTE_query_definition
)

範例

1
2
3
4
5
6
7
8
9
10
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 和遞迴部分結合遞迴部分為需要反覆執行的部分

語法

1
2
3
4
5
6
7
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)來斷行。

範例

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
-- 遞迴 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)

CTE Demo

參考資料


Cursor

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

範例

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
41
42
 -- 建立一個暫存表  等下查看
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

Cursor Demo

參考資料


Table VariableTemporary Table

暫存表(Temporary Tables)

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

1
2
3
4
CREATE TABLE #TableName
(
columnName bigint
)

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

範例

1
SELECT *  INTO #temp FROM Northwind.dbo.Employees

Temporary Tables Demo

全域暫存表(Global Temporary Tables)

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

範例

產生兩個暫存資料表

1
2
3
4
5
-- 全域暫存表 GTT
SELECT * INTO ##temp FROM Northwind.dbo.Employees

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

Global Temporary Tables Demo

在同一連線下 SELECT 資料,暫存表皆可讀取
GTT the same connection Demo

開一個新連線,一般的暫存表僅供建立該表的連線使用
GTT other connection Demo

在新連線中,GTT 可以使用
GTT on new connection Demo

表格變數(Table Variables)

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

優點
  • 用於 Stored Procedure 時,不需要每次Recompile,速度較快
  • 比照 Local Variable,Scope 定義明確,在不需要時就立刻會被清除
  • Transaction Lock 存在時間短,也不影響實體資料庫,資料的更新操作更有效率
    缺點
  • 只支援 PK 及 UNIQUE KEY,不能建立 Non-Cluster Index,也沒有資料分佈統計機制,不利於大量資料或複雜的查詢。
  • 基於 Local Variable 的限制,使用 sp_executesql 時無法存取
  • 不支援 SELECT INTO、INSERT EXEC

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

參考資料


預存程序(Stored Procedure)

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

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

範例

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

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
--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

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

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
41
42
43
44
45
46
47
48
49
50
51
--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

string split loop Demo
string split loop error Demo
最後展示接收預存程序回傳值回傳參數,如果預存程序使用表格方式輸出結果,那就要用表格參數或是暫存表去接收。

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
 -- 接收預存程序回傳值
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

return Demo

參考資料

備註

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

歡迎關注我的其它發布渠道