游标使用实例-入门级

最新推荐文章于 2019-05-27 21:49:28 发布

weixin_30918633

最新推荐文章于 2019-05-27 21:49:28 发布

阅读量76

点赞数

原文链接：http://www.cnblogs.com/fangquanjun/p/3833906.html

版权

例子
table1结构如下
id    int
name  varchar(50)

declare @id int
declare @name varchar(50)
declare cursor1 cursor for         --定义游标cursor1
select * from table1               --使用游标的对象(跟据需要填入select文)
open cursor1                       --打开游标

fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中

while @@fetch_status=0           --判断是否成功获取数据
begin
update table1 set name=name+'1'
where id=@id                           --进行相应处理(跟据需要填入SQL文)

fetch next from cursor1 into @id,@name  --将游标向下移1行
end

close cursor1                   --关闭游标
deallocate cursor1

ALTER Trigger [dbo].[Custom_MyTextBooks_Update_IsOpen_Vocabulary]

on [dbo].[Custom_MyTextBooks] for update
as

if update([IsOpen])
begin

declare cursorUpdate Cursor for
select inserted.BookId,inserted.ChildId,inserted.IsOpen IsOpenInserted ,deleted.IsOpen IsOpenDeleted from inserted inner join deleted
on inserted.MyId = deleted.MyId

open cursorUpdate

declare @BookId int,@ChildId int, @IsOpenInserted bit ,@IsOpenDeleted bit

fetch next from cursorUpdate into @BookId ,@ChildId , @IsOpenInserted ,@IsOpenDeleted

while @@FETCH_STATUS = 0
begin

if @IsOpenDeleted = 0 and @IsOpenInserted = 1
begin

declare @ret int;
EXEC @ret = Trigger_Create_Vocabulary_By_MyTextBooks
@ChildId = @ChildId,
@BookId = @BookId
end

fetch next from cursorUpdate into @BookId ,@ChildId , @IsOpenInserted ,@IsOpenDeleted
end

close cursorUpdate
deallocate cursorUpdate

end

转载于:https://www.cnblogs.com/fangquanjun/p/3833906.html