1.为何使用游标：使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。

用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。

游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

2.如何使用游标：一般地，使用游标都遵循下列的常规步骤：(1) 声明游标。

把游标与T-SQL语句的结果集联系起来。

(2)打开游标。

(3)使用游标操作数据。

(4)关闭游标。

2.1.声明游标DECLARE CURSOR语句SQL-92标准语法格式：DECLARE游标名[ INSENSITIVE ] [ SCROLL ] CURSORFOR sql-statementEg:Declare MycrsrVar CursorFOR Select * FROM tbMyData2.2打开游标OPEN MycrsrVar当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。

就本例而言，可以使用下列操作读取第1行数据：FETCH FIRST from E1cursor或FETCH NEXT from E1cursor2.3 使用游标操作数据下面的示例用@@FETCH_STATUS控制在一个WHILE循环中的游标活动DECLARE E1cursor cursorFOR SELECT * FROM c_exampleOPEN E1cursorFETCH NEXT from E1cursorWHILE @@FETCH_STATUS = 0BEGINFETCH NEXT from E1cursorENDCLOSE E1cursorDEALLOCATE E1cursor2.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 ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lnamevarchar(40),@message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FORSELECT au_id, au_fname, au_lnameFROM authorsWHERE state = "UT"ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursorINTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0BEGINPRINT " "SELECT @message = "----- Books by Author: " +@au_fname + " " + @au_lnamePRINT @message-- Declare an inner cursor based-- on au_id from the outer cursor.DECLARE titles_cursor CURSOR FORSELECT t.titleFROM titleauthor ta, titles tWHERE ta.title_id = t.title_id ANDta.au_id = @au_id -- Variable value from the outer cursorOPEN titles_cursorFETCH NEXT FROM titles_cursor INTO @titleIF @@FETCH_STATUS <> 0PRINT " <<No Books>>"WHILE @@FETCH_STATUS = 0BEGINSELECT @message = " " + @titlePRINT @messageFETCH NEXT FROM titles_cursor INTO @titleENDCLOSE titles_cursorDEALLOCATE titles_cursor-- Get the next author.FETCH NEXT FROM authors_cursorINTO @au_id, @au_fname, @au_lnameENDCLOSE authors_cursorDEALLOCATE authors_cursorGO2 游标使用方法：公司行政考评数据的处理CREATE PROCEDURE dbo.Toltal_jixiao@firstdata datetime,@enddata datetime,@errorMes varchar(100) outputASupdate Staff_Table set YjxkpCount=40 where not exists (select * from JiXiaoKaoHe_Score where suoshubumen=Staff_Table.Ybumen and kaoheren=Staff_Table.Ynameand kaoheRiQi=@firstdataa nd zhiwu='员工' )and yuefen=@firstdata and Yzhiwu='员工'update Staff_Table set YjxkpCount=50 where not exists (select * from JiXiaoKaoHe_Score where suoshubumen=Staff_Table.Ybumen andk aoheren=Staff_Table.Yname andk aoheRiQi=@firstdataa nd zhiwu<>'员工' )and yuefen=@firstdata and Yzhiwu<>'员工'--==================================================================== ==========================================update Staff_Table set YjxkpCount=40-ok.zf from(select p.kaoheID,(p.cdzt+p.ybyr+p.kq+p.gztd+p.qjia+p.hjws+p.jianzh+p.tdhz+p.baomi+p.gzzhze+p.caiwu+p.shhzr en+p.work_score) zf from(select kaoheID,sum(cdzt_score) as cdzt,sum(ybyr_score) as ybyr,sum(kq_score) as kq,sum(gztd_score) as gztd,sum(qjia_score) as qjia,sum(hjws_score) as hjws,sum(jianzh_score)as jianzh,sum(tdhz_score) as tdhz,sum(baomi_score) as baomi,sum(gzzhze_score) as gzzhze,sum(caiwu_score) as caiwu,sum(shhzren_score) as shhzren,sum(work_score) as work_scorefrom dbo.JiXiaoKaoHe_Score where kaoheRiQi =@firstdata group by kaoheID) p) okwhere Staff_Table.id=ok.kaoheID and Yzhiwu='员工'update Staff_Table set YjxkpCount=50-ok.zf from(select p.kaoheID,(p.cdzt+p.ybyr+p.kq+p.gztd+p.qjia+p.hjws+p.jianzh+p.tdhz+p.baomi+p.gzzhze+p.caiwu+p.shhzr en+p.work_score) zf from(select kaoheID,sum(cdzt_score) as cdzt,sum(ybyr_score) as ybyr,sum(kq_score) as kq,sum(gztd_score) as gztd,sum(qjia_score) as qjia,sum(hjws_score) as hjws,sum(jianzh_score)as jianzh,sum(tdhz_score) as tdhz,sum(baomi_score) as baomi,sum(gzzhze_score) as gzzhze,sum(caiwu_score) as caiwu,sum(shhzren_score) as shhzren,sum(work_score) as work_scorefrom dbo.JiXiaoKaoHe_Score where kaoheRiQi =@firstdata group by kaoheID) p) okwhere Staff_Table.id=ok.kaoheID and Yzhiwu<>'员工'update Staff_Table set YjxkpCount=cast(YjxkpCount asdecimal(18,1)) where yuefen=@firstdata--循环获取迟到早退次数====================================================================== =========================================================declare @i intdeclare @id1 intdeclare cur cursor forselect kaoheID, count(cdzt_score) from JiXiaoKaoHe_Score where kaoheRiQi=@firstdata and cdzt_score<>'' and zhiwu='员工' group by kaoheIDopen curfetch next from cur into @id1,@iwhile @@fetch_status =0beginselect @id1,@i-- 统计员工绩效行政考评数据if @i<6 and @i>2 --3次得beginupdate Staff_Table set YjxkpCount=20 where id=@id1update Staff_Table set YjxkpCount=20-(selectsum(ybyr_score)+sum(kq_score)+sum(gztd_score)+sum(qjia_score)+sum(hjws_score)+sum(jianz h_score)+sum(tdhz_score)+sum(baomi_score)+sum(gzzhze_score)+sum(caiwu_score)+sum(shhzren_score)+sum(work_score)from JiXiaoKaoHe_Scorewhere kaoheRiQi=@firstdata and zhiwu='员工' and kaoheID=@id1 group bykaoheID) where id=@id1--处理小数点问题update Staff_Table set YjxkpCount=cast(YjxkpCount asdecimal(18,1)) where yuefen=@firstdataend else if @i>5 --6次的begin--1、统计迟到早退次数update Staff_Table set YjxkpCount=0 where id=@id1endfetch next from cur into @id1,@iendclose cur --关闭游标deallocate cur--==================================================================== ====================================================================== =================declare @i2 intdeclare @id2 intdeclare cur cursor forselect kaoheID,count(cdzt_score) from JiXiaoKaoHe_Score where kaoheRiQi=@firstdata and cdzt_score<>'' and zhiwu<>'员工' group by kaoheIDopen curfetch next from cur into @id2, @i2while @@fetch_status =0beginselect @id2, @i2-- 统计管理层绩效行政考评数据if @i2<6 and @i2>2 --3次得begin--1、统计迟到早退次数update Staff_Table set YjxkpCount=25 where id=@id2update Staff_Table set YjxkpCount=25-(selectsum(ybyr_score)+sum(kq_score)+sum(gztd_score)+sum(qjia_score)+sum(hjws_score)+sum(jianz h_score)+sum(tdhz_score)+sum(baomi_score)+sum(gzzhze_score)+sum(caiwu_score)+sum(shhzren_score)+sum(work_score)from JiXiaoKaoHe_Scorewhere kaoheRiQi=@firstdata and zhiwu<>'员工' and kaoheID=@id2 group bykaoheID) where id=@id2update Staff_Table set YjxkpCount=cast(YjxkpCount asdecimal(18,1)) where yuefen=@firstdataend else if @i2>5 --6次的begin--1、统计迟到早退次数update Staff_Table set YjxkpCount=0 where id=@id2endfetch next from cur into @id2, @i2endclose curdeallocate cur--计算总的分值update Staff_Table set YzCount = YjxkpCount+YjxhpCount+YzgkpCount where yuefen=@firstdata--执行成功gSuccess:set @errorMes = ''return 0gFail:if @errorMes =''set @errorMes = '统计时异常错误'return -200GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO。