sqlserver 游标示例

create proc updatetestuser2
as
declare testcursor cursor for
select login_id,login_name from testuser1
open testcursor
declare @loginid int,@loginname varchar(30)
fetch next from testcursor into @loginid,@loginname
while(@@fetch_status = 0)
begin
update testuser2 set oalogin_id = @loginid where login_name = @loginname
fetch next from testcursor into @loginid,@loginname

end
close testcursor
deallocate testcursor
go

呵呵,因实施现场需要我们研发人员给他们一个批量删除业务数据的SQL,我把他写成存储过程来用,存储过程如下:

--创建存储过程删除入围管理和合作协议相关业务数据
--作者:zengqiang
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE SP_DELETEDATA
GO

CREATE PROCEDURE SP_DELETEDATA AS

DECLARE @changeID int

DECLARE TableID cursor for
select ItemAcceptId from T_ItemAccept
OPEN TableID
fetch next from TableID into @changeID

-- 循环开始
while @@fetch_status = 0
begin
-- 逻辑操作
delete from T_StartInfo where FlowTypeId=2468 and RefId=@changeID
delete from T_ApproveHistory where FlowTypeId=2468 and RefId=@changeID
delete from T_ItemAccept where ItemAcceptId=@changeID
-- 取下一条记录
fetch next from TableID into @changeID
end
-- 循环结束
-- 关闭游标 删除游标引用
close TableID
deallocate TableID

DECLARE TableID2 cursor for
select cooperateId from T_Cooperate
OPEN TableID2
fetch next from TableID2 into @changeID

-- 循环开始
while @@fetch_status = 0
begin
-- 逻辑操作
delete from T_StartInfo where FlowTypeId=668 and RefId=@changeID
delete from T_ApproveHistory where FlowTypeId=668 and RefId=@changeID
delete from T_Cooperate where cooperateId=@changeID
-- 取下一条记录
fetch next from TableID2 into @changeID
end
-- 循环结束
-- 关闭游标 删除游标引用
close TableID2
deallocate TableID2
go
--执行存储过程
execute SP_DELETEDATA
go
--删除存储过程
DROP PROCEDURE SP_DELETEDATA

----------------------------------------------------------------------------------------------------------------------------------------------------

--另一个存储过程

create procedure CreateReplyCode_A_ProC --处理常规的没有批复文号的项目
@ConstructUnitId int, --建设单位ID
@KeyCode varchar(255) --关键字，对应给的那个Excel表！如ConstructUnitId = 401,KeyCode = '三亚项字'
as
set nocount on
declare @Year varchar(255)
declare @SerialNO int
set @SerialNO = 0
declare @SerialNOStr varchar(255)
declare @Len int
declare @I int
set @I = 0
declare @ItemCreatId int
declare @KeyCodeTemp varchar(255)
declare ItemCreatId_Cursor_A cursor for
select ItemCreatId from T_ItemCreate left outer join t_startinfo on t_itemcreate.itemcreatId = t_startinfo.refid
and t_startinfo.flowtypeid = 1 and t_startinfo.docstate = 3 where (replycode is null or replycode = '')and ConstructUnitId = @ConstructUnitId
open ItemCreatId_Cursor_A
fetch next from ItemCreatId_Cursor_A into @ItemCreatId
while @@fetch_status = 0
begin
select @Year = PlanYear from T_ItemCreate inner join T_yearplan on T_YearPlan.YearPlanid=T_ItemCreate.YearPlanId where T_ItemCreate.ItemCreatId = @ItemCreatId
begin
set @SerialNO = @SerialNo + 1
set @SerialNoStr = CAST(@SerialNo as varchar(255))

set @Len = LEN(@SerialNoStr)
while @I < (4 - @Len)
begin
set @SerialNOStr = '0'+@SerialNOStr
if LEN(@SerialNOStr) = 4
break
else
continue
end
set @KeyCodeTemp = @KeyCode+'['+@Year+']'+@SerialNOStr
update T_ItemCreate set ReplyCode = @KeyCodeTemp where ItemCreatId = @ItemCreatId
end
fetch next from ItemCreatId_Cursor_A into @ItemCreatId
end
close ItemCreatId_Cursor_A
deallocate ItemCreatId_Cursor_A

/*****************************************************************************************/

create procedure CreateReplyCode_B_ProC --处理建设单位为17的没有批复文号的项目
@ConstructUnitId int, --建设单位ID为17(只能输入17)
@ConstructDeptId int, --建设单位为17的取取省公司部门ID
@KeyCode varchar(255) --关键字，对应给的那个Excel表！如ConstructDeptId = 14,KeyCode = '计建项字'
as
set nocount on
declare @Year varchar(255)
declare @SerialNO int
set @SerialNO = 0
declare @SerialNOStr varchar(255)
declare @Len int
declare @I int
set @I = 0
declare @ItemCreatId int
declare @KeyCodeTemp varchar(255)
declare ItemCreatId_Cursor_B cursor for
select ItemCreatId from T_ItemCreate left outer join t_startinfo on t_itemcreate.itemcreatId = t_startinfo.refid
and t_startinfo.flowtypeid = 1 and t_startinfo.docstate = 3 where (replycode is null or replycode = '')and ConstructUnitId = @ConstructUnitId and ConstructDeptId = @ConstructDeptId
open ItemCreatId_Cursor_B
fetch next from ItemCreatId_Cursor_B into @ItemCreatId
while @@fetch_status = 0
begin
select @Year = PlanYear from T_ItemCreate inner join T_yearplan on T_YearPlan.YearPlanid=T_ItemCreate.YearPlanId where T_ItemCreate.ItemCreatId = @ItemCreatId
begin
set @SerialNO = @SerialNo + 1
set @SerialNoStr = CAST(@SerialNo as varchar(255))

set @Len = LEN(@SerialNoStr)
while @I < (4 - @Len)
begin
set @SerialNOStr = '0'+@SerialNOStr
if LEN(@SerialNOStr) = 4
break
else
continue
end
set @KeyCodeTemp = @KeyCode+'['+@Year+']'+@SerialNOStr
update T_ItemCreate set ReplyCode = @KeyCodeTemp where ItemCreatId = @ItemCreatId
end
fetch next from ItemCreatId_Cursor_B into @ItemCreatId
end
close ItemCreatId_Cursor_B
deallocate ItemCreatId_Cursor_B