萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 游標完整教學:概念、類型、宣告開啟擷取關閉、薪資調整實戰範例

什麼是游標?

游標讓使用者逐行處理查詢結果集,將集合操作轉為單列處理。適用於:需逐行邏輯、動態 SQL 建構、跨表複雜業務無法集合化時。

游標優缺點

優點缺點
逐行精細控制效能差 (逐列 = RBAR,Row-By-Agonizing-Row)
支援複雜流程控制佔用 tempdb、鎖資源久、併發差
可定位更新/刪除 (WHERE CURRENT OF)程式碼冗長、難維護

鐵律:能用集合操作 (JOIN、窗口函數、CTE、MERGE) 絕不寫游標。

游標生命週期五步驟

  1. 宣告:DECLARE cursor_name CURSOR FOR SELECT ...
  2. 開啟:OPEN cursor_name (執行查詢、建立結果集)
  3. 擷取:FETCH NEXT FROM cursor_name INTO @var1, @var2 ...
  4. 處理:迴圈中執行業務邏輯
  5. 關閉/釋放:CLOSE cursor_name; DEALLOCATE cursor_name;

進階宣告選項

DECLARE cursor_name 
    [INSENSITIVE] [SCROLL] 
    CURSOR FOR select_statement
    [FOR READ ONLY | FOR UPDATE [OF column_list]]
  • INSENSITIVE:建立臨時表快照,隔離基表變更,唯讀
  • SCROLL:支援 FIRST/LAST/PRIOR/NEXT/RELATIVE/ABSOLUTE 擷取
  • FOR UPDATE:允許 WHERE CURRENT OF 定位更新

實戰範例:薪資調整 (OriginSalary + AddSalary)

資料準備

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 *
  • 避免游標內執行複雜查詢/動態 SQL
  • 批次提交:累積 N 筆再 COMMIT (需自管交易)

總結

游標是 T-SQL 的「核武器」:威力大但副作用嚴重。薪資調整範例完美展示 UPDATE ... JOIN 如何以單一陳述式取代整個游標程序。現代開發:集合思維優先,游標為最後手段。

copyright © 萬盛學電腦網 all rights reserved