SQL Server 游标详解及使用示例_四海名汀

http://blog.sina.com.cn/u/1558925640

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

SQL Server 游标详解及使用示例

(2012-12-12 11:24:51)

标签：

sqlserver游标

数据库游标

游标的使用

游标基础知识

游标示例

分类：数据库学习

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T-SQL 选择语句相关联，因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

一、游标的概念

游标(Cursor)它使用户可逐行访问由SQL Server返回的结果集。使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

二、游标种类

MS SQL SERVER 支持三种类型的游标：Transact_SQL 游标、API 服务器游标和客户游标。

1、Transact_SQL 游标

Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。Transact_SQL 游标不支持提取数据块或多行数据。

2、API 游标

API 游标支持在OLE DB，ODBC 以及DB_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API 游标函数，MS SQL SEVER 的OLE DB 提供者、ODBC驱动器或DB_library 的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。

3、客户游标

客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。

由于API 游标和Transact-SQL 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。

三、游标的优点

从游标定义可以得到游标的如下优点，这些优点使游标在实际应用中发挥了重要作用：

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

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

3、游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。

四、游标的使用

每一个游标必须由四个组成部分，这四个关键部分必须符合下面的顺序:

1.DECLARE 游标

2.OPEN 游标

3.从一个游标中FETCH 信息

4.CLOSE 或DEALLOCATE 游标

通常我们使用DECLARE 来声明一个游标，声明一个游标主要包括以下主要内容：

1.游标名字

2.数据来源（表和列）

3.选取条件

4.属性（仅读或可修改）

游标定义语法格式如下：

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——游标对于特定连接作为上下文，全局内有效。

forward_only——游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项。

scroll——游标在定义的数据集中向任何方向，或任何位置移动。所有的提取操作（如FIRST、LAST、PRIOR、 NEXT、RELATIVE、ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重开游标。

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

keyset——可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb，当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2，KEYSET无法探测新加入的数据。

dynamic——和STATIC完全相反的选项，当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变。

fast_forward——可以理解成FORWARD_ONLY的优化版本。FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好。

read_only——声明的游标只能读取数据，游标不能做任何更新操作。

scroll_locks——是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功。

optimistic——相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时，如果底层表数据更新，则游标内数据更新不成功，如果底层表数据未更新，则游标内表数据可以更新。

1、声明游标

最简单游标声明：DECLARE <游标名>CURSOR FOR

其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询

示例：

Declare mycursor cursor for select * from transinfo

这样我就对表transinfo申明了一个游标mycursor。

2、打开游标

非常简单，我们就打开刚才我们声明的游标mycursor。

open mycursor

3、读取数据

FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名 | @游标变量名 } [ INTO @变量名 [，…] ]

参数说明：

NEXT 取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。

INTO @变量名[,…] 把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据：

例如：Fetch next from mycursor 或则 Fetch first from mycursor

这样我就取出了游标里的数据，但是光光这样可不够，我们还需要将取出的数据赋给变量

--声明2个变量

declare @cid varchar(10)

declare @money money

--将取出的值传入刚才声明的2个变量

Fetch next from mycursor into @cid,@money

4、关闭游标

close mycursor

5、删除游标

deallocate mycursor

五、测试示例

介绍完游标的使用步骤后，我们来看看其简单的使用过程。

--定义后直接赋值

declare cursor_test cursor for select * from transinfo

--先定义后赋值

declare @cursor_test1 cursor

set @cursor_test1=cursor for select * from transinfo

--使用local和global参数

declare cursor_test cursor global for select * from transinfo

declare cursor_test1 cursor local for select * from transinfo

open cursor_test

open cursor_test1

测试结果：

消息 16916，级别 16，状态 1，第 1 行

名为 'cursor_test1' 的游标不存在。

说明：全局游标，在批处理结束后依然有效；局部游标，在批处理结束后，被隐式释放，无法再其他批处理中调用。如果不指定作用域，则默认为global。

--使用forward_only和scroll参数

deallocate cur_test

deallocate cur_test1

deallocate cur_test2

declare cur_test cursor for select * from transinfo

declare cur_test1 cursor forward_only for select * from transinfo

declare cur_test2 cursor scroll for select * from transinfo

open cur_test

open cur_test1

open cur_test2

fetch last from cur_test

fetch last from cur_test1

fetch last from cur_test2

测试结果：

消息 16911，级别 16，状态 1，第 4 行

fetch: 提取类型 last 不能与只进游标一起使用。

消息 16911，级别 16，状态 1，第 5 行

fetch: 提取类型 last 不能与只进游标一起使用。

(1 行受影响)

说明：默认和forward_only选项只支持fetch next选项，不支持游标向后或者走向特定的位置；scroll选项支持游标向任何方向滚动。

1、不循环读出数据游标

declare mycursor cursor for

select cardID,transMoney from transinfo

open mycursor

declare @id varchar(10),@money money

fetch next from mycursor into @id,@money

print '输出结果:'

print @id

print @money

close mycursor

deallocate mycursor

测试结果：

输出结果:

1000 0003

2500.00

2、循环读出数据游标

declare mycursor1 cursor for

select cardID,transMoney from transinfo

open mycursor1

declare @id varchar(10),@money money

fetch next from mycursor1 into @id,@money

while (@@fetch_status=0)

begin

print '成功取出一条数据'

print @id

print @money

fetch next from mycursor1 into @id,@money

end

close mycursor1

deallocate mycursor1

测试结果：

成功取出一条数据

1000 0003

2500.00

成功取出一条数据

1001 0002

600.00

成功取出一条数据

1001 0002

600.00

3、带Scroll参数游标

declare mycursor2 scroll cursor for

select cardID,transMoney from transinfo

open mycursor2

declare @id varchar(10),@money money

--取出下一行

fetch next from mycursor2 into @id,@money

print 'next输出结果:'

print @id

print @money

--取出最后一行

fetch last from mycursor2 into @id,@money

print 'last输出结果:'

print @id

print @money

--取出上一行

fetch prior from mycursor2 into @id,@money

print 'prior输出结果:'

print @id

print @money

--取出第一行

fetch first from mycursor2 into @id,@money

print 'first输出结果:'

print @id

print @money

--取出第三行

fetch absolute 3 from mycursor2 into @id,@money

print 'absolute输出结果:'

print @id

print @money

--取出相对当前的上一行

fetch relative -1 from mycursor2 into @id,@money

print 'relative输出结果:'

print @id

print @money

close mycursor2

deallocate mycursor2

测试结果：

next输出结果:

1000 0003

2500.00

last输出结果:

1000 0004

600.00

prior输出结果:

1000 0001

500.00

first输出结果:

1000 0003

2500.00

absolute输出结果:

1000 0001

500.00

relative输出结果:

1000 0002

800.00

说明：必须指定scroll，否则只支持next选项。(relative -1)取负数，对于目前行是向前；取正数，对于目前行是向后。

4、在存储过程中使用的游标

if object_id('p_cur_test')is not null

drop proc p_cur_test

create proc p_cur_test

declare @cid varchar(10)

declare mycur cursor for

select cardID from transinfo

open mycur

fetch next from mycur into @cid

while (@@fetch_status=0)

begin

print 'cardID:'+@cid

fetch next from mycur into @cid

end

close mycur

deallocate mycur

通过执行“exec p_cur_test”的测试结果：

cardID:1000 0003

cardID:1000 0002

cardID:1000 0001

cardID:1000 0004

本文参考资料：http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：SQL Server锁

后一篇：ct_connect(): directory service layer: internal directory control&

新浪BLOG意见反馈留言板　欢迎批评指正