SQL Server游标 #

游标特点：
├── 逐行处理数据
├── 可以定位到特定行
├── 支持数据修改
└── 性能开销大

游标生命周期：
1. DECLARE CURSOR - 声明游标
2. OPEN CURSOR - 打开游标
3. FETCH - 获取数据
4. CLOSE CURSOR - 关闭游标
5. DEALLOCATE CURSOR - 释放游标

游标范围：
├── LOCAL - 局部游标（当前批处理）
└── GLOBAL - 全局游标（当前连接）

-- 简单游标声明
DECLARE cur_users CURSOR FOR
SELECT id, name, email FROM users WHERE status = 1;

-- 完整游标声明
DECLARE cur_users CURSOR
LOCAL
STATIC
READ_ONLY
FOR
SELECT id, name, email FROM users WHERE status = 1;

-- 打开游标
OPEN cur_users;

-- 检查游标状态
IF @@CURSOR_ROWS > 0
    PRINT 'Cursor opened with ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows';
ELSE
    PRINT 'Cursor is empty or not opened';

-- 声明变量
DECLARE @id INT;
DECLARE @name NVARCHAR(50);
DECLARE @email VARCHAR(100);

-- 获取下一行
FETCH NEXT FROM cur_users INTO @id, @name, @email;

-- 检查获取状态
IF @@FETCH_STATUS = 0
    PRINT 'ID: ' + CAST(@id AS VARCHAR) + ', Name: ' + @name;
ELSE IF @@FETCH_STATUS = -1
    PRINT 'No more rows';
ELSE IF @@FETCH_STATUS = -2
    PRINT 'Row does not exist';

-- @@FETCH_STATUS值：
-- 0：成功
-- -1：超出结果集
-- -2：行不存在

-- 完整游标遍历
DECLARE @id INT;
DECLARE @name NVARCHAR(50);

DECLARE cur_users CURSOR FOR
SELECT id, name FROM users WHERE status = 1;

OPEN cur_users;

FETCH NEXT FROM cur_users INTO @id, @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'ID: ' + CAST(@id AS VARCHAR) + ', Name: ' + @name;
    
    FETCH NEXT FROM cur_users INTO @id, @name;
END

CLOSE cur_users;
DEALLOCATE cur_users;

-- 关闭游标
CLOSE cur_users;

-- 释放游标
DEALLOCATE cur_users;

-- 检查游标是否存在
IF CURSOR_STATUS('global', 'cur_users') >= 0
    PRINT 'Cursor exists';

-- 声明滚动游标
DECLARE cur_users SCROLL CURSOR FOR
SELECT id, name FROM users ORDER BY id;

OPEN cur_users;

-- 获取第一行
FETCH FIRST FROM cur_users;

-- 获取最后一行
FETCH LAST FROM cur_users;

-- 获取下一行
FETCH NEXT FROM cur_users;

-- 获取上一行
FETCH PRIOR FROM cur_users;

-- 获取绝对位置（第3行）
FETCH ABSOLUTE 3 FROM cur_users;

-- 获取相对位置（当前位置+2）
FETCH RELATIVE 2 FROM cur_users;

CLOSE cur_users;
DEALLOCATE cur_users;

-- 声明可更新游标
DECLARE cur_users CURSOR FOR
SELECT id, name, salary FROM users WHERE status = 1
FOR UPDATE OF salary;

OPEN cur_users;

DECLARE @id INT;
DECLARE @name NVARCHAR(50);
DECLARE @salary DECIMAL(10,2);

FETCH NEXT FROM cur_users INTO @id, @name, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 更新当前行
    UPDATE users 
    SET salary = @salary * 1.1 
    WHERE CURRENT OF cur_users;
    
    FETCH NEXT FROM cur_users INTO @id, @name, @salary;
END

CLOSE cur_users;
DEALLOCATE cur_users;

-- STATIC游标（快照）
DECLARE cur_static CURSOR
STATIC
FOR
SELECT id, name FROM users;

-- DYNAMIC游标（实时）
DECLARE cur_dynamic CURSOR
DYNAMIC
FOR
SELECT id, name FROM users;

-- KEYSET游标
DECLARE cur_keyset CURSOR
KEYSET
FOR
SELECT id, name FROM users;

-- FORWARD_ONLY游标
DECLARE cur_forward CURSOR
FORWARD_ONLY
FOR
SELECT id, name FROM users;

-- 获取游标行数
OPEN cur_users;
SELECT @@CURSOR_ROWS AS cursor_rows;

-- 返回值：
-- -m：异步填充，已获取m行
-- -1：动态游标，行数不确定
-- 0：未打开或无行
-- n：已填充n行

-- 获取获取状态
FETCH NEXT FROM cur_users;
SELECT @@FETCH_STATUS AS fetch_status;

-- 返回值：
-- 0：成功
-- -1：超出结果集
-- -2：行不存在

-- 检查游标状态
SELECT CURSOR_STATUS('global', 'cur_users') AS status;

-- 返回值：
-- 1：已打开，有行
-- 0：已打开，无行
-- -1：已关闭
-- -2：不适用
-- -3：不存在

-- 查看所有游标
EXEC sp_cursor_list @cursor_return = 0, @cursor_scope = 1;
-- @cursor_scope: 1=全局, 2=局部, 3=全部

-- 查看游标详细信息
DECLARE @cursor_report CURSOR;
EXEC sp_describe_cursor @cursor_return = @cursor_report OUTPUT, @cursor_source = 'global', @cursor_identity = 'cur_users';

-- 使用游标（不推荐）
DECLARE cur_users CURSOR FOR SELECT id FROM users;
OPEN cur_users;
DECLARE @id INT;
FETCH NEXT FROM cur_users INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE users SET status = 1 WHERE id = @id;
    FETCH NEXT FROM cur_users INTO @id;
END
CLOSE cur_users;
DEALLOCATE cur_users;

-- 使用集合操作（推荐）
UPDATE users SET status = 1;

-- 使用WHILE替代游标
DECLARE @id INT;
DECLARE @name NVARCHAR(50);

-- 获取第一行
SELECT TOP 1 @id = id, @name = name 
FROM users 
WHERE status = 1 
ORDER BY id;

WHILE @id IS NOT NULL
BEGIN
    PRINT 'ID: ' + CAST(@id AS VARCHAR) + ', Name: ' + @name;
    
    -- 获取下一行
    SELECT TOP 1 @id = id, @name = name 
    FROM users 
    WHERE status = 1 AND id > @id 
    ORDER BY id;
    
    IF @@ROWCOUNT = 0
        SET @id = NULL;
END

-- 使用CTE处理
WITH numbered_users AS (
    SELECT 
        id,
        name,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users
    WHERE status = 1
)
SELECT * FROM numbered_users;

适合使用游标的场景：
├── 复杂的逐行处理逻辑
├── 需要调用存储过程处理每行
├── 无法用集合操作实现
└── 需要特定的行定位操作

避免使用游标的场景：
├── 简单的数据更新
├── 可以用集合操作替代
├── 大数据量处理
└── 性能敏感的操作

-- 使用FAST_FORWARD游标（性能最优）
DECLARE cur_users CURSOR
FAST_FORWARD
FOR
SELECT id, name FROM users;

-- 使用LOCAL游标
DECLARE cur_users CURSOR
LOCAL FAST_FORWARD
FOR
SELECT id, name FROM users;

-- 尽早关闭和释放游标
OPEN cur_users;
-- 操作
CLOSE cur_users;
DEALLOCATE cur_users;

-- 推荐的游标模板
DECLARE @id INT;
DECLARE @name NVARCHAR(50);

DECLARE cur_users CURSOR
LOCAL FAST_FORWARD
FOR
SELECT id, name FROM users WHERE status = 1;

OPEN cur_users;

FETCH NEXT FROM cur_users INTO @id, @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 处理逻辑
    PRINT 'Processing: ' + @name;
    
    FETCH NEXT FROM cur_users INTO @id, @name;
END

CLOSE cur_users;
DEALLOCATE cur_users;