SQLServer教程详细学习游标I.docx
- 文档编号:2302729
- 上传时间:2023-05-03
- 格式:DOCX
- 页数:13
- 大小:170.98KB
SQLServer教程详细学习游标I.docx
《SQLServer教程详细学习游标I.docx》由会员分享,可在线阅读,更多相关《SQLServer教程详细学习游标I.docx(13页珍藏版)》请在冰点文库上搜索。
SQLServer教程详细学习游标I
SQLServer教程:
详细学习游标
(1)
游标(Cursor)是处理数据的一种方法,为了查看或者处理结果集中的数据,游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。
我们可以把游标当作一个指针,它可以指定结果中的任何位置,然后允许用户对指定位置的数据进行处理。
1.游标的组成
游标包含两个部分:
一个是游标结果集、一个是游标位置。
游标结果集:
定义该游标得SELECT语句返回的行的集合。
游标位置:
指向这个结果集某一行的当前指针。
2.游标的分类
游标共有3类:
1.API服务器游标
2.Transaction-SQL游标
3.API客户端游标。
其中前两种游标都是运行在服务器上的,所以又叫做服务器游标。
API服务器游标
API服务器游标主要应用在服务上,当客户端的应用程序调用API游标函数时,服务器会对API函数进行处理。
使用API函数和方法可以实现如下功能:
1.打开一个连接。
2.设置定义游标特征的特性或属性,API自动将游标影射到每个结果集。
3.执行一个或多个Transaction-SQL语句。
4.使用API函数或方法提取结果集中的行。
API服务器游标包含以下四种:
静态游标、动态游标、只进游标、键集驱动游标(Primarykey)
∙静态游标的完整结果集将打开游标时建立的结果集存储在临时表中,(静态游标始终是只读的)。
静态游标具有以下特点:
总是按照打开游标时的原样显示结果集;不反映数据库中作的任何修改,也不反映对结果集行的列值所作的更改;不显示打开游标后在数据库中新插入的行;组成结果集的行被其他用户更新,新的数据值不会显示在静态游标中;但是静态游标会显示打开游标以后从数据库中删除的行。
∙动态游标与静态游标相反,当滚动游标时动态游标反映结果集中的所有更改。
结果集中的行数据值、顺序和成员每次提取时都会改变。
∙只进游标不支持滚动,它只支持游标从头到尾顺序提取数据行。
注意:
只进游标也反映对结果集所做的所有更改。
∙键集驱动游标同时具有静态游标和动态游标的特点。
当打开游标时,该游标中的成员以及行的顺序是固定的,键集在游标打开时也会存储到临时工作表中,对非键集列的数据值的更改在用户游标滚动的时候可以看见,在游标打开以后对数据库中插入的行是不可见的,除非关闭重新打开游标。
Transaction-SQL游标
该游标是基于DeclareCursor语法,主要用于Transaction-SQL脚本、存储过程以及触发器中。
Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。
在存储过程或触发器中使用Transaction-SQL游标的过程为:
1.声明Transaction-SQL变量包含游标返回的数据。
为每个结果集列声明一个变量。
声明足够大的变量来保存列返回的值,并声明变量的类型为可从数据类型隐式转换得到的数据类型。
2.使用DeclareCursor语句将Transaction-SQL游标与Select语句相关联。
还可以利用DeclareCursor定义游标的只读、只进等特性。
3.使用Open语句执行Select语句填充游标。
4.使用FetchInto语句提取单个行,并将每列中得数据移至指定的变量中。
注意:
其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。
Transaction-SQL游标不支持提取行块。
5.使用Close语句结束游标的使用。
注意:
关闭游标以后,该游标还是存在,可以使用Open命令打开继续使用,只有调用Deallocate语句才会完全释放。
客户端游标
该游标将使用默认结果集把整个结果集高速缓存在客户端上,所有的游标操作都在客户端的高速缓存中进行。
注意:
客户端游标只支持只进和静态游标。
不支持其他游标。
3.游标的生命周期
游标的生命周期包含有五个阶段:
声明游标、打开游标、读取游标数据、关闭游标、释放游标。
声明游标是为游标指定获取数据时所使用的Select语句,声明游标并不会检索任何数据,它只是为游标指明了相应的Select语句。
Declare游标名称Cursor参数
声明游标的参数
1.Local与Global:
Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。
Global表示的是该游标作用域是整个会话层。
由连接执行的任何存储过程、批处理等都可以引用该游标名称,仅在断开连接时隐性释放。
2.Forward_only与Scroll:
前者表示为只进游标,后者表示为可以随意定位。
默认为前者。
3.Static、Keyset与Dynamic:
第一个表示定义一个游标,其数据存放到一个临时表内,对游标的所有请求都从临时表中应答,因此,对该游标进行提取操作时返回的数据不反映对基表所作的修改,并且该游标不允许修改。
Keyset表示的是,当游标打开时,键集驱动游标中行的身份与顺序是固定的,并把其放到临时表中。
Dynamic表示的是滚动游标时,动态游标反映对结果集内所有数据的更改。
4.Read_only、Scroll_Locks与Optimistic:
第一个表示的是只读游标,第二个表示的是在使用的游标结果集数据上放置锁,当行读取到游标中然后对它们进行修改时,数据库将锁定这些行,以保证数据的一致性。
Optimistic的含义是游标将数据读取以后,如果这些数据被更新了,则通过游标定位进行的更新与删除操作将不会成功。
标准游标:
DeclareMyCursorCursor
ForSelect*FromMaster_Goods
只读游标
DeclareMyCusrorCursor
ForSelect*FromMaster_Goods
ForReadOnly
可更新游标
DeclareMyCusrorCursor
ForSelect*FromMaster_Goods
ForUpDate
打开游标使用Open语句用于打开Transaction-SQL服务器游标,执行Open语句的过程中就是按照Select语句进行填充数据,打开游标以后游标位置在第一行。
打开游标
∙全局游标:
OpenGlobalMyCursor
∙局部游标:
OpenMyCursor
读取游标数据:
在打开游标以后,使用Fetch语句从Transaction-SQL服务器游标中检索特定的一行。
使用Fetch操作,可以使游标移动到下一个记录,并将游标返回的每个列得数据分别赋值给声明的本地变量。
Fetch[Next|Prior|First|Last|Absolute n |Relative n ]FromMyCursor
Into@GoodsID,@GoodsName
其中:
Next表示返回结果集中当前行的下一行记录,如果第一次读取则返回第一行。
默认的读取选项为Next
Prior表示返回结果集中当前行的前一行记录,如果第一次读取则没有行返回,并且把游标置于第一行之前。
First表示返回结果集中的第一行,并且将其作为当前行。
Last表示返回结果集中的最后一行,并且将其作为当前行。
Absolute n 如果n为正数,则返回从游标头开始的第n行,并且返回行变成新的当前行。
如果n为负,则返回从游标末尾开始的第n行,并且返回行为新的当前行,如果n为0,则返回当前行。
Relative n 如果n为正数,则返回从当前行开始的第n行,如果n为负,则返回从当前行之前的第n行,如果为0,则返回当前行。
关闭游标调用的是Close语句,方式如下:
CloseGlobalMyCursor CloseMyCursor
释放游标调用的是Deallocate语句,方法如下:
DeallocateGlboalMyCursor DeallocateMyCursor
游标实例:
DeclareMyCusrorCursorScroll
ForSelect*FromMaster_GoodsOrderByGoodsID
OpenMyCursor
FetchnextFromMyCursor
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
FetchnextFromMyCursor
Into@GoodsCode,@GoodsName
End
CloseMyCursor
DeallocateMyCursor
修改当前游标的数据方法如下:
UpDateMaster_GoodsSetGoodsName='yangyang8848'WhereCurrentOfMyCursor;
删除当前游标行数据的方法如下:
DeleteFromMaster_GoodsWhereCurrentOfMyCursor
Select@@CURSOR_ROWS可以得到当前游标中存在的数据行数。
注意:
此变量为一个连接上的全局变量,因此只对应最后一次打开的游标。
SQLServer游标(cursor)
1.基本概念
应用程序,特别是交互应用程序,并不总能将SQL语句访问数据库所返回的结果集(如select操作返回的查询结果)作为一个整体单元来处理。
这些应用程序需要一种机制,这样就可以每次处理一行。
游标就是提供这种机制的结果扩展集。
游标是由结果集(可以是零条、一条或由相关的select语句检索出的多条记录)和结果集集中指向特定记录的游标位置组成,游标的作用类似C语言中的指针。
游标能够遍历结果的所有行,它一次只指向一行。
游标通过下面方式扩展结果处理:
●允许定位在结果集的特定行。
●从结果集的当前位置检索一行或多行。
●支持对结果集中当前位置的行进行数据修改。
●为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。
游标类似于C语言指针一样的语言结构。
数据库执行的大多数的SQL命令都是同时处理集合内部的所有数据。
加入需要对数据的特定行进行操作,在没有游标的情况下,这种工作不得不放到数据库前端,用其它语言来实现,这将降低整个程序的速度和效率。
如果使用游标可以在服务器端有效的解决这些问题。
2.游标种类
SQLSERVER支持三种类型的游标:
Transact_SQL游标,API服务器游标和客户游标。
(1)Transact_SQL游标
Transact_SQL游标是由DECLARECURSOR语法定义、主要用在Transact_SQL脚本、存储过程和触发器中。
Transact_SQL游标主要用在服务器上,由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。
Transact_SQL游标不支持提取数据块或多行数据。
(2)API游标
API游标支持在OLEDB,ODBC以及DB_library中使用游标函数,主要用在服务器上。
每一次客户端应用程序调用API游标函数,MSSQLSEVER的OLEDB提供者、ODBC驱动器或DB_library的动态链接库(DLL)都会将这些客户请求传送给服务器以对API游标进行处理。
(3)客户游标
客户游标主要是当在客户机上缓存结果集时才使用。
在客户游标中,有一个缺省的结果集被用来在客户机上缓存整个结果集。
客户游标仅支持静态游标而非动态游标。
由于服务器游标并不支持所有的Transact-SQL语句或批处理,所以客户游标常常仅被用作服务器游标的辅助。
因为在一般情况下,服务器游标能支持绝大多数的游标操作。
由于API游标和Transact-SQL游标使用在服务器端,所以被称为服务器游标,也被称为后台游标,而客户端游标被称为前台游标。
在本章中我们主要讲述服务器(后台)游标。
3.游标的构成
每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序:
1)DECLARE游标
2)OPEN游标
3)从一个游标中FETCH信息
4)CLOSE或DEALLOCATE游标
通常我们使用DECLARE来声明一个游标声明一个游标主要包括以下主要内容:
游标名字
数据来源(表和列)
选取条件
属性(仅读或可修改)
语法:
DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR
FORselect_statement
[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]
其中:
cursor_name
指游标的名字。
INSENSITIVE
表明MSSQLSERVER会将游标定义所选取出来的数据记录存放在一临时表内(建立在tempdb数据库下)。
对该游标的读取操作皆由临时表来应答。
因此,对基本表的修改并不影响游标提取的数据,即游标不会随着基本表内容的改变而改变,同时也无法通过
游标来更新基本表。
如果不使用该保留字,那么对基本表的更新、删除都会反映到游标中。
例创建和使用游标
在这一步中,需要指定游标的属性和根据要求产生的结果集。
有两种方法可以指定一个游标。
形式1(ANSI92)
DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR
FORselect_statement
[FOR{READONLY|UPDATE][OFcolumn_list]}]
形式2
DECLAREcursor_nameCURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[STATIC|KEYSET|DYNAMIC]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
FORselect_statement
[FOR{READONLY|UPDATE][OFcolumn_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝,以后的数据从这个临时拷贝中获取。
如果在后来游标处理的过程中,原有基表中数据发生了改变,那么它们对于该游标而言是不可见的。
这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。
所有的fetch选项(first、last、next、relative、absolute)都可以在游标中使用。
如果忽略该选项,则游标只能向前滚动(next)。
Select_statement指明SQL语句建立的结果集。
TransactSQL语句COMPUTE、COMPUTEBY、FORBROWSE和INTO在游标声明的选择语句中不允许使用。
READONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OFcolumn_list指明结果集中可以进行修改的列。
缺省情况下(使用UPDATE关键字),所有的列都可进行修改。
LOCAL关键字指明游标是局部的,它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。
全局的游标在连接激活的任何时候都是可用的。
只有当连接结束时,游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。
SQLServer将从结果集中创建一个临时关键字集。
如果对数据库的非关键字列进行了修改,则它们对游标是可见的。
因为是固定的关键字集合,所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功,而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意:
·如果在SELECT语句中使用了DISTINCT、UNION、GROUPBY语句,且在选择中包含了聚合表达式,则游标自动为INSENSITIVE的游标。
·如果基表没有唯一的索引,则游标创建成INSENSITIVE的游标。
·如果SELECT语句包含了ORDERBY,而被ORDERBY的列并非唯一的行标识,则DYNAMIC游标将转换成KEYSET游标。
如果KEYSET游标不能打开,则将转换成INSENSITIVE游标。
使用SQLANSI-92语法定义的游标同样如此,只是没有INSENSITIVE关键字而已。
ii.打开游标
打开游标就是创建结果集。
游标通过DECLARE语句定义,但其实际的执行是通过OPEN语句。
语法如下:
OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
[Page]
Cursor_variable_name是所引用游标的变量名。
该变量应该为游标类型。
在游标被打开之后,系统变量@@cursor_rows可以用来检测结果集的行数。
@@cursor_rows为负数时,表示游标正在被异步迁移,其绝对值(如果@@cursor_rows为-5,则绝对值为5)为当前结果集的行数。
异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
iii.从游标中取值
在从游标中取值的过程中,可以在结果集中的每一行上来回移动和处理。
如果游标定义成了可滚动的(在声明时使用SCROLL关键字),则任何时候都可取出结果集中的任意行。
对于非滚动的游标,只能对当前行的下一行实施取操作。
结果集可以取到局部变量中。
Fetch命令的语法如下:
FETCH[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]
FROM[GLOBAL]cursor_name}|cursor_variable_name}
[INTO@variable_name][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTEn表示结果集中的第n行,该行数同样可以通过一个局部变量传播。
行号从0开始,所以n为0时不能得到任何行。
RELATIVEn表示要取出的行在当前行的前n行或后n行的位置上。
如果该值为正数,则要取出的行在当前行前n行的位置上,如果该值为负数,则返回当前行的后n行。
INTO@cursor_variable_name表示游标列值存储的地方的变量列表。
该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。
变量的数据类型也应该与被选择列的数据类型相同。
直到下一次使用FETCH语句之前,变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。
如果FETCH成功,则@@fetch_status被设置成0。
@@fetch_status为-1表示已经到达了结果集的一部分(例如,在游标被打开之后,基表中的行被删除)。
@@fetch_status可以用来构造游标处理的循环。
例如:
DECLARE@inamechar(20),@fnamechar(20)
OPENauthor_cur
FETCHFIRSTFROMauthor_curINTO@iname,@fname
WHILE@@fetch_status=0
BEGIN
IF@fname=‘Albert’
PRINT“FoundAlbertRinger”
ELSE
Print“OtherRinger”
FETCHNEXTFROMauthor_curINTO@iname,@fname
END
iv.关闭游标
CLOSE语句用来关闭游标并释放结果集。
游标关闭之后,不能再执行FETCH操作。
如果还需要使用FETCH语句,则要重新打开游标。
语法如下:
CLOSE[GLOBAL]cursor_name|cursor_variable_name
v.释放游标
游标使用不再需要之后,要释放游标。
DEALLOCATE语句释放数据结构和游标所加的锁。
语法如下:
DEALLOCATE[GLOBAL]cursor_name|cursor_variable_name
下面给出游标的一个完整的例子:
[Page]
USEmaster
GO
CREATEPROCEDUREsp_BuildIndexes
AS
DECLARE@TableNamesysname,@msgvarchar(100),@cmdvarchar(100)
DECLAREtable_curCURSORFOR
SELECTnameFROMsysobjectsWHEREtype=’u’
OPENtable_cur
FETCHNEXTFROMtable_curINTO@TableName
WHILE@@fetch_status=0
BEGIN
IF@@fetch_status=-2
CONTINUE
SELECT@msg=“Buildingindexesfortable”+@TableName+”…”
PRINT@msg
SELECT@cmd=“DBCCDBREINDEX(‘”+@TableName+”’)”
EXEC(@cmd)
PRINT““
FETCHNEXTFROMtable_curINTO@TableName
END
DEALLOCATEtable_cur
GO
下面的脚本将为PUBS数据库执行sp_BuildIndexes
USEpubs
GO
EXECap_BuildIndexes
注意:
上面也是创建用户定义的系统存储过程的示例。
使用临时表
临时表是在TempDB中创建的表。
临时表的名称都以“#”开头。
临时表的范围为创建临时表的连接。
因为,临时表不能在两个连接之间共享,一
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 教程 详细 学习 游标
![提示](https://static.bingdoc.com/images/bang_tan.gif)