- 游标的定义
- 有哪些游标
- 游标的使用
- 使用游标的优缺点
游标的定义
游标是SQL 的一种数据访问机制。可以将游标简单的看成是查询的结果集的一个指针,
可以根据需要在结果集上面来回滚动,浏览需要的数据。
有哪些游标
根据游标检测结果集变化的能力和消耗资源的情况不同,SQL Server支持的API服务器游标分为一下4种:
静态游标:
静态游标的结果集,在游标打开的时候建立在TempDB中,不论你在操作游标的时候,如何操作数据库,游标中的数据集都不会变。例如你在游标打开的时候,对游标查询的数据表数据进行增删改,操作之后,静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致,则重新关闭打开游标即可。
动态游标:
这个则与静态游标相对,滚动游标时,动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新,他们将立即可见。在游标外部所做的更新直到提交时才可见。
只进游标:
只进游标不支持滚动,只支持从头到尾顺序提取数据,数据库执行增删改,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。
键集驱动游标:
打开键集驱动游标时,该有表中的各个成员身份和顺序是固定的。打开游标时,结果集这些行数据被一组唯一标识符标识,被标识的列做删改时,用户滚动游标是可见的,如果没被标识的列增该,则不可见,比如insert一条数据,是不可见的,若可见,须关闭重新打开游标。
静态游标在滚动时检测不到表数据变化,但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化,但消耗的资源却较多。键集驱动游标则处于他们中间,所以根据需求建立适合自己的游标,避免资源浪费。。
游标的使用
游标的生命周期包含有五个阶段:声明游标、打开游标、读取游标数据、关闭游标、释放游标。
声明游标
declare cur_Set_Lve cursor
for
select id,Price from Books
打开游标
open cur_Set_Lve
读取游标数据
declare @id int
declare @price decimal(18,0)
--获取数据 id,Price
fetch next from cur_Set_Lve into @id,@price
关闭游标
close cur_Set_Lve
释放游标
deallocate cur_Set_Lve
示例
--根据价格设置书的等级,价格小于50便宜
--价格 50-100中等
--价格大于100的贵
--定义一个游标
declare cur_Set_Lve cursor
for
select id,Price from Books
--打开游标
open cur_Set_Lve
declare @id int
declare @price decimal(18,0)
--获取数据 id,Price
fetch next from cur_Set_Lve into @id,@price
--循环获取
while(@@FETCH_STATUS=0)
--@@fetch_status是MicroSoft SQL SERVER的一个全局变量
--其值有以下三种,分别表示三种不同含义:【返回类型integer】
--0 FETCH 语句成功
---1 FETCH 语句失败或此行不在结果集中
---2 被提取的行不存在
begin
if(@price<50)
update Books set Levels='便宜' where ID=@id
else if(@price<100)
update Books set Levels='中等' where ID=@id
else
update Books set Levels='贵' where ID=@id
fetch next from cur_Set_Lve into @id,@price
end
--关闭游标
close cur_Set_Lve
--释放游标
deallocate cur_Set_Lve
使用游标的好处
优点:
1)允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作,而不是对整个行集合执行同一个操作。
2)提供对基于游标位置的表中的行进行删除和更新的能力。
3)游标实际上作为面向集合的数据库管理系统(RDBMS)和面向行的程序设计之间的桥梁,使这两种处理方式通过游标沟通起来。
缺点:
处理大数据量时,效率低下,占用内存大;
一般来说,能使用其他方式处理数据时,最好不要使用游标,除非是当你使用while循环,子查询,临时表,表变量,自建函数或其他方式都无法处理某种操作的时候,再考虑使用游标。