游标

--示例1
USE [ceshi0827]
GO
/****** Object:  StoredProcedure [dbo].[pro_cursor111]    Script Date: 09/02/2015 18:21:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pro_cursor111]
as
begin
--声明一个全局游标
declare mycursor cursor for 
select sid from score 
--打开游标
open mycursor
--声明一个变量
declare @sid int
--循环移动
fetch next from mycursor into @sid
while(@@fetch_status=0)
  begin
    update score set score=score+10
where sid=@sid
    fetch next from mycursor into @sid
  end
close mycursor
deallocate mycursor
end


--示例2
--创建数据库
 
create proc InsertStudent
 
as
 
--定义所需要的变量
 
declare @SchoolID int
declare @ClassID int
declare @StudentID int
declare @IDNumber int  --条件判断时需要
begin
 
--创建游标
 
declare FeeInsertStudent cursor for
select stuID,SchID,ClasID from T_School 
--打开游标 
 
open FeeInsertStudent 
 
--从游标里取出数据给 变量 赋值
fetch next from FeeInsertStudent into @SchoolID,@ClassID,@StudentID
--判断有标的状态
 
while @@FETCH_STATUS=0
 
begin
 
--为变量赋值
 
set @IDNumber=(select count(*) from T_LeaveSchool where StudentID=@StudentID and SchoolID=@SchoolID and ClassId=@ClassID)
if(@IDNumber=0)  --判断
begin
insert into T_LeaveSchool(StudentID,ClassID,SchoolID)
values( @StudentID,@ClassID,@StudentID)
end
fetch  next from FeeInsertStudent into @SchoolID,@ClassID,@StudentID
end
close FeeInsertStudent         --关闭游标
deallocate FeeInsertStudent                 --撤销游标
 
 
end