SQL Server中的游标介绍
游标是面向行的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能。
在性能上，游标会吃更多的内存，减少可用的并发，占用带宽，锁定资源，当然还有更多的代码量。用一个比喻来说明为什么游标会占用更多的资源。当你从ATM机取款的时候，是一次取1000的效率更高呢，还是10次100呢？
游标是非常邪恶的一种存在，使用游标经常会比使用面向集合的方法慢2-3倍，当游标定义在大数据量时，这个比例还会增加。如果可能，尽量使用while,子查询，临时表，函数，表变量等来替代游标，记住，游标永远只是你最后无奈之下的选择，而不是首选。
既然游标那么多缺点，为什么要学习游标呢？
游标的定义语法：
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 ] ] ]
[;]
一、定义游标
在T-SQL中，定义一个游标可以使非常简单，也可以相对复杂，这主要取决于游标的参数。而游标的参数设置取决于你对游标原理的了解程度。
游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以SELECT开始的数据集上的。
游标分为游标类型和游标变量。
游标变量支持两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”。
注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”。
两种定义方式如下:
--定义后直接赋值
declare test_Cursor cursor for
select * from Person;
--先定义后赋值
declare @TEST_Cursor2 cursor;
set @TEST_Cursor2 = cursor for
select * from Person;
参数解释：
1、LOCAL和GLOBAL二选一
如果不指定游标作用域，默认作用域为GLOBAL。
--全局游标，跨GLOBAL
declare test_Cursor cursor global for
select * from Person;
--局部游标，跨LOCAL
declare test_Cursor2 cursor local for
select * from Person;
go --用GO结束上面的作用域
open test_Cursor;
open test_Cursor2; --此行代码报错，报游标不存在，因此可以理解局部游标不跨批处理，批处理结束后，将被隐式释放，无法在其他批处理中调用
2、FORWARD_ONLY 和 SCROLL 二选一
--不加参数，默认为Forward_Only
declare test_Cursor cursor for
select * from Person;
--加Forward_Only
declare test_Cursor2 cursor forward_only for
select * from Person;
--加SCROLL
declare test_Cursor3 cursor scroll for
select * from Person;
open test_Cursor;
open test_Cursor2;
open test_Cursor3;
fetch last from test_Cursor; --报错 fetch: 提取类型 last 不能与只进游标一起使用。
fetch last from test_Cursor2; --报错 fetch: 提取类型 last 不能与只进游标一起使用。
fetch last from test_Cursor3; --正确执行
3、游标的分类：STATIC、 KEYSET 、DYNAMIC 和 FAST_FORWARD 四选一
这四个关键字是游标所在数据集所反映的表数据和游标读取出数据的关系
4、READ_ONLY 、 SCROLL_LOCKS 和 OPTIMISTIC 三选一
5、For Update[of column_name ,....] :定义游标中可更新的列。
二、打开游标
当定义完游标后，游标需要打开后使用，只需一行代码便可打开游标:
OPEN test_Cursor
注意，当全局游标和局部游标变量重名时，默认会打开局部变量游标。
三、使用游标
1、利用游标提取数据
游标的使用分为两部分，一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作。
支持6种移动导航，分别为：
例如：
declare test_Cursor cursor scroll for
select name from Person;
open test_Cursor;
declare @c nvarchar(10);
--取下一行
fetch next from test_Cursor into @c;
print @c;
--取最后一行
fetch last from test_Cursor into @c;
print @c;
--取第一行
fetch first from test_Cursor into @c;
print @c;
--取上一行
fetch prior from test_Cursor into @c;
print @c;
--取第三行
fetch absolute 3 from test_Cursor into @c;
print @c;
--取相对目前来说上一行
fetch relative -1 from test_Cursor into @c;
print @c;
对于未指定SCROLL选项的游标来说(未指定，则是只进游标)，只支持NEXT取值。
游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的。
当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：
游标总记录数 @@CURSOR_ROWS
例如：
declare test_Cursor cursor fast_forward for
select id, name from Person;
open test_Cursor;
declare @id int;
declare @name nvarchar(10);
fetch next from test_Cursor into @id, @name;
while @@FETCH_STATUS = 0
begin
print @id;
print @name;
fetch next from test_Cursor into @id, @name;
end;
close test_Cursor;
deallocate test_Cursor;
2、利用游标更新删除数据
游标修改当前行数据语法：
Update 基表名 Set 列名=值[,...] Where Current of 游标名
游标删除当前数行据语法：
Delete 基表名 Where Current of 游标名
举例：
---1.声明游标
declare orderNum_03_cursor cursor scroll for
select OrderId, userId from bigorder where orderNum = 'ZEORD003402';
--2.打开游标
open orderNum_03_cursor;
--3.声明游标提取数据所要存放的变量
declare @OrderId int, @userId varchar(15);
--4.定位游标到哪一行
fetch first from orderNum_03_cursor into @OrderId, @userId; -- into的变量数量必须与游标查询结果集的列数相同
while @@fetch_status = 0 --提取成功，进行下一条数据的提取操作
begin
if @OrderId = 122182
begin
update bigorder set UserId = '123' where current of orderNum_03_cursor; --修改当前行
end;
if @OrderId = 154074
begin
delete bigorder where current of orderNum_03_cursor; --删除当前行
end;
fetch next from orderNum_03_cursor
into @OrderId, @userId; --移动游标
end;
close orderNum_03_cursor;
deallocate orderNum_03_cursor;
四、关闭游标
在游标使用完之后，一定要记得关闭,只需要一行代码:CLOSE+游标名称
close test_Cursor
五、释放游标
当游标不再需要被使用后，释放游标，只需要一行代码:DEALLOCATE+游标名称
deallocate test_Cursor
六、对于游标一些优化建议
到此这篇关于SQL Server游标的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持脚本之家。
您可能感兴趣的文章: