关于 sql 中的游标 - 达人(在海水中游泳)

2007/05/31 | 关于 sql 中的游标

类别(数据库学习笔记) | 评论(1) | 阅读(471) | 发表于 17:31

Transact-SQL 游标

Transact-SQL 游标主要用在存储过程、触发器和 Transact-SQL 脚本中，它们使结果集的内容对其它 Transact-SQL 语句同样可用。

在存储过程或触发器中使用 Transact-SQL 游标的典型进程为：

声明 Transact-SQL 变量包含游标返回的数据。为每一结果集列声明一个变量。声明足够大的变量以保存由列返回的值，并声明可从列数据类型以隐性方式转换得到的数据类型。

使用 DECLARE CURSOR 语句把 Transact-SQL 游标与一个 SELECT 语句相关联。DECLARE CURSOR 语句同时定义游标的特征，比如游标名称以及游标是否为只读或只进特性。

使用 OPEN 语句执行 SELECT 语句并生成游标。

使用 FETCH INTO 语句提取单个行，并把每列中的数据转移到指定的变量中。然后，其它 Transact-SQL 语句可以引用这些变量来访问已提取的数据值。Transact-SQL 不支持提取行块。

结束游标时，使用 CLOSE 语句。关闭游标可以释放某些资源，比如游标结果集和对当前行的锁定，但是如果重新发出一个 OPEN 语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用游标的名称。DEALLOCATE 语句则完全释放分配给游标的资源，包括游标名称。在游标被释放后，必须使用 DECLARE 语句来重新生成游标。

监视 Transact-SQL 游标的活动

可以使用 sp_cursor_list 系统存储过程来获得对当前连接可见的游标列表，使用sp_describe_cursor、sp_describe_cursor_columns和 sp_describe_cursor_tables 来确定游标的特征。

在游标打开后，@@CURSOR_ROWS 函数或由 sp_cursor_list 或 sp_describe_cursor 返回的 cursor_rows 列会指出游标中的行数。

在每个 FETCH 语句执行之后，@@FETCH_STATUS 更新以反映最后一次提取的状态。也可以从由 sp_describe_cursor 返回的 fetch_status 列中获取该状态信息。@@FETCH_STATUS 报告游标中的状态，比如超出第一行和最后一行的提取。@@FETCH_STATUS 对于连接来说是全局性的，并在连接游标打开时由每次提取进行重置。如果必须在以后了解状态，就需要在连接中执行另外一个语句之前，把 @@FETCH_STATUS 保存在一个用户变量中。即使下一语句不是 FETCH，也可能是 INSERT、UPDATE 或 DELETE 这些语句，它们能够激发包含可重置 @@FETCH_STATUS 的 FETCH 语句的触发器。sp_describe_cursor 返回的 fetch_status 列对于指定的游标来说是确定的，不受那些引用其它游标的 FETCH 语句的影响，但 sp_describe_cursor 会受引用相同游标的 FETCH 语句的影响，因此使用时必须注意。

完成 FETCH 后，游标将定位在已提取的行上。被提取的行称为当前行。如果游标没有声明为只读游标，就可以执行带有 WHERE CURRENT OF cursor_name 子句的 UPDATE 或 DELETE 语句来修改当前行。

由 DECLARE CURSOR 语句赋给 Transact-SQL 游标的名称可以是全局或局部的。全局游标的名称可由任何位于同一连接上的批处理、存储过程或触发器引用。局部游标名称不能在声明游标的批处理、存储过程或触发器之外被引用。触发器和存储过程中的局部游标因而可以避免来自存储过程或触发器外部的无意引用。

使用游标变量

Microsoft® SQL Server™ 2000 也支持 cursor 数据类型的变量。游标可以有两种方法与一个 cursor 变量相关联：

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */

DECLARE @MyVariable CURSOR

DECLARE MyCursor CURSOR FOR

SELECT LastName FROM Northwind.dbo.Employees

SET @MyVariable = MyCursor

/* Use DECLARE @local_variable and SET */

DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR

SELECT LastName FROM Northwind.dbo.Employees

当游标与 cursor 变量相关联之后，在 Transact-SQL 游标语句中就可以使用 cursor 变量代替游标名称。存储过程输出参数也可指派为 cursor 数据类型，并与一个游标相关联。这就允许存储过程有节制地展示其局部游标。

引用 Transact-SQL 游标

Transact-SQL 游标名称和变量只能由 Transact-SQL 语句引用，而不能由 OLE DB、ODBC、ADO 和 DB-Library 的 API 函数引用。例如，如果要使用 DECLARE CURSOR 和 OPEN 语句生成一个 Transact-SQL 游标，就无法使用 SQLFetch 或 SQLFetchScroll 函数来从 Transact-SQL 游标中提取行。需要游标处理而又使用这些 API 的应用程序应该使用数据库 API 中内建的游标支持代替 Transact-SQL 游标。

通过使用 FETCH 并绑定由 FETCH 返回给程序变量的每一列，可以在应用程序中使用 Transact-SQL 游标。Transact-SQL FETCH 不支持批处理，因此，这是将数据返回给应用程序的效率最低的方法。每提取一行均需往返服务器一次。使用内建在数据库 API 中的游标功能更为有效，可进行多行提取。

当包含在存储过程和触发器中时，Transact-SQL 游标极其有效。这是因为所有操作都编译到服务器上的一个执行计划内，不存在与行提取有关的网络流量。

请参见

@@FETCH_STATUS

FETCH

游标函数

OPEN

DEALLOCATE

WHERE

DECLARE CURSOR

Transact-SQL 游标名称的作用域

哪位大虾能帮忙把下面的VBS函数写成对应SQL的用户自定义函数啊?急求!

函数1:

function xinxifei(timesec)

if timesec mod 60 then

timemin=(timesec\60)+1

else

timemin=timesec/60

end if

xinxifei=timemin*0.6

end function

函数2:

function tonghuafei(timesec)

if timesec mod 60 then

timemin=timesec\60+1

else

timemin=timesec/60

end if

if timemin<=3 then

tonghuafei=0.2

else

tonghuafei=(timemin-3)*0.1+0.2

end if

end function

问题点数：100、回复次数：17

Top

1 楼xiaoku（野蛮人(^v^)）回复于 2006-09-23 11:05:20 得分 0 1

create function xinxifei(@timesec int)

returns @xinxifei decimal(18,2)

bgin

declare @timemin decimal(18,2)

if exists( @timesec mod 60)

@timemin=(@timesec/60)+1

else

@timemin=@timesec/60

@xinxifei=@timemin*0.6

end

Top

2 楼xiaoku（野蛮人(^v^)）回复于 2006-09-23 11:06:06 得分 0 timemin=timesec\60+1

这句是什么意思?

Top

3 楼xyjnsdcn（MCP。刚被解封,怎么挣信誉分?）回复于 2006-09-23 11:07:59 得分 0 不好意思,忘了说明一下,timesec和timemin是长整型,xinxifei是有2位小数的.

Top

4 楼xyjnsdcn（MCP。刚被解封,怎么挣信誉分?）回复于 2006-09-23 11:09:18 得分 0 timemin=timesec\60+1

整除后+1

就是一个时间的换算。秒->分钟，不足一分钟按1分钟计算。

Top

5 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:18:02 得分 0

create function xinxifei(@timesec int)

returns numeric(10,1)

begin

declare @sec int,

@timemin int,

@xinxifei numeric(10,1)

set @sec=@timesec

if @timesec>60 --这里不知道mod是什么意思

set @timemin=(@timesec/60)+1

else

set @timemin=@timesec/60

set @xinxifei=@timemin*0.6

return @xinxifei

end

select dbo.xinxifei(100)

drop function xinxifei

Top

6 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:20:31 得分 0 numeric(10,1)

－－》numeric(10,2)

@timesec>60 －－》 @timesec<60

Top

7 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:23:34 得分 0 int-->bigint

楼主可以参照上面的语法稍微改改

不足一分钟按1分钟计算。

可以直接<60就等于1啊

Top

8 楼xiaoku（野蛮人(^v^)）回复于 2006-09-23 11:26:57 得分 0 前两个星期是 ximeng 跟我抢,

现在是你哦小虾米:

ＬＺ最好参照一下　连接帮助的　create function 的用法..

Top

9 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:30:44 得分 0 前两个星期是 ximeng 跟我抢,

现在是你哦小虾米:

^0^我刷的时候没人 ^0^

Top

10 楼xiaoku（野蛮人(^v^)）回复于 2006-09-23 11:32:23 得分 0 1 --修改后

create function xinxifei(@timesec int)

returns @xinxifei decimal(18,2)

bgin

declare @timemin int

if exists( @timesec % 60)

@timemin=cast ((@timesec/60) as int) +1

else

@timemin=@timesec/60

@xinxifei=@timemin*0.6

end

Top

11 楼Hopewell_Go（好的在后頭﹗希望更好﹗﹗）回复于 2006-09-23 11:32:56 得分 0 看看联机帮助就可以了，这只是语法问题，问题不是好大，只要知道其语法格式就OK

Top

12 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:34:15 得分 0 if exists( @timesec % 60)

@timemin=cast ((@timesec/60) as int) +1

-->

这里好像没必要，直接《60 ＝1

哈哈，就跟你一起回答了两个问题啊，另外一个问题我也没得分 ^0^

Top

13 楼xiaoku（野蛮人(^v^)）回复于 2006-09-23 11:38:32 得分 0 1

create function xinxifei(@timesec int)

returns @xinxifei decimal(18,2)

bgin

declare @timemin int

if ( @timesec % 60) >0

@timemin=cast ((@timesec/60) as int) +1

else

@timemin=@timesec/60

@xinxifei=@timemin*0.6

return

函数2:

create function tonghuafei(@timesec int)

returns @tonghuafei decimal(18,2)

begin

declare @timemin int

if @timesec % 60 >0

@timemin=cast ((@timesec/60) as int) +1

else

@timemin=@timesec/60

if @timemin<=3 then

@tonghuafei=0.2

else

@tonghuafei=(@timemin-3)*0.1+0.2

return

Top

14 楼xyjnsdcn（MCP。刚被解封,怎么挣信誉分?）回复于 2006-09-23 11:38:42 得分 0 2位都不对啊。。。

老是说第一行错误

create function xinxifei(@timesec int)

Top

15 楼xiaoku（野蛮人(^v^)）回复于 2006-09-23 11:38:55 得分 50小虾米:来抢吧!

别介意!

Top

16 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:45:39 得分 50老是说第一行错误

create function xinxifei(@timesec int)

在查询分析器执行

？？

什么错误

Top

17 楼xyxfly（All things are difficult before they are easy.）回复于 2006-09-23 11:46:16 得分 0 顺便问一句，楼主如何使用这个函数呢？

求日期所属星座的 T-SQL UDF (用户自定义函数)

ChinaCCNA.com 2006-5-9 15:23:03 40

use northwind

CREATE FUNCTION GetStar(@ datetime)

RETURNS varchar(100)

BEGIN

--仅一句 SQL 搞定

RETURN

(

--declare @ datetime

--set @ = getdate()

select max(star)

from

(

-- 星座,该星座开始日期所属月,该星座开始日期所属日

select '魔羯座' as star,1 as [month],1 as [day]

union all select '水瓶座',1,20

union all select '双鱼座',2,19

union all select '牧羊座',3,21

union all select '金牛座',4,20

union all select '双子座',5,21

union all select '巨蟹座',6,22

union all select '狮子座',7,23

union all select '处女座',8,23

union all select '天秤座',9,23

union all select '天蝎座',10,24

union all select '射手座',11,22

union all select '魔羯座',12,22

) stars

where dateadd(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0)))

(

select max(dateadd(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0))))

from

(

select '魔羯座' as star,1 as [month],1 as [day]

union all select '水瓶座',1,20

union all select '双鱼座',2,19

union all select '牧羊座',3,21

union all select '金牛座',4,20

union all select '双子座',5,21

union all select '巨蟹座',6,22

union all select '狮子座',7,23

union all select '处女座',8,23

union all select '天秤座',9,23

union all select '天蝎座',10,24

union all select '射手座',11,22

union all select '魔羯座',12,22

) stars

where @ >= dateadd(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0)))

)

end

--测试

use northwind

select dbo.getstar(birthdate),count(*)

from employees

group by dbo.getstar(birthdate)

SQL中Table型数据与用户自定义函数(downmoon)

SQL Server 2000 新增了Table型数据：Table型数据不能用来定义列的类型,只能用作T-SQL变量或者作为自定义函数的返回值,下例是一个简单的table 型数据的例子:

Declare @TableVar Table

(Cola int Primary Key,Colb char(3))

Insert Into @TableVar Values (1, 'abc')

Insert Into @TableVar Values (2, 'def')

Select * From @TableVar

以上语句定义了一个名为TableVar,有两列的table 型变量像通常的表一样table 型数据也有insert select 等操作。在SQL Server 2000 中table 型数据与用户自定义函数是密不可分的，SQL Server 2000支持两种类型的函数：内置函数和用户定义函数。内置函数只允许T-SQL 语句调用而不能更改使用。用户定义函数可以根据需要定义自己所需的函数。用户定义函数可以带参数，也可以不带参数，但只能返回单值。正是由于这个原因SQL Server 2000 增加了table 型数据，其值可以是整型字符型或数值型。下例是一个简单的用户定义函数说明了用户定义函数的基本结构:

Create Function CubicVolume

(@CubeLength decimal(4,1),@CubeWidth decimal(4,1),@CubeHeight decimal(4,1) )

Returns decimal(12,3)

Begin

Return (@CubeLength * @CubeWidth * @CubeHeight)

End

--SELECT AppDta.dbo.CubicVolume (10,8,6)

在上例中用CREATE FUNCTION 创建了一个函数CubicVolume 来计算立方体的体积，变量CubeLength CubeWidth CubeHeight 为输入参数，返回值为数值型。BEGIN 表明函数体的开始，END 表明函数体的结束。通过下例我们就会清楚用户定义函数与table 型数据是如何有机结合的：

Use pubs

Create Function SalesByStore(@storeid varchar(30))

Returns Table

Return (Select title, qty From sales s, titles t

Where s.stor_id = @storeid and t.title_id = s.title_id)

-- select * from sales

SELECT * FROM Pubs.dbo.SalesByStore(7131)

请教各路高手有关SQL 自定义函数问题!!!

--------------------------------------------------------------------------------

发布时间: 2006-12-15 ; 上次回复: 2006-12-15; 总计回复: 6人次

--------------------------------------------------------------------------------

问题1.我想在存储过程当中定义一个自定义函数然后在调用不知道这样能否行得通

问题2.我写了个自定义函数

但是调用的时候始终提示有错:

如下:CREATE FUNCTION test( @type char(4) )

returns INT

BEGIN

SELECT COUNT(* ) AS PSHU

FROM tbl_Vdelay Where yea='2005' and mon='5' and shipway='SEA' andtype=@type

END

execute test('AZH')

伺服器: 訊息 156，層級 15，狀態 1，程序 test，行 8

關鍵字 'execute' 附近的語法不正確。

伺服器: 訊息 170，層級 15，狀態 1，程序 test，行 8

行 8: 'AZH' 附近的語法不正確。

--------------------------------------------------------------------------------

阿来 [等级：★(中级)] (信誉值: 100) 回复于: 2005-5-20 12:19:02 Top

create FUNCTION test( @type char(4) )

Returns INT

BEGIN

Return(Select COUNT(*) AS PSHU

FROM tbl_Vdelay

Where yea='2005' and mon='5'

and shipway='SEA' and type=@type)

END

execute test('AZH')

--------------------------------------------------------------------------------

一天到晚游泳的鱼 [等级：★★(中级)] (信誉值: 100) 回复于: 2005-5-20 12:21:58 Top

你的函数应该也有问题吧，没有返回值的。

CREATE FUNCTION test( @type char(4) )

returns INT

BEGIN

Declare @CountInt--加上定义

SELECT @Count=COUNT(* )

FROM tbl_Vdelay Where yea='2005' and mon='5' and shipway='SEA' andtype=@type

Return @Count--加上返回值

END

--execute test('AZH')

Select dbo.test('AZH')--执行

--------------------------------------------------------------------------------

点点星灯 [等级：◆◆◆◆◆(中级)] (信誉值: 100) 回复于: 2005-5-20 12:22:40 Top

例如:

select dbo.ConvertNumberToChinese('100')

请一定要加"dbo."或是其他的用户名.

select dbo.test('AZH')

--------------------------------------------------------------------------------

一天到晚游泳的鱼 [等级：★★(中级)] (信誉值: 100) 回复于: 2005-5-20 12:23:20 Top

一楼，函数不是那样调用的。

--------------------------------------------------------------------------------

阿来 [等级：★(中级)] (信誉值: 100) 回复于: 2005-5-20 12:26:09 Top

改一下:

alter FUNCTION test( @type char(2) )

Returns INT

BEGIN

declare @cn int

select @cn=(Select COUNT(*)

FROM medicaltypedict

Where medicaltypecode=@type )

Return(@cn)

END

select dbo.test('02')

SQL-SERVER 声明游标

作者：kinytx 来源：CSDN 发布时间：2005-1-10 2:00:19

SQL-SERVER 声明游标

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

1.DECLARE 游标

2.OPEN 游标

3.从一个游标中FETCH 信息

4.CLOSE 或DEALLOCATE 游标

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

游标名字

数据来源（表和列）

选取条件

属性（仅读或可修改）

其语法格式如下：

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

其中：

cursor_name

指游标的名字。

INSENSITIVE

表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过

游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。

在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；

使用OUTER JOIN；

所选取的任意表没有索引；

将实数值当作选取的列。

SCROLL

表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再

重开游标。

select_statement

是定义结果集的SELECT 语句。应该注意的是，在游标中不能使用COMPUTE、COMPU- TE BY、 FOR BROWSE、 INTO 语句。

READ ONLY

表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在UPDATE或DELETE 语句的WHERE CURRENT OF 子句中，不允许对该游标进行引用。

UPDATE [OF column_name[,…n]]

定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。当游标被成功创建后，游标名成为该游标的惟一标识，如果在以后的存储过程、触发器或Transact_SQL 脚本中使用游标，必须指定该游标的名字。

上面介绍的是SQL_92 的游标语法规则。下面介绍MS SQL SERVER 提供的扩展了的游标声明语法，通过增加另外的保留字，使游标的功能进一步得到了增强其语法规则为；

LOCAL

定义游标的作用域仅限在其所在的存储过程、触发器或批处理中。当建立游标的存储过程执行结束后，游标会被自动释放。因此，我们常在存储过程中使用OUTPUT 保留字，将游标传递给该存储过程的调用者，这样在存储过程执行结束后，可以引用该游标变量，在该种情况下，直到引用该游标的最后一个就是被释放时，游标才会自动释放。

GLOBAL

定义游标的作用域是整个会话层会话层指用户的连接时间它包括从用户登录到SQLSERVER 到脱离数据库的整段时间。选择GLOBAL 表明在整个会话层的任何存储过程、触发器或批处理中都可以使用该游标，只有当用户脱离数据库、时该游标才会被自动释放。

注意：如果既未使用GLOBAL也未使用LOCAL，那么SQL SERVER将使用default local cursor数据库选项，为了与以彰的版本歉容，该选项常设置为FALSE。

FORWARD_ONLY

选项指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用FETCH NEXT 操作。除非使用STATIC， KEYSET 和DYNAMIC 关键字，否则如果未指明是使用FORWARD_ONLY 还是使用SCROLL，那么FORWARD_ONLY 将成为缺省选项，因为若使用STATIC KEYSET 和DYNAMIC 关键字，则变成了SCROLL 游标。另外如果使用了FORWARD_ONLY，便不能使用FAST_FORWARD。

STATIC

选项的含义与INSENSITIVE 选项一样，MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此对基本表的修改并不影响游标中的数据，即游标不会随着基本表内容的

改变而改变，同时也无法通过游标来更新基本表。

KEYSET

指出当游标被打开时，游标中列的顺序是固定的，并且MS SQL SERVER 会在tempdb内建立一个表，该表即为KEYSET KEYSET 的键值可惟一识别游标中的某行数据。当游标拥有者或其它用户对基本表中的非键值数据进行修改时，这种变化能够反映到游标中，所以游标用户或所有者可以通过滚动游标提限这些数据。

当其它用户增加一条新的符合所定义的游标范围的数据时，无法由此游标读到该数据。因为Transact-SQL 服务器游标不支持INSERT 语句。

如果在游标中的某一行被删除掉，那么当通过游标来提取该删除行时，@@FETCH_STATUS 的返回值为-2。 @@FETCH_STATUS 是用来判断读取游标是否成功的系统全局变量。

由于更新操作包括两部分：删除原数据插入新数据，所以如果读取原数据，@@FETCH_STATUS 的返回值为-2；而且无法通过游标来读取新插入的数据。但是如果使用了WHERE CURRENT OF 子句时，该新插入行数据便是可见的。

注意：如果基础表未包含惟一的索引或主键，则一个KEYSET游标将回复成STATIC游标。

DYNAMIC

指明基础表的变化将反映到游标中，使用这个选项会最大程度上保证数据的一致性。然而，与KEYSET 和STATIC 类型游标相比较，此类型游标需要大量的游标资源。

FAST_FORWARD

指明一个FORWARD_ONLY, READ_ONLY 型游标。此选项已为执行进行了优化。如果SCROLL 或FOR_UPDATE 选项被定义，则FAST_FORWARD 选项不能被定义。

SCROLL_LOCKS

指明锁被放置在游标结果集所使用的数据上当。数据被读入游标中时，就会出现锁。这个选项确保对一个游标进行的更新和删除操作总能被成功执行。如果FAST_FORWARD选项被定义，则不能选择该选项。另外，由于数据被游标锁定，所以当考虑到数据并发处理时，应避免使用该选项。

OPTIMISTIC

指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。如果使用了FAST_FORWARD 选项，则不能使用该选项。

TYPE_WARNING

指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。

注意：不可以将SQL_92的游标语法规则与MS SQL SERVER的游标扩展用法混合在一起使用。

下面我们将总结一下声明游标时应注意的一些问题。

如果在CURSOR 前使用了SCROLL 或INSENSITIVE 保留字，则不能在CURSOR 和FOR select_statement 之间使用任何的保留字。反之同理。

如果用DECLARE CURSOR 声明游标时，没有选择READ_ONLY、 OPTIMISTIC 或SCROLL_LOCKS 选项时，游标的缺省情况为：

如果SELECT 语句不支持更新，则游标为READ_ONLY；

STATIC 和FAST_FORWARD 类型的游标缺省为READ_ONLY；

DYNAMIC 和KEYSET 游标缺省为OPTIMISTIC。

我们仅能在Transact-SQL 语句中引用游标，而不能在数据库API 函数中引用。

游标被声明以后，可以通过系统过程对其特性进行设置。

对那些有权限对视图、表或某些列执行SELECT 语句的用户而言，它也具有使用游标的缺省权限。

使用脚本自己可以执行自己的特性,封装游标操作存储过程如下:

CREATE PROCEDURE Pr_ExecSQL2

@Asqlstring VARCHAR(8000) --输入的SQL语句

EXEC ('

--启动事务

BEGIN TRAN

DECLARE @ASql VARCHAR(8000)

DECLARE tnames_cursor CURSOR LOCAL FAST_FORWARD FOR '+ @asqlstring +'

OPEN tnames_cursor

FETCH NEXT FROM tnames_cursor INTO @ASql

WHILE (@@FETCH_STATUS=0)

BEGIN

print @ASql

EXEC (@ASql)

IF @@ERROR <> 0 GOTO FINALEX99v

FETCH NEXT FROM tnames_cursor INTO @ASql

END

CLOSE tnames_cursor

DEALLOCATE tnames_cursor

COMM99v TRAN

RETURN

FINALEX99v:

ROLLBACK TRAN

CLOSE tnames_cursor

DEALLOCATE tnames_cursor

使用方法:

declare @sql varchar(8000)

set @sql='select ''update a set a.a='' + b.a from b '

exec Pr_execsql2 @sql

示例

A. 在简单的游标中使用 FETCH

下例为 authors 表中姓以字母 B 开头的行声明了一个简单的游标，并使用 FETCH NEXT 逐个提取这些行。FETCH 语句以单行结果集形式返回由 DECLARE CURSOR 指定的列的值。

USE pubs

DECLARE authors_cursor CURSOR FOR

SELECT au_lname FROM authors

WHERE au_lname LIKE "B%"

ORDER BY au_lname

OPEN authors_cursor

-- Perform the first fetch.

FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM authors_cursor

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

au_lname

----------------------------------------

Bennet

au_lname

----------------------------------------

Blotchet-Halls

au_lname

----------------------------------------

B. 使用 FETCH 将值存入变量

下例与上例相似，但 FETCH 语句的输出存储于局部变量而不是直接返回给客户端。PRINT 语句将变量组合成单一字符串并将其返回到客户端。

USE pubs

-- Declare the variables to store the values returned by FETCH.

DECLARE @au_lname varchar(40), @au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR

SELECT au_lname, au_fname FROM authors

WHERE au_lname LIKE "B%"

ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.

FETCH NEXT FROM authors_cursor

INTO @au_lname, @au_fname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- Concatenate and display the current values in the variables.

PRINT "Author: " + @au_fname + " " + @au_lname

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM authors_cursor

INTO @au_lname, @au_fname

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

Author: Abraham Bennet

Author: Reginald Blotchet-Halls

C. 声明 SCROLL 游标并使用其它 FETCH 选项

下例创建一个 SCROLL 游标，使其通过 LAST、PRIOR、RELATIVE 和 ABSOLUTE 选项支持所有滚动能力。

USE pubs

-- Execute the SELECT statement alone to show the

-- full result set that is used by the cursor.

SELECT au_lname, au_fname FROM authors

ORDER BY au_lname, au_fname

-- Declare the cursor.

DECLARE authors_cursor SCROLL CURSOR FOR

SELECT au_lname, au_fname FROM authors

ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Fetch the last row in the cursor.

FETCH LAST FROM authors_cursor

-- Fetch the row immediately prior to the current row in the cursor.

FETCH PRIOR FROM authors_cursor

-- Fetch the second row in the cursor.

FETCH ABSOLUTE 2 FROM authors_cursor

-- Fetch the row that is three rows after the current row.

FETCH RELATIVE 3 FROM authors_cursor

-- Fetch the row that is two rows prior to the current row.

FETCH RELATIVE -2 FROM authors_cursor

CLOSE authors_cursor

DEALLOCATE authors_cursor

au_lname au_fname

---------------------------------------- --------------------

Bennet Abraham

Blotchet-Halls Reginald

Carson Cheryl

DeFrance Michel

del Castillo Innes

Dull Ann

Green Marjorie

Greene Morningstar

Gringlesby Burt

Hunter Sheryl

Karsen Livia

Locksley Charlene

MacFeather Stearns

McBadden Heather

O'Leary Michael

Panteley Sylvia

Ringer Albert

Ringer Anne

Smith Meander

Straight Dean

Stringer Dirk

White Johnson

Yokomoto Akiko

au_lname au_fname

---------------------------------------- --------------------

Yokomoto Akiko

au_lname au_fname

---------------------------------------- --------------------

White Johnson

au_lname au_fname

---------------------------------------- --------------------

Blotchet-Halls Reginald

au_lname au_fname

---------------------------------------- --------------------

del Castillo Innes

au_lname au_fname

---------------------------------------- --------------------

Carson Cheryl

Top

7 楼pengdali（璇玑的钻石）回复于 2003-06-17 10:15:03 得分 0 @@FETCH_STATUS

返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

返回值描述

0 FETCH 语句成功。

-1 FETCH 语句失败或此行不在结果集中。

-2 被提取的行不存在。

http://wwle.5d.cn/

我有一对翅膀，不是用来飞翔，而是用来煮汤。

评论Comments