首页 > 代码库 > 游标使用实例-入门级

游标使用实例-入门级

例子
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