SQL 游标介绍及使用

游标

　　游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

　　游标是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。一般复杂的存储过程，都会有游标的出现，他的用处主要有：

定位到结果集中的某一行。
对当前位置的数据进行读写。
可以对结果集中的数据单独操作，而不是整行执行相同的操作。
是面向集合的数据库管理系统和面向行的程序设计之间的桥梁

优点　　

对从表中检索出的数据进行操作非常灵活
允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。
提供对基于游标位置的表中的行进行删除和更新的能力。
游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。

缺点

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

生命周期

　　游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

1.声明游标，语法

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

参数说明：

cursor_name：游标名称。
Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
[Local | Global]:默认为local。
Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
Static:静态游标
KeySet：键集游标
Dynamic：动态游标，不支持Absolute提取选项
Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
Read_Only:不能通过游标对数据进行删改。
Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
For Update[of column_name ,....] :定义游标中可更新的列。

2.声明一个动态游标

declare orderNum_02_cursor cursor scroll
for select OrderId from bigorder where orderNum='ZEORD003402'

3.打开游标

--打开游标语法
open [ Global ] cursor_name | cursor_variable_name

cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。

--打开游标
open orderNum_02_cursor

4.提取数据

--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

参数说明：

Frist：结果集的第一行
Prior：当前位置的上一行
Next：当前位置的下一行
Last：最后一行
Absoute n：从游标的第一行开始数，第n行。
Relative n：从当前位置数，第n行。
Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

例子：

--提取数据
fetch first from orderNum_02_cursor
fetch relative 3 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch absolute 4 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch last from orderNum_02_cursor 
fetch prior from orderNum_02_cursor
select * from bigorder where orderNum='ZEORD003402'

结果（对比一下，就明白啦）：

例子：

--提取数据赋值给变量
declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
select @OrderId as id
select * from bigorder where orderNum='ZEORD003402'

结果：

通过检测全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。

这个状态值可以帮你判断提取数据的成功与否。

declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
 begin
   select @OrderId as id
   fetch  next from orderNum_02_cursor into @OrderId  --移动游标
 end

5.利用游标更新删除数据

--游标修改当前数据语法
Update 基表名 Set 列名=值[,...] Where Current of 游标名
--游标删除当前数据语法
Delete 基表名  Where Current of 游标名

6.关闭游标

游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。

--关闭游标语法
close [ Global ] cursor_name | cursor_variable_name
--关闭游标
close orderNum_03_cursor

7.删除游标

删除游标，释放资源

--释放游标语法
deallocate  [ Global ] cursor_name | cursor_variable_name
--释放游标
deallocate orderNum_03_cursor

8.示例

--测试数据
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 STATIC                --声明静态游标
FOR
    SELECT  * FROM    #T
OPEN cur                                --打开游标
FETCH NEXT FROM cur INTO @id, @name     --取数据
WHILE ( @@fetch_status = 0 )            --判断是否还有数据
    BEGIN
        SELECT '数据: ' + RTRIM(@id) + @name
        UPDATE #T SET name='测试' WHERE id=4  --测试静态动态用
        FETCH NEXT FROM cur INTO @id, @name   --这里一定要写取下一条数据
    END
CLOSE cur                                --关闭游标
DEALLOCATE cur

结果如下，我们可以看到ID是4的数据没有改变，依然是赵六，而不是UPDATE之后的测试：

DECLARE @id INT , @name NVARCHAR(50)    --声明变量，需要读取的数据
DECLARE cur CURSOR                 --去掉STATIC关键字即可
FOR
    SELECT  * FROM    #T
OPEN cur                                --打开游标
FETCH NEXT FROM cur INTO @id, @name     --取数据
WHILE ( @@fetch_status = 0 )            --判断是否还有数据
    BEGIN
        SELECT '数据: ' + RTRIM(@id) + @name
        UPDATE #T SET name='测试' WHERE id=4  --测试静态动态用
        FETCH NEXT FROM cur INTO @id, @name   --这里一定要写取下一条数据
    END
CLOSE cur                                --关闭游标
DEALLOCATE cur

我们看结果，可以看到ID为4的数据已经更改为测试：

原文链接：https://www.cnblogs.com/knowledgesea/p/3699851.html

示例链接：https://blog.csdn.net/sinat_28984567/article/details/79811887

SQL 游标介绍及使用

游标

优点

缺点

分类

生命周期

优点　　