游標讓使用者逐行處理查詢結果集,將集合操作轉為單列處理。適用於:需逐行邏輯、動態 SQL 建構、跨表複雜業務無法集合化時。
| 優點 | 缺點 |
|---|---|
| 逐行精細控制 | 效能差 (逐列 = RBAR,Row-By-Agonizing-Row) |
| 支援複雜流程控制 | 佔用 tempdb、鎖資源久、併發差 |
| 可定位更新/刪除 (WHERE CURRENT OF) | 程式碼冗長、難維護 |
鐵律:能用集合操作 (JOIN、窗口函數、CTE、MERGE) 絕不寫游標。
DECLARE cursor_name CURSOR FOR SELECT ...OPEN cursor_name (執行查詢、建立結果集)FETCH NEXT FROM cursor_name INTO @var1, @var2 ...CLOSE cursor_name; DEALLOCATE cursor_name;DECLARE cursor_name
[INSENSITIVE] [SCROLL]
CURSOR FOR select_statement
[FOR READ ONLY | FOR UPDATE [OF column_list]]
WHERE CURRENT OF 定位更新CREATE TABLE OriginSalary (O_ID NVARCHAR(20), O_Name NVARCHAR(100), O_Salary FLOAT);
CREATE TABLE AddSalary (O_ID NVARCHAR(20), A_Salary FLOAT);
INSERT OriginSalary VALUES ('E001','張三',50000),('E002','李四',60000),('E003','王五',55000);
INSERT AddSalary VALUES ('E001',5000),('E002',3000),('E003',2000);
CREATE PROCEDURE PK_SalaryAdd
AS
BEGIN
SET NOCOUNT ON;
DECLARE @O_ID NVARCHAR(20), @A_Salary FLOAT;
DECLARE cur_salary CURSOR LOCAL FAST_FORWARD FOR
SELECT O_ID, A_Salary FROM AddSalary;
OPEN cur_salary;
FETCH NEXT FROM cur_salary INTO @O_ID, @A_Salary;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE OriginSalary
SET O_Salary = O_Salary + @A_Salary
WHERE O_ID = @O_ID;
FETCH NEXT FROM cur_salary INTO @O_ID, @A_Salary;
END
CLOSE cur_salary;
DEALLOCATE cur_salary;
END
EXEC PK_SalaryAdd;
SELECT * FROM OriginSalary;
-- E001 張三 55000
-- E002 李四 63000
-- E003 王五 57000
-- 單一 UPDATE + JOIN,效能碾壓游標
UPDATE o
SET o.O_Salary = o.O_Salary + a.A_Salary
FROM OriginSalary o
JOIN AddSalary a ON o.O_ID = a.O_ID;
LOCAL FAST_FORWARD:唯讀、僅向前、最佳化器自動選擇最輕量實作READ_ONLY:明確宣告不更新,避免鎖升級SELECT O_ID, A_Salary 而非 SELECT *COMMIT (需自管交易)游標是 T-SQL 的「核武器」:威力大但副作用嚴重。薪資調整範例完美展示 UPDATE ... JOIN 如何以單一陳述式取代整個游標程序。現代開發:集合思維優先,游標為最後手段。