2-4 游标技术
u
了解游标的基本概念及特点，学会使用游标的基本步骤
u
掌握两个系统全局变量：@@cursor_rows和@@FETCH_ STATUS在定义游标中的作用，掌握在游标中使用FETCH获取游标技术，掌握FETCH语句使用过程中的移动关键词。
u
掌握如何使用游标修改或删除数据
u
了解如何使用递归游标遍历树算法解决家族树的问题
u
了解改进的非游标查询策略
游标实际上是用户在系统中开设的一个数据缓冲区，存放
SQL
语句的执行结果。到目前为止，所有的
SQL
语句是采用面向集合的方法来处理和操作数据的，是针对行的集合进行的操作。应用
WHERE
子句指出的行包含在该集合之中，必须对每一行进行同样的工作。例如：返回集合的条件行（
SELECT
），改变集合中的每一行的同一个列（
UPDATE
），删除集合中的每一个行（
DELETE
）等。
游标具体使用的过程是：游标中存放查询结果的一组记录，用户可以通过移动游标指针逐一访问记录，获得结果，并赋给主变量，交由主语言进一步处理。
游标技术的引入最根本解决的问题是替代了面向集合的数据信息操作方法，试图通过数据结构指针的方法进行数据的定位查询，这就是游标概念引入的目的。它允许每次一行的操作，基于行的内容，可以决定采取的下一步行为。
注意：我们在使用游标之前务必清楚的了解下面的两点：
q 游标是有害的！！在实际测试中，游标的效率是正常SQL select 查询的1/50到1/70
q 不得不按照每次一行的方式来处理行（游标）时候的性能，开销相对于面向集合的方式来处理行的性能是巨大的。在解决使用游标之前，应该确定已经仔细分析了需求，并且无法通过别的编程方法来解决问题。某些情况下面，游标是非常有用的，但是它应当是程序设计员最后的手段，而并不应当成为第一的选择！
2-4-1 使用游标的基本步骤
使用游标共分五个步骤，分别是：声明游标，打开游标，利用游标读取、修改或删除所取的行，关闭游标，释放游标。
1. 声明游标：
为游标指定获取数据时候的
select
语句。声明的游标并不会检索任何的数据，仅仅为游标指定
select
的查询范围。
Declare
游标名称
cusor cursoroptions
For select
语句
2. 打开游标
Open
游标名称
。
3. 取出游标中的信息
Fetch
游标名称
into @
变量
1
，
@
变量
2
该操作将使得游标移动到下一行记录，并将游标返回的每个列的数据分别赋值给本地变量，这些变量必须预先予以声明。
4. 关闭游标
关闭游标的目的是释放数据和系统资源，但是保留
select
语句，以后还可以通过
open
方法打开（
open
命令与
close
相似）。
Close
游标名称
释放游标，释放相关内存，并删除游标定义
Deallocate
游标名称
实验：建立游标的基本实验
--例1：请按照下面的步骤建立游标
use school
GO
--
FOR
SELECT sname FROM student
--
OPEN stu_cursor
--
FETCH NEXT FROM stu_cursor
--
close stu_cursor
--
deallocate stu_cursor
2-4-2 在游标中使用 FETCH
在游标的使用中我们经常会用到两个系统全局变量：
@@cursor_rows
和
@@FETCH_ STATUS
，下面我们就这两个系统全局变量进行说明。
1. @@cursor_rows
返回连接上最后打开的游标中当前存在的合格行的数量。
2. @@FETCH_ STATUS
会被
FETCH
语句执行的最后游标的状态，而
@@fetch_status
标量的意义是：
0——
最近一次
fetch
命令成功的获取到一行数据；
1——
最近一次
fetch
命令到达结果集的尾部；
2——
最近一次获取的行不可用，该行已经被删除。
实验
1
：在简单的游标中使用
FETCH
USE school
GO
DECLARE @sname varchar(40)
DECLARE stu_cursor CURSOR
FOR
SELECT sname FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
INTO @sname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '
FETCH NEXT FROM stu_cursor INTO @sname
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO
刚才我们看到关键词：
FETCH NEXT
是推动游标向前移动的关键，除此以外，
FETCH
还包括以下的移动关键词，如表
2-6
所示。
表2-6 FETCH 移动关键词
|
关键字
|
移动位置
|
|
FIRST
|
数据集第一条记录
|
|
LAST
|
数据集末一条记录
|
|
PRIOR
|
前一条记录
|
|
NEXT
|
后一条记录
|
|
RELATIVE
|
按照相对位置决定移动的位置
|
|
ABSOLUTE
|
按照绝对位置决定移动的位置
|
实验
2
：在简单的游标中使用
FETCH
--例1：
DECLARE @sname varchar(40) --定义游标阶段
DECLARE stu_cursor CURSOR
FOR
SELECT sname FROM student
--定义并打开游标阶段
declare @sname varchar(20)
FETCH NEXT FROM stu_cursor
INTO @sname
print @sname --读取并显示游标阶段
declare @sname varchar(20)
FETCH prior FROM stu_cursor
INTO @sname
print @sname
--
小问题：系统将报错提示：“fetch: 提取类型 prior 不能用于只进游标。这是为什么？”
--例2：语法改进
DECLARE @sname varchar(40)
DECLARE stu_cursor CURSOR scroll –-
注意差异之处
FOR
…… --其余地方同例1一致，此处略。
--例3：通过游标技术循环打印学生姓名，每个姓名之间用逗号分隔
DECLARE @sname varchar(30),@s varchar(400)
DECLARE stu_cursor CURSOR
FOR
SELECT sname FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor INTO @s
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM stu_cursor
INTO @sname
set @s=ltrim(rtrim(@s))+','+ltrim(rtrim(@sname))
END
--开始打印姓名
print @s
CLOSE stu_cursor
DEALLOCATE stu_cursor --关闭并释放游标资源
GO
--
小问题：为什么最后一行总是重复出现呢？如何解决这个问题？
实验
3
：使用游标修改或删除数据
DECLARE abc CURSOR
FOR
SELECT sName FROM Student
OPEN abc
GO
FETCH NEXT FROM abc
GO
UPDATE student SET sName = '
GO
CLOSE abc
DEALLOCATE abc
GO
实验
4
：复杂的游标查询问题
——
使用递归游标遍历树算法解决家族树的问题
--
--
create procedure family_all(@me_id int)
--
as
declare @child_id int,@child_name varchar(30)
declare cchild cursor local fast_forward
--
for
select person_id,person_name from person where person.father_id=@me_id or person.mother_id=@me_id
order by date_birth
--
open cchild
fetch cchild into @child_id,@child_name
--
while @@fetch_status=0
begin
print space(@@nestlevel*2)+'+'+cast(@child_id as varchar(6))+''+@child_name
--
--@@NESTLEVEL
exec family_all @child_id
--
fetch cchild into @child_id,@child_name
--
end
close cchild
deallocate cchild
exec family_all 2
注意：
当游标较少时候，使用该算法解决遍历问题已经足够；但是当数据量较大时候，就会出现问题。主要由两个原因造成：
q MSSQL限制存储过程循环次数最多32层。
q 性能问题。基于行的游标比基于集合的查询性能上面将慢50到70倍。如果假设将500万行数据组织为12个层次，需要进行游标调用500万次，开辟500万个缓冲空间，执行500万次的select查询。
实验
5
：复杂的游标查询问题
——
改进的非游标查询策略
--
create table familytree
(person_id int, generation int, familyline varchar(20) default '')
declare @generation int,@firsperson int
set @generation=1
set @firsperson=1
--
insert familytree(person_id,generation,familyline)
select @firsperson,@generation,@firsperson
while @@rowcount>0
begin
set @generation=@generation+1
insert familytree(person_id,generation,familyline)
select person.person_id,@generation,familytree.familyline+''+str(person.person_id,5)
from person
inner join familytree
on familytree.generation=@generation-1
and (familytree.person_id=person.mother_id
or familytree.person_id=person.father_id)
end
--
select * from familytree
drop table familytree
本文转自 qianshao 51CTO博客，原文链接：http://blog.51cto.com/qianshao/348298，如需转载请自行联系原作者
