简单使用游标插入数据

简单使用游标插入数据

 

--创建数据库

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