SQL Server DML 語法（SELECT、UPDATE 和 DELETE）是以資料集為資料處理單位，方便且有效率，
而 Cursor 則是以記錄為資料處理單位，對於資料操作彈性較大。
(使用 Cursor 會導致 SQLServer 效能不彰，應視為最後手段)
語法
1
2
3
4
5
6
7
8
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
使用 Cursor 的處理順序
- 宣告 cursor
- 開啟 cursor
- 提取(fetch) cursor
- 關閉 cursor
1
2
3
4
5
6
7
8
9
10
--1. 宣告 cursor
declare cur cursor local for --宣告一個名為cur的指標
--2. 開啟 cursor
open cur
--3. 提取(fetch) cursor
fetch next from cur into @param
--4. 關閉 cursor
close cur
--5
deallocate cur
SQL Cursor 的基礎架構
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE curTemp CURSOR Local Fast_Forward -- 宣告 Cursor 及其資料來源
FOR
(
SELECT Col1,Col2,Col3......
FROM SourceTable
WHERE Condition
)
OPEN curTemp -- 打開 Cursor，並建立 Cursor 與資料表關連
DECLARE @var1 as .....
DECLARE @var2 as .....
FETCH NEXT FROM curTemp INTO @var1,@var2 -- 將資料存進變數中
WHILE (@@FETCH_STATUS = 0) -- 檢查是否有讀取到資料
BEGIN
....................
FETCH NEXT FROM curTemp INTO @var1,@var2
END
CLOSE curTemp -- 關閉 Cursor，並關閉 Cursor 與資料表連結
DEALLOCATE curTemp -- 將 Cursor 物件移除
使用 Cursor 會導致 SQLServer 效能不彰，應視為最後手段，但假如使用時請注意下列事項：
盡量由前往後讀取資料就好 FORWARD_ONLY 和 FETCH NEXT 為預設值，
不要使用 SCROLL 和 FETCH PRIOR、FETCH FIRST、FETCH LAST 等語法，
個人習慣通常都會設定 FAST_FORWARD 來提高效率。
不要利用 Cursor 來修改和刪除資料，能明確指定為 READ_ONLY 較好。
避免在 Cursor 中進行排序
範例1：使用簡單資料指標和語法
在開啟這個資料指標時所產生的結果集內，包含了資料表的所有資料列及所有資料行。
可以更新這個資料指標，而且所有更新和刪除都會在針對這個資料指標所做的提取中表示。
FETCH NEXT 是唯一可用的提取，因為尚未指定 SCROLL 選項。
1
2
3
4
DECLARE vend_cursor CURSOR --宣告指標
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor --開啟指標
FETCH NEXT FROM vend_cursor; --提取指標
範例2
製作出使用cursor來求得販賣單價之總和的預存程序
(此範例並不是一定要用到cursor)
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
--製作出使用cursor來求得販賣單價之總和的預存程序
--(此範例並不是一定要用到cursor)
create or alter proc uspTestCursor
--宣告參數
@total int output --輸出參數
as
--1.定義cursor指標變數
declare curTest cursor local for
--此區域可以撰寫資料集
select isnull(販賣單價, 0) from 商品清單 --商品清單中的所有販賣單價
--定義變數
declare @price int --宣告價格變數
begin
--處理內容
set @total = 0 --設定total初始值為0
--2.開啟cursor指標
open curTest
--3.提取指標(開始跑cursor迴圈)
/*
fetch next from curTest into` 為什麼要寫兩次?
第一次是用來判斷 @@FETCH_STATUS的
後面一次是才是循環使用的
*/
fetch next from curTest into @price --提取的資料存入price變數(用來判斷@@FETCH_STATUS)
while @@FETCH_STATUS = 0 --檢查是否有讀取到資料(0:提取成功)
begin
--此區塊就可以處理商業邏輯
set @total += @price --加總
--3.提取指標
fetch next from curTest into @price --提取的資料存入price變數(while使用的)
end
--4.關閉指標
close curTest
--5.將指標 Cursor物件移除
deallocate curTest
end
- 以
@@FETCH_STATUS來檢查是否還有資料
注意：
fetch next from cursor into為什麼要寫兩次?
用兩次「FETCH NEXT FROM」是因為：第一次是用來判斷@@FETCH_STATUS的，
後面一次「FETCH NEXT FROM」才是循環使用的！是被重複執行的。。每次讀取一行！
執行預存程序
1
2
3
4
5
6
declare @total int --宣告變數
exec uspTestCursor @total output --執行sp
select @total --顯示結果
--執行結果
--797000
@@FETCH_STATUS
@@FETCH_STATUS 此函數會傳回針對連接目前開啟的任何資料指標所發出的最後一個資料指標 FETCH 陳述式的狀態。
傳回值：
0FETCH 陳述式成功。-1FETCH 陳述式失敗，或資料列已超出結果集。-2遺漏提取的資料列。-9資料指標並未執行擷取作業。
範例
此範例使用 @@FETCH_STATUS 控制 WHILE 迴圈中的資料指標活動。
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2022.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
為什麼要用兩次 Fetch Next From Into？
指標Cursor一般格式
1
2
3
4
5
6
7
8
9
10
DECLARE 指標名稱 CURSOR FOR Select_SQL語句
OPEN 指標名稱
FETCH NEXT FROM 指標名稱 INTO @變數1, @變數2, @變數3,... --將指標下移一行，獲取的數據存入變數
WHILE @@FETCH_STATUS=0 --判斷是否成功獲取數據
BEGIN
--SQL語句執行過程... ...
FETCH NEXT FROM 指標名稱 INTO @變數1, @變數2, @變數3,... --將指標下移一行
END
CLOSE 指標名稱
DEALLOCATE 指標名稱
注意：用兩次「FETCH NEXT FROM」是因為，第一次是用來判斷@@FETCH_STATUS的，後面一次「FETCH NEXT FROM」才是循環使用的！是被重複執行的。。每次讀取一行！
MSDN - @@FETCH_STATUS (Transact-SQL)
MSDN - DECLARE CURSOR (Transact-SQL)
MSDN - 資料指標 (Transact-SQL)
德瑞克：SQL Server 學習筆記 - 初探Cursors(資料指標) 與資料列集(Rowsets)
[SQL] CURSOR
SQL Cursor 基本用法[用两次FETCH NEXT FROM INTO语句?]