SQL Server-处理结果集的循环机制——游标

相关文章推荐

奔跑的西瓜 · SQL ...· 2 天前 ·

率性的春卷 · LiquiBase启动慢，被锁_flowab ...· 10 月前 ·

朝气蓬勃的豆浆 · 学习ASP.NET Core ...· 1 年前 ·

博学的警车 · Python查找不限层级Json数据中某个k ...· 1 年前 ·

1、游标的概念

结果集，结果集就是select查询之后可能返回多条记录，返回的所有行数据的集合。这时需要对数据进行处理以每次处理一行或一部分行，通过使用游标来逐条读取查询结果集中的记录。

游标则是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。

一般复杂的存储过程，都会有游标的出现，他的用处主要有：

允许定位在结果集的特定行。

从结果集的当前位置检索一行或一部分行。

支持对结果集中当前位置的行进行数据修改，而不是所有行执行相同的操作。

为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句

是面向集合的数据库管理系统和面向行的程序设计之间的桥梁

游标是一种处理数据的方法，主要用于存储过程，触发器和 T_SQL脚本中，它们使结果集的内容可用于其它T_SQL语句。在查看或处理结果集中向前或向后浏览数据的功能。类似与C语言中的指针，它可以指向结果集中的任意位置，当要对结果集进行逐条单独处理时，必须声明一个指向该结果集中的游标变量。

SQL Server 中的数据操作结果都是面向集合的，并没有一种描述表中单一记录的表达形式，除非使用WHERE子句限定查询结果，使用游标可以提供这种功能，并且游标的使用和操作过程更加灵活、高效。

2、游标的优点

SELECT 语句返回的是一个结果集，但有时候应用程序并不总是能对整个结果集进行有效地处理，游标便提供了这样一种机制，它能从包括多条记录的结果集中每次提取一条记录，游标总是与一跳SQL选择语句相关联，由结果集和指向特定记录的游标位置组成。使用游标具有一下优点：

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

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

(3).游标作为数据库管理系统和应用程序设计之间的桥梁，将两种处理方式连接起来。

3.游标的语法

3.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 ,....] :定义游标中可更新的列。

静态游标（static）： 当游标被建立时，将会创建 FOR 后面的 SELECT 语句所包含数据集的副本存入 tempdb 数据库中，任何对于底层表内数据的更改不会影响到游标的内容。

即打开游标之后，对游标查询的数据表的数据进行增删改操做之后，静态游标中 select 的数据依旧显示的为没有操作之前的数据。

如果想与操作之后的数据一致，则关闭之后重新打开游标即可。

动态游标（dynamic）： 动态游标与静态游标相反，当底层数据表的数据更改时，游标的内容也随之得到反映，在下一次 fetch 中，行的数据值、顺序和成员身份在每次提取时都会更改。

只进游标（fast_forward）： 只进游标不支持滚动，只支持从头到尾按顺序读取数据，对数据执行增删改操作，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。

键集游标（keyset）： 打开键集驱动游标时，结果集的每行数据被一组唯一标识符进行标识，被标识的列做删改时，用户滚动游标是可见的，其他用户执行的插入是不可见的（不能通过 Transact-SQL 服务器游标执行插入）。如果删除了某行，尝试读取的行返回 @@FETCH_STATUS为-2。从游标外部更新键值类似于删除旧行后再插入新行。具有新值的行不可见，并且尝试提取具有旧值的行返回 @@FETCH_STATUS为-2。如果通过指定 WHERE CURRENT OF 子句来通过游标执行更新，则新值可见

3.3 --提取游标并赋值

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行。（n 为正数时是对于目前行向前，为负数时是对于目前行向后）

Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中

创建测试数据——

CREATE TABLE [dbo].[bank3](
[userName] [varchar](10) NOT NULL,
[cardID] [varchar](10) NOT NULL,
[currentMoney] [float] NOT NULL,
[createID] [varchar](20)
) ON [PRIMARY]
GO

insert into bank3 values('XX1',11002,'1.5','202111');
insert into bank3 values('XX2',11002,'2.7','202111');
insert into bank3 values('XX3',11002,'3.7','202109');
insert into bank3 values('XX3',11004,'4.2','202110');

CREATE TABLE [dbo].[bank2](
[userName] [varchar](10) NOT NULL,
[cardID] [varchar](10) NOT NULL,
[currentMoney] [float] NOT NULL,
[createID] [varchar](20)
) ON [PRIMARY]
GO

insert into bank2 values('XX1',11002,'444.78','202103');
insert into bank2 values('XX3',11004,'555.99','202109');
insert into bank2 values('XX4',11001,'333.33','202111');

-----------------------------------------------------------------

select * from bank2
SELECT *FROM BANK3
select * from bank202109
SELECT *FROM BANK202111
SELECT *FROM BANK202110

--------------------------------------------------------------

使用游标对数据的插入操作——

方式1：查询表bank2的数据是否在表bank3中存在，如果不存在则插入到表bank3中

declare @varCursor varchar(22), --定义输出变量
@cardid varchar(22),
@currentmoney float,
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float

declare @xSQL varchar(200)

declare cursor_bank2 cursor for --创建游标
select username,currentmoney,cardid from bank2 ORDER BY CURRENTMONEY

open cursor_bank2 --打开游标

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --从游标变量中读取值,并赋值

while(@@FETCH_STATUS=0) --游标读取下一条数据是否成功，等于0的时候，游标查询的结果有，游标就会将查询到的数据直接存入游标捕捉数据的变量,@@fetch_status值的改变是通过fetch next from实现的
begin

if NOT exists(select *from bank3 where CARDID=@cardid ) --查询表BANK3是否包含有游标值的此条数据，如果没有就执行插入游标查询表BANK2的这一条数据
BEGIN
set @xSQL ='insert into bank3 values ('''+@varCursor+''','''+@cardid+''','''+cast(@currentmoney as varchar(20))+''') '
exec(@xSQL);
END
else
BEGIN
print ('已存在数据')
END

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --取下一行数据，否则@@fetch_status永远等于0，则While进入死循环

close cursor_bank2 --关闭游标

deallocate cursor_bank2 --释放游标

------------------------------------------------------------------------------------------------------------------------------------------

方式2：将表bank3数据按 createID字段分类创建对应表，并把该分类数据写入对应分类表内

begin

declare @biao varchar(100),@sql varchar(1000),@tempentinfo1 varchar(100)
set @tempEntInfo1 ='bank'
set @sql=+@tempEntInfo1+'%'
declare c cursor for select name from sysobjects where type='u' and name like @sql and len(name)='10'
set @sql='drop table '
open c
fetch c into @biao
while @@fetch_status=0
begin
set @sql=@sql+@biao
exec(@sql)
set @sql='drop table '
fetch c into @biao
end
close c
deallocate c

------------------------------------------------------
declare @varCursor varchar(22), --定义输出变量
@cardid varchar(22),
@currentmoney float,
@createID varchar(22),
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float,
@createID1 varchar(22)

declare @xSQL varchar(200),
@createSQL VARCHAR(200),
@deleteSQL VARCHAR(200)

declare @count int,@tempEntInfo VARCHAR(200)

declare cursor_bank2 cursor for --创建游标
select username,currentmoney,cardid,createID from bank3 ORDER BY CURRENTMONEY

open cursor_bank2 --打开游标

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --从游标变量中读取值,并赋值

while(@@FETCH_STATUS=0) --游标读取下一条数据是否成功，等于0的时候，游标查询的结果有，游标就会将查询到的数据直接存入游标捕捉数据的变量,@@fetch_status值的改变是通过fetch next from实现的
begin
set @tempEntInfo ='bank'+@createID+''
select @count=COUNT(*) from sysobjects where name=@tempEntInfo -- select COUNT(*) from sysobjects where name='bank202111'
if (@count=0)
begin
SET @createSQL='CREATE TABLE bank'+@createID+' ([userName] [varchar](10) NOT NULL,[cardID] [varchar](10) NOT NULL, [currentMoney] [float] NOT NULL, [createID] [varchar](20)) ON [PRIMARY]'
exec (@createSQL)
end
else
begin
print ('表已经存在')
end;
begin
set @xSQL ='insert into bank'+@createID+' values ('''+@varCursor+''','''+@cardid+''','''+cast(@currentmoney as varchar(20))+''','''+@createID+''') '
exec(@xSQL);
end

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --取下一行数据，否则@@fetch_status永远等于0，则While进入死循环

close cursor_bank2 --关闭游标

deallocate cursor_bank2 --释放游标

----------------------------------------------------------------------------------------------------------------------------------------------

使用游标对数据的删除操作——

方式1：查询表bank2内的数据不包含在表bank3里面时，就删除对应该条数据

declare @varCursor varchar(22), --定义输出变量
@cardid varchar(22),
@currentmoney float,
@createID varchar(22),
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float,
@createID1 varchar(22)

declare @xSQL varchar(200),
@createSQL VARCHAR(200),
@deleteSQL VARCHAR(200)

declare @count int,@tempEntInfo VARCHAR(200)

declare cursor_bank2 cursor for --创建游标
select username,currentmoney,cardid,createID from bank2 ORDER BY CURRENTMONEY

open cursor_bank2 --打开游标

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --从游标变量中读取值,并赋值

while(@@FETCH_STATUS=0) --游标读取下一条数据是否成功，等于0的时候，游标查询的结果有，游标就会将查询到的数据直接存入游标捕捉数据的变量,@@fetch_status值的改变是通过fetch next from实现的
begin
if NOT exists(select *from bank3 where cardid=@cardid ) --如果查询的游标值不包含在表BANK3里面此条数据就执行删除
begin
delete from bank2 where cardid=@cardid --执行更新操作
end
else
begin
print '已包含'
end

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid,@createID --取下一行数据，否则@@fetch_status永远等于0，则While进入死循环

close cursor_bank2 --关闭游标

deallocate cursor_bank2 --释放游标

----------------------------------------------------------------------------------------------------------------------------------------------

使用游标对数据的更新操作——

方式1：判断数据是否存在

declare @varCursor varchar(22), --定义输出变量
@cardid varchar(22),
@currentmoney float

declare @temp_cu table(currentmoney float)

declare cursor_bank cursor for --创建游标
select username,currentmoney,cardid from bank3 ORDER BY CURRENTMONEY

open cursor_bank --打开游标

fetch next from cursor_bank INTO @VARCURSOR,@currentmoney,@cardid --从游标变量中读取值,并赋值

if NOT exists(select *from bank2 where currentmoney=@currentmoney ) --如果查询的游标值不包含在表BANK2里面更新的此条数据就输出XX，包含就执行更新语句
begin
update bank2 set currentMoney =@currentmoney where username=@varCursor and cardid=@cardid --执行更新操作
end
else
begin
print '已包含'
end

fetch next from cursor_bank INTO @VARCURSOR,@currentmoney,@cardid --取下一行数据，否则@@fetch_status永远等于0，则While进入死循环

close cursor_bank --关闭游标

deallocate cursor_bank --释放游标

--------------------------------------------------------------------------------------------------------------------------------------------

方式2：双游标嵌套查询

declare @varCursor varchar(22), --定义输出变量
@cardid varchar(22),
@currentmoney float,
@varCursor1 varchar(22),
@cardid1 varchar(22),
@currentmoney1 float

declare @xSQL varchar(200)

--declare @temp_cu table(currentmoney float)

declare cursor_bank2 cursor for --创建游标
select username,currentmoney,cardid from bank3 ORDER BY CURRENTMONEY

open cursor_bank2 --打开游标

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --从游标变量中读取值,并赋值

while(@@FETCH_STATUS=0) --游标读取下一条数据是否成功，等于0的时候，游标查询的结果有，游标就会将查询到的数据直接存入游标捕捉数据的变量,@@fetch_status值的改变是通过fetch next from实现的
begin
--再定义另一游标循环多表操作判断
declare cursor_bank3 cursor for

select currentmoney,username,cardid from bank2 ORDER BY CURRENTMONEY

open cursor_bank3

fetch next from cursor_bank3 into @currentmoney1,@varCursor1,@cardid1

while(@@fetch_status=0)
begin
if (@varCursor=@varCursor1)
BEGIN
--update bank3 set currentMoney =@currentmoney where username=@varCursor and cardid=@cardid
set @xSQL ='update bank2 set currentMoney='''+cast(@currentmoney as varchar(20))+''' where username= '''+cast(@varCursor as varchar(20))
+''' and cardid= '''+cast(@cardid as varchar(20))+''' '
--print (@xSQL)
exec(@xSQL);
END
else
BEGIN
print ('不存在')
END
fetch next from cursor_bank3 into @currentmoney1,@varCursor1,@cardid1
end
close cursor_bank3
deallocate cursor_bank3

fetch next from cursor_bank2 INTO @VARCURSOR,@currentmoney,@cardid --取下一行数据，否则@@fetch_status永远等于0，则While进入死循环

close cursor_bank2 --关闭游标

deallocate cursor_bank2 --释放游标

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

对游标的一些优化建议：

不得不使用游标时，如果不需要用游标写数据，就设置成只读游标，如游标指针不需要上移，就用只进游标。

用完之后一定要关闭、释放游标。

尽量不要在大量数据上定义游标。

尽量不要使用游标上执行更新、删除操作

尽量使用Fast_Forward关键字定义游标

（2）游标是在while的基础上实现循环的功能，但是语法较复杂，在数据量较少时效率比while高，但如果数据量很多的情况下一般推荐使用游标，因为这会导致效率很慢且需要耗大量内存；

如果可能尽量使用while、子查询、临时表、函数、表变量等来替换游标。