游标应用qq45055266的博客-

create database db0182945  create table 考生表( 准考证号 varchar(50) not null primary key, 姓名 varchar(50) not null, 性别 char(2) not null, 报考专业 varchar(50) not null, 总分 int default 0 )  create table 考生成绩表( 准考证号 varchar(50) not null foreign key references 考生表(准考证号), 科目名称 varchar(50) not null, 成绩 int check( 成绩 between 0 and 100) ) CREATE NONCLUSTERED INDEX index_id ON 考生表(准考证号)    insert 考生表 values('001','李红','女','经济系',605) insert 考生表 values('002','王辉','男','法学系',585)  insert 考生成绩表 values('001','英语',88) insert 考生成绩表 values('002','英语',89) insert 考生成绩表 values('001','政治',90) insert 考生成绩表 values('002','政治',91) insert 考生成绩表 values('001','数学',92) insert 考生成绩表 values('002','数学',93) insert 考生成绩表 values('001','专业综合',94) insert 考生成绩表 values('002','专业综合',95) /*3题*/ use db0182945 GO create proc p1  as begin update 考生表 set 总分=0 Declare gradeChange_cursor cursor scroll for select 准考证号,成绩 from 考生成绩表 open gradeChange_cursor Declare @id varchar(50) Declare @grade int fetch next from gradeChange_cursor into @id,@grade while(@@FETCH_STATUS=0) begin         update 考生表 set 总分= 总分+@grade where 准考证号 =@id   fetch next from gradeChange_cursor into @id,@grade end close gradeChange_cursor deallocate gradeChange_cursor select*from 考生表 end  EXEC p1  /*4题*/ create table 日志表( 准考证号 varchar(50) not null, 科目名称 varchar(50) not null, 原成绩 int, 新成绩 int ) /*更新触发器*/ use db0182945 GO create trigger tr1 on 考生成绩表 for update,insert,delete as begin Declare @id varchar(40) Declare @course varchar(50) Declare @oldgrade int Declare @newgrade int if(exists(select * from inserted) and exists(select * from deleted)) begin Declare grade_cursor cursor scroll for select * from deleted Declare grade_cursor1 cursor scroll for select 成绩 from inserted open grade_cursor open grade_cursor1 fetch next from grade_cursor into @id,@course,@oldgrade fetch next from grade_cursor1 into @newgrade while(@@FETCH_STATUS=0) begin  update 考生表 set 总分=总分-@oldgrade+@newgrade where 准考证号=@id  insert into 日志表 values(@id,@course,@oldgrade,@newgrade)   fetch next from grade_cursor into @id,@course,@oldgrade  fetch next from grade_cursor1 into @newgrade end close grade_cursor deallocate grade_cursor close grade_cursor1 deallocate grade_cursor1 end end  update 考生成绩表 set 成绩=成绩-5 select*from 日志表  /*删除触发器*/ use db0182945 GO create trigger tr2 on 考生成绩表 for update,insert,delete as begin Declare @id varchar(40) Declare @course varchar(50) Declare @oldgrade int Declare @newgrade int if(not exists(select * from inserted) and exists(select * from deleted)) begin Declare grade_cursor cursor scroll for select * from deleted open grade_cursor fetch next from grade_cursor into @id,@course,@oldgrade while(@@FETCH_STATUS=0) begin  update 考生表 set 总分=总分-@oldgrade where 准考证号=@id  insert into 日志表 values(@id,@course,@oldgrade,0)   fetch next from grade_cursor into @id,@course,@oldgrade end close grade_cursor deallocate grade_cursor end end  delete from 考生成绩表 where 准考证号='001' select*from 日志表  /*插入触发器*/ use db0182945 GO create trigger tr3 on 考生成绩表 for update,insert,delete as begin Declare @id varchar(40) Declare @course varchar(50) Declare @oldgrade int Declare @newgrade int if(exists(select * from inserted) and not exists(select * from deleted)) begin Declare grade_cursor cursor scroll for select * from inserted open grade_cursor fetch next from grade_cursor into @id,@course,@newgrade while(@@FETCH_STATUS=0) begin  update 考生表 set 总分=总分+@newgrade where 准考证号=@id  insert into 日志表 values(@id,@course,0,@newgrade)   fetch next from grade_cursor into @id,@course,@newgrade end close grade_cursor deallocate grade_cursor end end  insert 考生成绩表 values('001','英语',88) insert 考生成绩表 values('001','政治',90) insert 考生成绩表 values('001','数学',92) insert 考生成绩表 values('001','专业综合',94) select*from 日志表