求大神注释下面的一段代码 以及说明其功能alter proc prc_getallscoreasbeginupdate studentinfoset lastscore =0declare @sno char(10)declare @score int declare sno_cursor CurSor for select sno from studentinfoopen sno_cursor fetch next f
来源:学生作业帮助网 编辑:作业帮 时间:2024/08/28 08:34:52
求大神注释下面的一段代码 以及说明其功能alter proc prc_getallscoreasbeginupdate studentinfoset lastscore =0declare @sno char(10)declare @score int declare sno_cursor CurSor for select sno from studentinfoopen sno_cursor fetch next f
求大神注释下面的一段代码 以及说明其功能
alter proc prc_getallscore
as
begin
update studentinfo
set lastscore =0
declare @sno char(10)
declare @score int
declare sno_cursor CurSor for select sno from studentinfo
open sno_cursor
fetch next from sno_cursor into @sno
WHILE @@FETCH_STATUS = 0
begin
select @score=sum(score) from StudentManifest where sno=@sno
if @score is null
set @score =0
update studentinfo
set lastscore =@score+orignscore
where sno=@sno
fetch next from sno_cursor into @sno
end
close sno_cursor
deallocate sno_cursor
end
go
prc_getallscore
select *from StudentInfo
求大神注释下面的一段代码 以及说明其功能alter proc prc_getallscoreasbeginupdate studentinfoset lastscore =0declare @sno char(10)declare @score int declare sno_cursor CurSor for select sno from studentinfoopen sno_cursor fetch next f
ALTER PROC prc_getallscore
AS
BEGIN
UPDATE studentinfo SET lastscore =0 --首先将studentinfo表中所有记录的lastscore字段值置为0
DECLARE @sno CHAR(10) --定义变量@sno记录学生学号
DECLARE @score INT --定义变量@score记录学生成绩
DECLARE sno_cursor CURSOR FOR SELECT sno FROM studentinfo --定义游标sno_cursor,用于从studentinfo 表中获取学生学号
OPEN sno_cursor --打开游标sno_cursor
FETCH NEXT FROM sno_cursor INTO @sno --从游标中取一条记录填充到变量@sno
WHILE @@FETCH_STATUS = 0 --循环条件(游标未指向末尾)
BEGIN
SELECT @score=SUM(score) FROM StudentManifest WHERE sno=@sno --从StudentManifest表中获取学号为@sno的学生的总成绩
IF @score IS NULL --如果@score为NULL就置为0
SET @score =0
UPDATE studentinfo SET lastscore =@score+orignscore WHERE sno=@sno --更新studentinfo表中的lastscore字段值
FETCH NEXT FROM sno_cursor INTO @sno --从游标中取下一条记录填充到变量@sno
END
CLOSE sno_cursor --关闭游标
DEALLOCATE sno_cursor --释放游标
END
GO
整个存储过程涉及两张表studentinfo学生表,StudentManifest学生成绩表,存储过程的功能是从学生表studentinfo中取每个学生的学号,根据学号到成绩表StudentManifest中统计得到该学生的总成绩,并更新到学生表studentinfo的lastscore字段中