实验要求

运行环境
SQL Server 2022
SQL Server Management Studio Management Studio 19
本实验的全部SQL脚本
create function func1(@coursename char(30))
returns float
as
begin
declare @avg_score float
select @avg_score = avg(score) from sc, c
where c.cname = @coursename and sc.cno = c.cno
return @avg_score
end
go
select dbo.func1('高数') avg_score
go
create proc update_score
@coursename char(30)
as
begin
declare @id char(12), @old int, @c_id char(6), @new int;
declare cur cursor for
select sno, score, sc.cno from sc, c where c.cname = @coursename and sc.cno = c.cno
open cur
fetch next from cur into @id, @old, @c_id;
while @@FETCH_STATUS = 0
begin
if @old > 90 and @old < 95
set @new = @old + 5;
else if @old <= 90 and @old > 90
set @new = @old + 3;
else if @old <= 80 and @old > 70
set @new = @old + 2;
else if @old <=70 and @old > 60
set @new = @old + 1;
update sc set score = @new where sno = @id and sc.cno = @c_id;
fetch next from cur into @id, @old, @c_id;
end
close cur
deallocate cur
end
exec dbo.update_score '高等数学1'