实验要求

运行环境

• SQL Server 2022
• SQL Server Management Studio Management Studio 19

本实验的全部SQL脚本

-- 第一题
create function func1(@coursename char(30)) -- 定义函数
returns float
as
begindeclare @avg_score floatselect @avg_score = avg(score) from sc, cwhere c.cname = @coursename and sc.cno = c.cnoreturn @avg_score
endgo -- 消除批处理警告select dbo.func1('高数') avg_score -- 调用函数go -- 消除批处理警告-- 第二题
create proc update_score@coursename char(30)
as
begindeclare @id char(12), @old int, @c_id char(6), @new int;declare cur cursor forselect sno, score, sc.cno from sc, c where c.cname = @coursename and sc.cno = c.cnoopen curfetch next from cur into @id, @old, @c_id;while @@FETCH_STATUS = 0beginif @old > 90 and @old < 95set @new = @old + 5;else if @old <= 90 and @old > 90set @new = @old + 3;else if @old <= 80 and @old > 70 set @new = @old + 2;else if @old <=70 and @old > 60set @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;endclose curdeallocate cur
end exec dbo.update_score '高等数学1'