2023学年完整公开课版游标(SQLServer)

游标内容游标概念、特点、种类使用游标游标示例Q&A1.1游标概念如何从某一结果集中逐一地读取一条记录？游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制游标总是与一条T_SQL选择语句相关联游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通…游标当前行指针游标结果集1.2游标特点允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。提供在脚本、存储过程和触发器中使用的访问结果集中的数据的Transact-SQL语句1.3游标种类Transact_SQL游标 由DECLARECURSOR语法定义主要用在Transact_SQL脚本，存储过程和触发器中API游标 支持在OLEDBODBC以及DB_library中使用游标函数，主要用在服务器上。客户游标 主要是当在客户机上缓存结果集时才使用2使用游标是否声明游标打开游标提取数据处理完成？关闭游标释放资源2.1声明游标DECLAREcursor_nameCURSOR

[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]][;]SQL-92语法

DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]2.2打开游标OPEN{cursor_name|cursor_variable_name}2.3提取游标

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]@@FETCH_STATUS可以使用@@FETCH_STATUS全局变量判断数据提取的状态。@@FETCH_STATUS返回FETCH语句执行后的游标最终状态。返回值含义0FETCH语句成功。-1FETCH语句失败或此行不在结果集中。-2被提取的行不存在。2.4关闭游标CLOSE{cursor_name|cursor_variable_name}在使用CLOSE语句关闭某游标后，系统并没有完全释放游标的资源，并且也没有改变游标的定义，当再次使用OPEN语句时可以重新打开此游标。2.5释放游标释放分配给游标的所有资源。

DEALLOCATE{cursor_name| cursor_variable_name}释放游标就释放了与该游标有关的一切资源，包括游标的声明，以后就不能再使用OPEN语句打开此游标了2.6游标变量declaretitleauthor_curscrollcursorforselect*fromtitleauthor使用SET语句将一游标赋值给游标变量declare@cur_tacursorset@cur_ta=titleauthor_cur利用游标修改数据SQLServer中的UPDATE语句和DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行UPDATE语句的格式为： UPDATEtable_nameSET列名=表达式}[,…n]WHERECURRENTOFcursor_nameDELETE语句的格式为： DELETEFROMtable_nameWHERECURRENTOFcursor_name说明:CURRENTOFcursor_name：表示当前游标指针所指的当前行数据。CURRENTOF只能在UPDATE和DELETE语句中使用注意：使用游标修改基表数据的前提是声明的游标是可更新的对相应的数据库对象（游标的基表）有修改和删除权限3游标示例

示例1.pubs数据库的authors表，声明一个处理姓以字母“

B”开头的作者的游标。DECLAREauthors_cursorCURSORFORSELECTau_lnameFROMauthorsWHEREau_lnameLIKE'B%'ORDERBYau_lnameOPENauthors_cursor--首先提取第一行数据FETCHNEXTFROMauthors_cursor示例1（续）WHILE@@FETCH_STATUS=0BEGINFETCHNEXTFROMauthors_cursorENDCLOSEauthors_cursorDEALLOCATEauthors_cursorGO示例2.与例1类似，只是将FETCH语句的输出存储在局部变量中返回给客户端--声明用于存储FETCH返回结果的局部变量DECLARE@au_lnamevarchar(40),

@au_fnamevarchar(20)DECLAREauthors_cursorCURSORFORSELECTau_lname,au_fnameFROMauthorsWHEREau_lnameLIKE'B%'ORDERBYau_lname,au_fnameOPENauthors_cursor--首先提取第一行数据，并将结果保存到局部变量中FETCHNEXTFROMauthors_cursorINTO@au_lname,@au_fname

示例2（续）

WHILE@@FETCH_STATUS=0BEGIN--将当前行值连接成一个字符串

PRINT'Author:'+@au_fname+''+@au_lname

--提取下一行数据

FETCHNEXTFROMauthors_cursorINTO@au_lname，@au_fnameENDCLOSEauthors_cursorDEALLOCATEauthors_cursorGO示例3.声明带SCROLL选项的游标DECLAREauthors_cursorSCROLLCURSORFORSELECTau_lname，au_fnameFROMauthorsORDERBYau_lname，au_fnameOPENauthors_cursorSETNOCOUNTON--提取游标中的第一行数据FETCHLASTFROMauthors_cursor示例3（续）--提取游标中的第四行数据FETCHABSOLUTE4FROMauthors_cursor--提取当前行后边的第三行数据FETCHRELATIVE3FROMauthors_cursor--提取当前行前边的第二行数据FETCHRELATIVE-2FROMauthors_cursorCLOSEauthors_cursorDEALLOCATEauthors_cursorGO示例4建立生成报表的游标。利用pubs数据库的jobs表和employee表，生成显示如下报表形式的游标：首先列出一项工作的工作编号和工作描述然后在此项工作下列出干此项工作的雇员的姓名（fname加lname）、工作级别（job_lvl）和受雇日期(hire_date)然后再列出第二项工作的工作编（job_id）号和工作描述（job_desc），然后在此项工作下列出干此项工作的雇员的姓名、工作级别和受雇日期依此类推，直到列出全部工作示例4DECLARE@jobidsmallint,@jobdescvarchar(50)DECLARE@namevarchar(50),@joblvlsmallint,@hdatesmalldatetime--声明查询工作表全部数据的游标DECLAREcur_jobcursorforselectjob_id,job_descfromjobsorderbyjob_idascOPENcur_jobFETCHNEXTFROMcur_jobinto@jobid,@jobdescWHILE@@FETCH_STATUS=0BEGIN--显示当前的工作编号及工作描述

PRINTcast(@jobidasvarchar(4))+':'+@jobdesc--声明查询从事此项工作的全体雇员的游标

DECLAREcur_empcursorforselectfname+''+lname,job_lvl,hire_datefromemployeewherejob_id=@jobidOPENcur_empFETCHNEXTFROMcur_empinto@name,@joblvl,@hdateWHILE@@FETCH_STATUS=0BEGINPRINT'雇员：'+@name+',级别：'+cast(@joblvlasvarchar(4))+',参加工作日期：'+datename(year,@hdate)+'年'+datename(mm,@hdate)+'月'+datename(day,@hdate)+'日'FETCHNEXTFROMcur_empinto@name,@joblvl,@hdateENDPRINT'================================='--关闭并释放雇员游标

CLOSEcur_empDEALLOCATEcur_empFETCHNEXTFROMcur_jobinto@jobid,@jobdescEND--关闭并释放工作游标CLOSEcur_jobDEALLOCATEcur_jobGO示例5对pubs数据库的titles表，查询每种类型的图书中价格最贵的前2种图书的书号（title_id）、类型（type）和价格（price）（不考虑只有一本图书的类型）示例5--声明保存两行书号、类型和价格的局部变量DECLARE@title_id1char(6),@type1char(12),@price1moneyDECLARE@title_id2char(6),@type2char(12),@price2money--声明查询titles表中数据的游标，游标的内容为按图书的类型和价格降序排序的结果DECLAREcurcursorforselecttitle_id,type,pricefromtitlesorderbytypeasc,pricedesc--定义保存处理结果的临时表createtable#t(title_idchar(6),typechar(12),pricemoney)OPENcurFETCHNEXTfromcurinto@title_id1,@type1,@price1IF@price1isnotnull--如果价格不为空，则此种类型的第一行数据（即价格最高的）插入到临时表中

INSERTINTO#tvalues(@title_id1,@type1,@price1)WHILE@@fetch_status=0BEGINFETCHNEXTfromcurinto@title_id2,@type2,@price2WHILE@price2isnulland@@fetch_status=0--找到非空的价格

FETCHNEXTfromcurinto@title_id2,@type2,@price2IF@type1=@type2--如果是同类型的图书

IF@price1isnotnull--将此种类型的第二行数据（即价格第二高的）插入到临时表中

INSERTINTO#tvalues(@title_id2,@type2,@price2)--跳过此种类型的其余图书，直到达到一种新的图书类型

WHILE@@fetch_status=0and@type1=@type2FETCHNEXTfromcurinto@title_id2,@type2,@price2IF@@fetch_status=0BEGINset@title_id1=@title_id2set@type1=@type2set@price1=@price2IF@price1isnotnullinsertinto#tvalues(@title_id1,@type1,@price1)END ENDCLOSEcurDEALLOCATECurSELECT*FROM#tDROPTABLE#t示例6-删除数据的游标设有工作表，结构如下：Job(Jobidchar(4)primarykey,--工作编号

descvarchar(40),--工作描述

lvltinyint)--工作级别设此表包含的数据如下表所示，现希望将工作级别相同的工作只保留一项，保留工作编号小的一项工作，删除工作编号大的工作JobiddesclvlJ01软件开发10J02硬件开发12J03软件测试10J04硬件维护8J05硬件测试12DECLARE@jid_1varchar(4),@jid_2varchar(4),@lvl_1int,@lvl_2int--将job中的全部数据按级别和工作编号排序后放置到临时表中SELECT*INTO#new_jobFROMjoborderbylvl,jobid--声明游标DECLAREcur_jobcursorDYNAMICforselectjobid,lvlfrom#new_jobOPENcur_jobFETCHNEXTFROMcur_