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