SQL SERVER 從入門到精通 第5版 第三篇 高階應用 第12章 遊標的使用 讀書筆記

一曲轻扬發表於2024-04-30
SQLServer筆記

第十二章 遊標的使用

　　>.遊標的概述

　　遊標是一種資料庫物件，用於在SQL中處理(SELECT的)查詢結果集。它允許逐行地訪問查詢結果集的資料，以進行一系列操作，如更新、刪除或插入資料。遊標通常用於儲存過程或觸發器中，用於對資料進行逐行處理。透過遊標，可以實現對結果集的逐行處理，可以方便地進行資料處理和操作。遊標在資料庫中的使用可能會導致效能問題，應該謹慎使用。

　　>.遊標的實現

　　一個完整的遊標由五個部分組成,並且遵循以下順序:

　　　　1.宣告遊標

　　　　2.開啟遊標

　　　　3.使用遊標

　　　　4.關閉遊標

　　　　5.釋放遊標

　　>.遊標的型別

　　　　>.靜態遊標

　　　　　　靜態遊標的結果集是儲存在tempdb中的,相當於是對原資料的一個快照,只讀,不能對原資料進行修改.不能看到資料的更新. 特點: 只讀,消耗資源少,適用於資料量不大且不需要頻繁更新的情況。

　　　　>.動態遊標

　　　　　　與靜態遊標相反,動態遊標是可讀寫的,它會反映出使用者對結果集所做的所有修改,包括增刪改.特點:讀寫,消耗資源多,適用於需要頻繁更新的情況。

　　　　>.只進遊標

　　　　　　靜態遊標和動態遊標都是可以前後滾動的,而只進遊標只能向前滾動遊標，不能在結果集中反向移動。一旦過了記錄，就無法再次檢索。注意:只進遊標除了不能向後移動,其他的基本上與動態遊標無異,也就是說,它是可讀寫基礎表資料的!

　　　　>.鍵集驅動遊標

　　　　　　鍵集驅動遊標介於靜態遊標與動態遊標之間.換句話說就是,它同時具備了靜態遊標與動態遊標的部分特點.當開啟遊標時，該遊標中的成員以及行的順序是固定的，鍵集在遊標開啟時也會儲存到tempdb中，對非鍵集列的資料值的更改在使用者遊標滾動的時候可以看見，在遊標開啟以後對資料庫中插入的行是不可見的，除非關閉重新開啟遊標.適用於需要對遊標進行大量查詢和跳躍的情況。

　　>.遊標的基本操作

　　　　>.宣告遊標　　　　
--語法:DECLARE 遊標名稱 CURSOR FOR + SELECT語句
--例如:
DECLARE cursor_name CURSOR FOR
SELECT column1, column2, ...
FROM table_name
WHERE condition;

舉例一:

假設我們有一個名為"employees"的表，包含員工的ID、姓名和薪水欄位。我們需要計算員工的總薪水並輸出每位員工的姓名和對應的總薪水。

-- 建立一個儲存過程
CREATE PROCEDURE calculate_total_salary
AS
BEGIN
    -- 宣告遊標
    DECLARE employee_cursor CURSOR FOR
    SELECT name, salary
    FROM employees;

    DECLARE @name NVARCHAR(50); -- 宣告一個變數用於儲存員工姓名
    DECLARE @salary INT; -- 宣告一個變數用於儲存員工薪水
    DECLARE @total_salary INT = 0; -- 初始化總薪水為0

    -- 開啟遊標
    OPEN employee_cursor;

    -- 獲取資料
    FETCH NEXT FROM employee_cursor INTO @name, @salary; -- 嘗試獲取第一行資料
    WHILE @@FETCH_STATUS = 0 -- 當能夠獲取資料時執行迴圈
    BEGIN
        SET @total_salary = @total_salary + @salary; -- 累計總薪水
        PRINT 'Employee ' + @name + ' total salary is ' + CAST(@salary AS NVARCHAR(10)); -- 列印每位員工的姓名和總薪水

        FETCH NEXT FROM employee_cursor INTO @name, @salary; -- 嘗試獲取下一行資料
    END

    -- 關閉遊標
    CLOSE employee_cursor;
    DEALLOCATE employee_cursor;

    PRINT 'Total salary of all employees is ' + CAST(@total_salary AS NVARCHAR(10)); -- 列印所有員工的總薪水
END

舉例二:

　　　　宣告遊標時,可以在SELECT語句的末尾加FOR關鍵字,對遊標內的資料進行限制,比如

--前面是宣告一個遊標,SELECT語句後面的For將對資料進行約束
DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, Name, Department
FROM Employee
ORDER BY EmployeeID
--FOR READ ONLY;   -- 宣告遊標並限制資料為只讀
--FOR update;   -- 更新遊標
　　　　>.開啟遊標
OPEN mycurcor;    --區域性遊標
OPEN GLOBAL mycurcor;    --GLOBAL 表示指定mycurcor遊標為全域性遊標

如果使用INSENSITIV或者STATIC選項宣告瞭遊標,那麼 OPEN將建立一個臨時表來儲存結果集.如果結果集中任意行的大小超過SQL SERVER表的最大行大小,OPEN將失敗..另外:遊標選項 FOR UPDATE 和 INSENSITIVE 有衝突,不能共存。

-- 宣告一個遊標並使用 INSENSITIVE 選項
DECLARE employee_cursor INSENSITIVE CURSOR FOR
SELECT EmployeeID, Name, Department
FROM Employee
ORDER BY EmployeeID;

-- 開啟遊標，建立臨時表儲存結果集
OPEN employee_cursor;
　　>.讀取遊標中的資料(略,見上面示例)
　　>.關閉遊標(略,見上面示例)
　　>.釋放遊標(略,見上面示例)

>.使用系統過程檢視遊標

　　>.sp_cursor_list
　　>.sp_describe_cursor

相關文章