SQL SERVER 从入门到精通 第5版 第三篇 高级应用 第12章 游标的使用 读书笔记

 第十二章 游标的使用

　　>.游标的概述

　　游标是一种数据库对象，用于在SQL中处理(SELECT的)查询结果集。它允许逐行地访问查询结果集的数据，以进行一系列操作，如更新、删除或插入数据。游标通常用于存储过程或触发器中，用于对数据进行逐行处理。通过游标，可以实现对结果集的逐行处理，可以方便地进行数据处理和操作。游标在数据库中的使用可能会导致性能问题，应该谨慎使用。

　　>.游标的实现

　　一个完整的游标由五个部分组成,并且遵循以下顺序:

　　　　1.声明游标

　　　　2.打开游标

　　　　3.使用游标

　　　　4.关闭游标

　　　　5.释放游标

　　>.游标的类型

　　　　>.静态游标

　　　　　　静态游标的结果集是保存在tempdb中的,相当于是对原数据的一个快照,只读,不能对原数据进行修改.不能看到数据的更新. 特点: 只读,消耗资源少,适用于数据量不大且不需要频繁更新的情况。

 

　　　　>.动态游标

　　　　　　与静态游标相反,动态游标是可读写的,它会反映出用户对结果集所做的所有修改,包括增删改.特点:读写,消耗资源多,适用于需要频繁更新的情况。

　　　　>.只进游标

　　　　　　静态游标和动态游标都是可以前后滚动的,而只进游标只能向前滚动游标，不能在结果集中反向移动。一旦过了记录，就无法再次检索。注意:只进游标除了不能向后移动,其他的基本上与动态游标无异,也就是说,它是可读写基础表数据的!

　　　　>.键集驱动游标

　　　　　　键集驱动游标介于静态游标与动态游标之间.换句话说就是,它同时具备了静态游标与动态游标的部分特点.当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到tempdb中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新打开游标.适用于需要对游标进行大量查找和跳跃的情况。

 

　　>.游标的基本操作

　　　　遍历游标建议使用 FETCH NEXT方法.

　　　　>.声明游标　　　　

--语法: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 -- 当获取到数据时返回0,否则返回-1,这里的循环条件也可以用全局变量 @@cursor_rows 来统计结果集的行数,之后再从1到尾遍历
    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

举例二:

DECLARE mycurcor SCROLL CURSOR FOR    --注意这里的SCROLL 参数,如果缺省的话,结果栏里将看不到结果
SELECT *
FROM dbo.student
WHERE sex = '男';

BEGIN TRY
    OPEN mycurcor;
    DECLARE @i INT,
            @ROWS INT;
    SET @i = 0;    --游标是从0开始的
    SET @ROWS = @@CURSOR_ROWS;   --结果集的行数

    WHILE (@i < @ROWS)   --遍历游标
    BEGIN
        FETCH NEXT FROM mycurcor;   --获取数据
        SET @i += 1;    --变量自增
    END;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();   --捕获错误
END CATCH;
CLOSE mycurcor;     --关闭游标
DEALLOCATE mycurcor; --释放游标

结果如下:

  

　　　　声明游标时,可以在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