游标的创建与应用

游标是SQL Server的一种数据访问机制，它允许用户访问单独的数据行。用户可以对每一行进行单独处理，从而降低系统开销和潜在的阻隔情况，用户也可以使用这些数据生成SQL代码并立即执行。
游标是一种处理数据的方法，主要用于存储过程、触发器和T-SQL脚本中，它们使结果集的内容可用于其他T-SQL语句。
1. 游标的优点
- 允许程序对由SELECT查询语句返回的行集中的每一行执行相同或不同的操作，而不是对整个集合执行同一操作。
- 提供对基于游标位置的表中的行进行删除和更新能力。
- 游标作为数据库管理系统和应用程序设计之间的桥梁，将两种处理方式连接起来。

一.游标的基本操作

对游标的操作主要有以下几个：声明游标、打开游标、读取游标中的数据、关闭游标和释放游标。
1.游标创建

--声明游标
DECLARE cursor_employee CURSOR FOR
SELECT e_name,e_salary FROM employee
--打开游标
OPEN cursor_employee
--读取游标中的数据
--FETCH NEXT FROM将游标向下移1行，获取数据
FETCH NEXT FROM cursor_employee
--@@FETCH_STATUS=0语句成功，=-1语句失败或此行不在结果集中，=-2被提取行不存在
WHILE @@FETCH_STATUS=0
BEGIN
--FETCH NEXT FROM将游标向下移1行，获取数据
    FETCH NEXT FROM cursor_employee
END
--关闭游标
CLOSE cursor_employee
--释放游标
DEALLOCATE cursor_employee

用两次 “FETCH NEXT FROM ” 是因为，第一次只是用来判断 @@FETCH_STATUS的，后面一次 “FETCH NEXT FROM ” 才是 loop用到的！也就是后面一次是在 BEGIN END之间，是被反复执行的。。每次读取一行！
在使用完游标后，一定要将其关闭和删除，关闭游标的作用是释放游标和数据库的连接；删除游标是将其从内存中删除，删除将释放系统资源
2.游标的运用

--声明游标变量
DECLARE @VarCursor Cursor
--声明游标
DECLARE cursor_employee CURSOR FOR
SELECT e_name,e_salary FROM employee
--打开游标
OPEN cursor_employee
--为游标变量赋值
SET @VarCursor = cursor_employee
FETCH NEXT FROM @VarCursor
--判断FETCH语句是否执行成功
WHILE @@FETCH_STATUS = 0
BEGIN
--读取游标变量中的数据
    FETCH NEXT FROM @VarCursor
END
--关闭游标
CLOSE @VarCursor
--释放游标
DEALLOCATE @VarCursor

3.用游标为变量赋值

DECLARE @employeeName VARCHAR(20),@employeeSalary DECIMAL(8,2)
DECLARE cursor_employee CURSOR FOR
SELECT e_name,e_salary FROM employee
WHERE dept_no = 30
ORDER BY e_salary DESC;
OPEN cursor_employee
FETCH NEXT FROM cursor_employee
INTO @employeeName,@employeeSalary
PRINT '员工名称'+'工资'
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @employeeName+' '+STR(@employeeSalary,8,2)
    FETCH NEXT FROM cursor_employee
    INTO @employeeName,@employeeSalary
END
CLOSE cursor_employee
DEALLOCATE cursor_employee

4.用游标修改数据

--需要修改e_no=1008用户的数据，给变量@NO赋值
DECLARE @eNO INT,@NO INT= 1008
DECLARE cur_employee CURSOR FOR
SELECT e_no FROM employee 
ORDER BY e_no;
OPEN cur_employee
--读取游标中的值，并把游标中的值e_no赋给@eNO变量
FETCH NEXT FROM cur_employee INTO @eNO
WHILE @@FETCH_STATUS = 0
BEGIN
--判断获取的eNO值是否等于需要修改@NO的值，如果是就跳转到UPDATE语句中，否则跳出
    IF @eNO=@NO
    BEGIN
        UPDATE employee SET e_salary = 3700 WHERE e_no=@eNO
    END
    FETCH NEXT FROM cur_employee INTO @eNO
END
CLOSE cur_employee
DEALLOCATE cur_employee
SELECT * FROM employee

5.用游标删除数据

--需要修改e_no=1002用户的数据，给变量@NO赋值
DECLARE @eNO INT,@NO INT= 1002
DECLARE cur_delete CURSOR FOR
SELECT e_no FROM employee 
ORDER BY e_no;
OPEN cur_delete
--读取游标中的值，并把游标中的值e_no赋给@eNO变量
FETCH NEXT FROM cur_delete INTO @eNO
WHILE @@FETCH_STATUS = 0
BEGIN
--判断获取的eNO值是否等于需要修改@NO的值，如果是就跳转到DELETE语句中，否则跳出
    IF @eNO=@NO
    BEGIN
        DELETE employee WHERE e_no=@eNO
    END
    FETCH NEXT FROM cur_delete INTO @eNO
END
CLOSE cur_delete
DEALLOCATE cur_delete

二.使用系统存储过程管理游标

1.sp_cursor_list存储过程

DECLARE cursor_employee CURSOR FOR
SELECT e_no,e_name,e_salary FROM employee
--打开游标
OPEN cursor_employee
--声明游标变量
DECLARE @Report CURSOR
--执行sp_cursor_list存储过程，将结果保存到@Report游标变量中
EXEC sp_cursor_list @cursor_return=@Report OUTPUT,@cursor_scope = 2
FETCH NEXT FROM @Report
--输出游标变量中的每一行
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    FETCH NEXT FROM cursor_employee
END
--关闭游标
CLOSE cursor_employee
--释放游标
DEALLOCATE cursor_employee

2.sp_describe_cursor存储过程

DECLARE testcur CURSOR FOR
SELECT e_no,e_name,e_salary FROM employee
--打开游标
OPEN testcur
--声明游标变量
DECLARE @Report CURSOR
--执行sp_cursor_list存储过程，将结果保存到@Report游标变量中
EXEC sp_describe_cursor @cursor_return =@Report OUTPUT,
                        @cursor_source =N'global',@cursor_identity =N'testcur'
FETCH NEXT FROM @Report
--输出游标变量中的每一行
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    FETCH NEXT FROM testcur
END
--关闭游标
CLOSE testcur
--释放游标
DEALLOCATE testcur