sqlserver游标的使用示例

use soft--打开数据库
--判断存储过程是否存在
SET NOCOUNT On
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[yearCountTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yearCountTest]
go

--创建存储过程
create procedure yearCountTest
(@currentYear int)
as
begin
---创建临时表

SET NOCOUNT ON
---创建临时表
IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#TempTable%' AND type='U')
DROP TABLE #TempTable ---删除临时表
CREATE TABLE #TempTable
(
employeeID int  primary key,
employeeName varchar(50),
oneMonth int,
towMonth int,
threeMonth int,
fourMonth int,
fiveMonth int,
sixMonth int,
sevenMonth int,
eightMonth int,
nineMonth int,
tenMonth int,
elevenMonth int,
twelveMonth int
)
---创建临时游标
DECLARE myCursor CURSOR
for select employeeID,employee_name from employee
--定义临时变量
declare @month int --月份变量
declare @total int --统计数目
declare @empID int --员工id
declare @empName varchar(50)  --员工姓名

--打开游标
open myCursor
--游标向前移动一行
fetch next from myCursor into @empID,@empName
while @@fetch_status=0
begin
 if (select employeeID from #TempTable where employeeID=@empID)is null
 begin

 insert into #TempTable(employeeID,employeeName)values(@empID,@empName)
 set @month = 1
 while @month<13 --逐月循环
 begin
      

SELECT @total=count(*) from soft_look where replace(开发者,' ','@') LIKE '%@' + CONVERT(VARCHAR(10), @empID) + '@%'  and month(著作权申报日期)= @month and year(著作权申报日期)=@currentYear

   if @month=1
 update #TempTable set oneMonth=@total where employeeID=@empID
 else if @month=2
 update #TempTable set towMonth=@total where employeeID=@empID
 else if @month=3
 update #TempTable set threeMonth=@total where employeeID=@empID
 else if @month=4
 update #TempTable set fourMonth=@total where employeeID=@empID
 else if @month=5
 update #TempTable set fiveMonth=@total where employeeID=@empID
 else if @month=6
 update #TempTable set sixMonth=@total where employeeID=@empID
 else if @month=7
 update #TempTable set sevenMonth=@total where employeeID=@empID
 else if @month=8
 update #TempTable set eightMonth=@total where employeeID=@empID
 else if @month=9
 update #TempTable set nineMonth=@total where employeeID=@empID
 else if @month=10
 update #TempTable set tenMonth=@total where employeeID=@empID
 else if @month=11
 update #TempTable set elevenMonth=@total where employeeID=@empID
 else if @month=12
 update #TempTable set twelveMonth=@total where employeeID=@empID

 set @month=@month+1
 end
 end
 else
 print CAST(@empID AS VARCHAR(10))+'已存在'

 fetch next from myCursor into @empID,@empName

end

close myCursor --关闭游标
deallocate myCursor --删除游标

select * from  #TempTable
SET NOCOUNT OFF
end
go

execute yearCountTest 2007