1.游标的组成
游标包含两个部分：一个是游标结果集、一个是游标位置。
游标结果集：定义该游标得SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。
2.游标的分类
游标共有3类：API服务器游标、Transaction-sql游标和API客户端游标。
其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。
API服务器游标
API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对API函数进行处理。使用API函数和方法可以实现如下功能：
（１）打开一个连接。
（２）设置定义游标特征的特性或属性，API自动将游标影射到每个结果集。
（３）执行一个或多个Transaction-SQL语句。
（４）使用API函数或方法提取结果集中的行。
API服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标（Primary key）
静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，（静态游标始终是只读的）。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不反映数据库中作的任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行；组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；但是静态游标会显示打开游标以后从数据库中删除的行。
动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的行数据值、顺序和成员每次提取时都会改变。
只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标也反映对结果集所做的所有更改。
键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到临时工作表中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新打开游标。
Transaction-sql游标
该游标是基于Declare Cursor 语法，主要用于Transaction-sql脚本、存储过程以及触发器中。Transaction-sql游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。
在存储过程或触发器中使用Transaction-sql游标的过程为：
（１）声明Transaction-sql变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。
（２）使用Declare Cursor语句将Transaction-sql游标与Select语句相关联。还可以利用Declare Cursor定义游标的只读、只进等特性。
（３）使用Open语句执行Select语句填充游标。
（４）使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。Transaction-sql游标不支持提取行块。
（５）使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用Deallocate语句才会完全释放。
客户端游标
该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标 *** 作都在客户端的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。
3.游标的生命周期
游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。
声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select 语句。
Declare 游标名称 Cursor 参数
声明游标的参数
（１）Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。
（２）Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。
（３）Static、Keyset与Dynamic: 第一个表示定义一个游标，其数据存放到一个临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取 *** 作时返回的数据不反映对基表所作的修改，并且该游标不允许修改。Keyset表示的是，当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic表示的是滚动游标时，动态游标反映对结果集内所有数据的更改。
（４）Read_only 、Scroll_Locks与Optimistic：第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除 *** 作将不会成功。
标准游标：
Declare MyCursor Cursor
For Select * From Master_Goods
只读游标
Declare MyCusror Cursor
For Select * From Master_Goods
For Read Only
可更新游标
Declare MyCusror Cursor
For Select * From Master_Goods
For UpDate
打开游标使用Open语句用于打开Transaction-sql服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。
打开游标
全局游标：Open Global MyCursor 局部游标: Open MyCursor
读取游标数据：在打开游标以后，使用Fetch语句从Transaction-sql服务器游标中检索特定的一行。使用Fetch *** 作，可以使游标移动到下一个记录，并将游标返回的每个列得数据分别赋值给声明的本地变量。
Fetch [Next | Prior | First | Last | absolute n | relative n ] From MyCursor
Into @GoodsID,@Goodsname
其中:Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为Next
Prior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。
First表示返回结果集中的第一行，并且将其作为当前行。
Last表示返回结果集中的最后一行，并且将其作为当前行。
absolute n 如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。
relative n 如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。
关闭游标调用的是Close语句，方式如下：Close Global MyCursor Close MyCursor
释放游标调用的是Deallocate语句，方法如下：Deallocate Glboal MyCursor Deallocate MyCursor
游标实例:
Declare MyCusror Cursor Scroll
For Select * From Master_Goods Order By GoodsID
Open MyCursor
Fetch next From MyCursor
Into @GoodsCode,@Goodsname
While(@@Fetch_Status = 0)
Begin
Begin
Select @GoodsCode = Convert(Char(20),@GoodsCode)
Select @Goodsname = Convert(Char(20),@Goodsname)
PRINT @GoodsCode + ':' + @Goodsname
End
Fetch next From MyCursor
Into @GoodsCode,@Goodsname
End
Close MyCursor
Deallocate MyCursor
修改当前游标的数据方法如下:
UpDate Master_Goods Set Goodsname = 'yangyang8848' Where Current Of MyCursor;
删除当前游标行数据的方法如下:
Delete From Master_Goods Where Current Of MyCursor
Select @@CURSOR_ROWS 可以得到当前游标中存在的数据行数。注意：此变量为一个连接上的全局变量，因此只对应最后一次打开的游标。
全局变量及其功能
| 全局变量 | 功 能 |
| @@CONNECTIONS | 自sql Server 2000最近一次启动以来登录或试图登录的次数 |
| @@cpu_BUSY | 自sql Server 2000最近一次启动以来cpu Server的工作时间 |
| @@CURRSOR_ROWS | 返回在本次连接最新打开的游标中的行数 |
| @@DATEFirsT | 返回SET DATEFirsT参数的当前值 |
| @@DBTS | 数据库的惟一时间标记值 |
| @@ERROR | 系统生成的最后一个错误，若为0则成功 |
| @@FETCH_STATUS | 最近一条FETCH语句的标志 |
| @@IDENTITY | 保存最近一次的插入身份值 |
| @@IDLE | 自cpu服务器最近一次启动以来的累计空闲时间 |
| @@IO_BUSY | 服务器输入输出 *** 作的累计时间 |
| @@LANGID | 当前使用的语言的ID |
| @@LANGUAGE | 当前使用语言的名称 |
| @@LOCK_TIMEOUT | 返回当前锁的超时设置 |
| @@MAX_CONNECTIONS | 同时与sql Server 2000相连的最大连接数量 |
| @@MAX_PRECISION | 十进制与数据类型的精度级别 |
| @@nesTLEVEL | 当前调用存储过程的嵌套级，范围为0～16 |
| @@OPTIONS | 返回当前SET选项的信息 |
| @@PACK_RECEIVED | 所读的输入包数量 |
| @@PACKET_SENT | 所写的输出包数量 |
| @@PACKET_ERRORS | 读与写数据包的错误数 |
| @@RPOCID | 当前存储过程的ID |
| @@REMSERVER | 返回远程数据库的名称 |
| @@ROWCOUNT | 最近一次查询涉及的行数 |
| @@SERVERname | 本地服务器名称 |
| @@SERVICEname | 当前运行的服务器名称 |
| @@SPID | 当前进程的ID |
| @@TEXTSIZE | 当前最大的文本或图像数据大小 |
| @@TIMETICKS | 每一个独立的计算机报时信号的间隔(ms)数，报时信号为31.25ms或1/32s |
| @@TOTAL_ERRORS | 读写过程中的错误数量 |
| @@TOTAL_READ | 读磁盘次数(不是高速缓存) |
| @@TOTAL_WRITE | 写磁盘次数 |
| @@TRANCOUNT | 当前用户的活动事务处理总数 |
| @@VERSION | 当前sql Server的版本号 |
例：
delete from student where sscore is null
select @@ROWCOUNT
例：
select @@IDENTITY
IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
ScopE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值
ScopE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，ScopE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。
在sqlServer存储过程中使用Cursor（游标） *** 作记录
1. 为何使用游标：
使用游标(cursor)的一个主要的原因就是把集合 *** 作转换成单个记录处理方式。用sql语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在sql server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。
2. 如何使用游标：
一般地，使用游标都遵循下列的常规步骤：
(1) 声明游标。把游标与T-SQL语句的结果集联系起来。
(2) 打开游标。
(3) 使用游标 *** 作数据。
(4) 关闭游标。
2.1. 声明游标
DECLARE CURSOR语句sql-92标准语法格式：
DECLARE 游标名 [ INSENSITIVE ] [ SCRolL ] CURSOR
FOR sql-statement
Eg:
Declare MycrsrVar Cursor
FOR Select * FROM tbMyData
2.2 打开游标
OPEN MycrsrVar
当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列 *** 作读取第1行数据：
FETCH FirsT from E1cursor
或 FETCH NEXT from E1cursor
2.3 使用游标 *** 作数据
下面的示例用@@FETCH_STATUS控制在一个WHILE循环中的游标活动
/* 使用游标读取数据的 *** 作如下。*/
DECLARE E1cursor cursor /* 声明游标，默认为FORWARD_ONLY游标 */
FOR SELECT * FROM c_example
OPEN E1cursor /* 打开游标 */
FETCH NEXT from E1cursor /* 读取第1行数据*/
WHILE @@FETCH_STATUS = 0 /* 用WHILE循环控制游标活动 */
BEGIN
FETCH NEXT from E1cursor /* 在循环体内将读取其余行数据 */
END
CLOSE E1cursor /* 关闭游标 */
DEALLOCATE E1cursor /* 删除游标 */
2.4 关闭游标
使用CLOSE语句关闭游标
CLOSE { { [ GLOBAL ] 游标名 } | 游标变量名 }
使用DEALLOCATE语句删除游标，其语法格式如下：
DEALLOCATE { { [ GLOBAL ] 游标名 } | @游标变量名
3. FETCH *** 作的简明语法如下：
FETCH
[ NEXT | PRIOR | FirsT | LAST]
FROM
{ 游标名 | @游标变量名 } [ INTO @变量名 [，…] ]
参数说明：
NEXT 取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT *** 作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。
INTO @变量名[,…] 把提取 *** 作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。
--------------------------------------------------------------------------------------------------------------------------------
每执行一个FETCH *** 作之后，通常都要查看一下全局变量@@FETCH_STATUS中的状态值，以此判断FETCH *** 作是否成功。该变量有三种状态值：
· 0 表示成功执行FETCH语句。
· -1 表示FETCH语句失败，例如移动行指针使其超出了结果集。
· -2 表示被提取的行不存在。
由于@@FETCH_STATU是全局变量，在一个连接上的所有游标都可能影响该变量的值。因此，在执行一条FETCH语句后，必须在对另一游标执行另一FETCH 语句之前测试该变量的值才能作出正确的判断。
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
SET NOCOUNT ON
DECLARE @au_ID varchar(11),@au_fname varchar(20),@au_lname varchar(40),
@message varchar(80),@Title varchar(80)
PRINT "-------- Utah Authors report --------"
DECLARE authors_cursor CURSOR FOR
SELECT au_ID,au_fname,au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_ID
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_ID,@au_fname,@au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname
PRINT @message
-- Declare an inner cursor based
-- on au_ID from the outer cursor.
DECLARE Titles_cursor CURSOR FOR
SELECT t.Title
FROM Titleauthor ta,Titles t
WHERE ta.Title_ID = t.Title_ID AND
ta.au_ID = @au_ID -- Variable value from the outer cursor
OPEN Titles_cursor
FETCH NEXT FROM Titles_cursor INTO @Title
IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @Title
PRINT @message
FETCH NEXT FROM Titles_cursor INTO @Title
END
CLOSE Titles_cursor
DEALLOCATE Titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_ID,255); white-space:pre-wrap; Font-size:16px">END
CLOSE authors_cursor
DEALLOCATE authors_cursorGO
SQL游标的执行效率,静态游标的高效率执行
静态(STATIC)游标创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取 *** 作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改
动态(DYNAMIC) 游标会反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持absolute 提取选项。
不指定STATIC 关键字的时候,默认定义的游标是动态(DYNAMIC) 的
如果很在意游标的类型,应该在定义游标的时候,加上游标类型定义的关键字,并加上TYPE_WARNING 关键字,以便在游标类型不是预期的情况下,收到警告信息
演示:
下面的演示针对 STATIC 和DYNAMIC 游标,显示两者在游标循环期的差异
[code=sql]
-- 定义演示数据
IF OBJECT_ID('tempdb..#tb') IS NOT NulL
DROP table #tb
CREATE table #tb(
ID int PRIMARY KEY,
col sysname)
INSERT #tb(
ID,col)
SELECT 1,'AA' UNION ALL
SELECT 2,'BB' UNION ALL
SELECT 3,'CC' UNION ALL
SELECT 4,'DD'
-- 游标测试
DECLARE CUR_tb CURSOR LOCAL FORWARD_ONLY READ_ONLY TYPE_WARNING STATIC --DYNAMIC
FOR
SELECT
FROM #tb
-- 游标打开前删除记录
DELETE top (1)
WHERE ID = 4
SELECT 'before cursor open',* FROM #tb
-- 打开游标
OPEN CUR_tb
-- 游标打开后删除记录
WHERE ID = 3
SELECT 'after cursor open',51); Font-size:14px"> FETCH CUR_tb
WHILE @@FETCH_STATUS = 0
BEGIN
-- 游标循环中删除记录
DELETE top (1)
FROM #tb
WHERE ID = 2
FETCH CUR_tb
END
CLOSE CUR_tb
DEALLOCATE CUR_tb
总结
以上是内存溢出为你收集整理的SQLServer游标(Cursor)简介和使用说明 及全局变量说明和功能全部内容，希望文章能够帮你解决SQLServer游标(Cursor)简介和使用说明 及全局变量说明和功能所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）