在Sql Server存储过程中使用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

FOR SELECT * FROM c_example

OPEN E1cursor

FETCH NEXT from E1cursor

WHILE @@FETCH_STATUS = 0

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 " <>"

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, @au_fname, @au_lname

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

GO

2 游标使用方法：公司行政考评数据的处理

CREATE PROCEDURE dbo.Toltal_jixiao

@firstdata datetime,

@enddata datetime,

@errorMes varchar(100) output

AS

update Staff_Table set YjxkpCount=40 where not exists (select * from JiXiaoKaoHe_Score where suoshubumen=Staff_Table.Ybumen and kaoheren=Staff_Table.Yname

and kaoheRiQi=@firstdata

a 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 and

k aoheren=Staff_Table.Yname and

k aoheRiQi=@firstdata

a 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_score

from dbo.JiXiaoKaoHe_Score where kaoheRiQi =@firstdata group by kaoheID) p

) ok

where 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_score

from dbo.JiXiaoKaoHe_Score where kaoheRiQi =@firstdata group by kaoheID) p

) ok

where Staff_Table.id=ok.kaoheID and Yzhiwu<>'员工'

update Staff_Table set YjxkpCount=cast(YjxkpCount as

decimal(18,1)) where yuefen=@firstdata

--循环获取迟到早退次数

====================================================================== =========================================================

declare @i int

declare @id1 int

declare cur cursor for

select kaoheID, count(cdzt_score) from JiXiaoKaoHe_Score where kaoheRiQi=@firstdata and cdzt_score<>'' and zhiwu='员工' group by kaoheID

open cur

fetch next from cur into @id1,@i

while @@fetch_status =0

begin

select @id1,@i

-- 统计员工绩效行政考评数据

if @i<6 and @i>2 --3次得

begin

update Staff_Table set YjxkpCount=20 where id=@id1

update Staff_Table set YjxkpCount=20-(select

sum(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_Score

where kaoheRiQi=@firstdata and zhiwu='员工' and kaoheID=@id1 group by

kaoheID) where id=@id1

--处理小数点问题

update Staff_Table set YjxkpCount=cast(YjxkpCount as

decimal(18,1)) where yuefen=@firstdata

end else if @i>5 --6次的

begin

--1、统计迟到早退次数

update Staff_Table set YjxkpCount=0 where id=@id1

end

fetch next from cur into @id1,@i

end

close cur --关闭游标

deallocate cur

--==================================================================== ====================================================================== =================

declare @i2 int

declare @id2 int

declare cur cursor for

select kaoheID,count(cdzt_score) from JiXiaoKaoHe_Score where kaoheRiQi=@firstdata and cdzt_score<>'' and zhiwu<>'员工' group by kaoheID

open cur

fetch next from cur into @id2, @i2

while @@fetch_status =0

begin

select @id2, @i2

-- 统计管理层绩效行政考评数据

if @i2<6 and @i2>2 --3次得

begin

--1、统计迟到早退次数

update Staff_Table set YjxkpCount=25 where id=@id2

update Staff_Table set YjxkpCount=25-(select

sum(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_Score

where kaoheRiQi=@firstdata and zhiwu<>'员工' and kaoheID=@id2 group by

kaoheID) where id=@id2

update Staff_Table set YjxkpCount=cast(YjxkpCount as

decimal(18,1)) where yuefen=@firstdata

end else if @i2>5 --6次的

begin

--1、统计迟到早退次数

update Staff_Table set YjxkpCount=0 where id=@id2

end

fetch next from cur into @id2, @i2

end

close cur

deallocate cur

--计算总的分值

update Staff_Table set YzCount = YjxkpCount+YjxhpCount+YzgkpCount where yuefen=

@firstdata

--执行成功

gSuccess:

set @errorMes = ''

return 0

gFail:

if @errorMes =''

set @errorMes = '统计时异常错误'

return -200

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO