SQL Server游标

• SQL游标概念

SQL的游标是一种临时的数据库对象，既可以存放储存在数据库表中数据行的副本，也指向数据行的指针。

• SQL游标作用

1.遍历数据行；

2.保存查询结果，方便下文调用。概念中提到使用游标会保存数据行的副本，那么创建游标后，下文查询即可从副本中查询，要比直接查数据库快很多。

• 创建游标

语法：

declare cursor_name  --游标名称，唯一标识
[insensitive] [scroll] cursor 
for
select_statement --查询语句
[for {read only| update [of column_name [,...n]]}]

 

insensitive

告诉DBMS产生查询结果的临时副本，而不是使用指针指向数据库表中源数据。

指定insensitive时，对底层表任何改动都不会反映到游标数据中。反之，对底层表的改动都会反映到游标数据中。

insensitive游标是只读的，因此不能修改其内容，也不能通过它修改底层表数据。

scroll

表明所有的提取操作，即fetch选项（具体选项在下文提到），若不指定只能进行next提取。

read only

设置游标数据只读，指定read only后，对底层表的改动不会更新其游标数据。

update [of column_name[,...n]]

定义在游标中可被更改的列。如果只指定了update，表示所有列都可以更新。

 

• FETCH语句检索数据

刚才讲了创建游标的语法，结合fetch语句先写个例子吧：

--创建游标
declare cursor_school scroll cursor 
for
select Num,ChineseName from School order by Num

--打开游标
open cursor_school

--定义变量
declare @num bigint, @schoolname nvarchar(50)

--提取最后一行学校信息
fetch last from cursor_school into @num, @schoolname

print '学校编号：' + cast(@num as varchar) + '学校名称：' + @schoolname

--关闭游标
close cursor_school

结果集：

 

输出结果：

学校编号：609781000000学校名称：香港凱莉山學校

注意上文中order by之后的列必须在select中出现。

 

fetch语句中，SQL Server提供了6种定位选项：

next

返回结果集当前行的下一行，首次提取返回第一行。

frior

返回结果集的上一行，首次提取无数据返回。

first

返回结果集第一行。

last

返回结果集最后一行。

absolute

移动到结果集的第n行。如果n为正数，从结果集的第一行（包含第一行）起移到第n行；如果n为负数，则从结果集的最后一行起移到第n行。

relative

从游标指针的当前位置移动n行。如果n为正数，则读取游标当前位置起向后的第n行数据；如果n为负数，则读取游标当前位置起向前的第n行数据。

• 基于游标的定位UPDATE语句和定位DELETE语句

//update
update table_name set column_name,... 
where current of cursor_name

//delete
delete from table_name
where current of cursor_name

使用update或delete语句时要先创建游标。

• 游标关闭与释放 

CLOSE（关闭）游标后不会释放其占用的数据结构。那么想要释放占用的数据结构需要用DEALLOCATE语句，该语句不仅删除游标中的数据，还会将游标作为对象从数据库中删除。

释放游标语句：deallocate cusor_name