数据库实验七（SQL Server & SSMS）

实验要求

运行环境

• 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'