9、MSSQL游标

一：定义：

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力，迫不得已不需要使用，如果表有主键可以使用WHILE循环。

二：游标生命周期：

声明、打开、使用、关闭、销毁释放。

三：事例

语法：

官网参考： https://docs.microsoft.com/zh-cn/sql/relational-databases/cursors?view=sql-server-ver15 https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15 参考:https://www.cnblogs.com/knowledgesea/p/3699851.html

DECLARE cursor_name CURSOR 
	 [ LOCAL | GLOBAL ] 	--全局、局部
     [ FORWARD_ONLY | SCROLL ]  --游标只能向后移动、游标可以随意移动
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
--READ_ONLY只读、 
--scroll_locks：游标锁定，游标在读取时，数据库会将该记录锁定，以便游标完成对记录的操作
--optimistic：该参数不会锁定游标；此时，如果记录被读入游标后，对游标进行更新或删除不会超过
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
     
--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]     

声明游标

DECLARE <CursorName> CURSOR FOR <SQL查询语句>

打开游标

OPEN <CursorName>

使用游标读取数据@@FETCH_STATUS=0：一切正常；@@FETCH_STATUS=-1：找不到记录；@@FETCH_STATUS=-2：超出记录数；

FETCH NEXT FROM <CursorName> INTO <@变量>;
  WHILE @@FETCH_STATUS=0
   BEGIN
   --用户操作
   --继续读取下一条数据
   FETCH NEXT FROM <CursorName> INTO <@变量>;
   END

关闭和销毁

CLOSE <CursorName>;
DEALLOCATE <CursorName>;

模板：

DECLARE <CursorName> CURSOR FOR <SQL查询语句>
OPEN <CursorName>
FETCH NEXT FROM <CursorName> INTO <@变量>;
  WHILE @@FETCH_STATUS=0
   BEGIN
   --用户操作
   --继续读取下一条数据
   FETCH NEXT FROM <CursorName> INTO <@变量>;
   END
CLOSE <CursorName>;
DEALLOCATE <CursorName>;   

四：测试用例

https://blog.csdn.net/sinat_28984567/article/details/79811887

1：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(22))
Insert #T
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'赵六'
Go
--测试数据结束
DECLARE @id INT , @name NVARCHAR(50)    --声明变量，需要读取的数据
DECLARE cur CURSOR 				--声明游标
FOR
    SELECT id,name FROM    #T
OPEN cur								--打开游标
FETCH NEXT FROM cur INTO @id, @name     --取数据
WHILE ( @@fetch_status = 0 )            --判断是否还有数据
    BEGIN
        SELECT '数据: ' + RTRIM(@id) + @name
        FETCH NEXT FROM cur INTO @id, @name   --这里一定要写取下一条数据
    END
CLOSE cur								--关闭游标
DEALLOCATE cur

测试结果：

2：

--测试数据
if not object_id(N'Tempdb..#T_Student') is null
	drop table #T_Student
Go
Create table #T_Student([id] int,[name] nvarchar(22),age int)
Insert #T_Student
select 1,N'张三',11 union all
select 2,N'李四',12 union all
select 3,N'王五',13 union all
select 4,N'赵六',14
Go

--创建一个游标
declare cursor_stu cursor scroll for
    select id, name, age from #T_Student;
--打开游标
open cursor_stu;
--存储读取的值
declare @id int,
        @name nvarchar(20),
        @age varchar(20);
--读取第一条记录
fetch first from cursor_stu into @id, @name, @age;
--循环读取游标记录
print '读取的数据如下：';
--全局变量
while (@@fetch_status = 0)
begin
    print '编号：' + convert(char(5), @id) + ', 名称：' + @name + ', 年龄：' + @age;
    --继续读取下一条记录
    fetch next from cursor_stu into @id, @name, @age;
end
--关闭游标
close cursor_stu;

--删除游标
deallocate cursor_stu;

五：总结

优点：

1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。

2）提供对基于游标位置的表中的行进行删除和更新的能力。

3）游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。

缺点：

处理大数据量时，效率低下，占用内存大；一般来说，能使用其他方式处理数据时，最好不要使用游标，除非是当你使用while循环,子查询，临时表，表变量,自建函数或其他方式都无法处理某种操作的时候，再考虑使用游标