2019版数据库实验报告4

2019版数据库实验报告4

2019版数据库实验报告4—触发器和存储过程部分题的做法

前情提要，我写这个博客不是为了让同样要做这个报告的人照抄，而且这里的答案也不一定正确，如果有大佬发现错误，请千万帮忙在评论区更正，万分感谢！！！！！！！

第一题

编写一个触发器：在添加期末成绩信息时，利用平时成绩、期中成绩和期末成绩来计算成绩总评。如果没有期中成绩，则平时和期末成绩比重为2：8，如果有期中成绩，则平时、期中、期末三者比重为2：2：6。（假设表中有期中成绩列，且该列默认值为-1。）

use mydb
go
alter table student_course
add midmark int /*添加midmark列*/
go
update student_course set midmark = Mark-1
go
create trigger trigger_student_course
on student_course
for insert,update
as
begindeclare @nmark int,@emark int,@mmark intdeclare @sno varchar(10),@ccno char(10)select @nmark=normalmark,@emark=exammark,@mmark=midmark,@sno=sno,@ccno=ccno
from insertedif @mmark > 0beginupdate student_courseset Mark = @nmark*0.2+@mmark*0.2+@emark*0.6where sNO=@sno and ccNO = @ccnoendelsebeginupdate student_courseset Mark=@nmark*0.2+@emark*0.8where sNO=@sno and ccNO=@ccnoend
end

第二题

编写一个触发器：当修改一个学生专业的时候（转专业），则自动将本学期新专业同学全都选修的课程给该同学选修。

#我自己写的版本

use mydb
go
create trigger trigger_choose_class
on student
for update
as
begindeclare @mmno char(6)declare @ssno varchar(10)if UPDATE(mno)select @ssno = deleted.sno , @mmno = inserted.mNO from inserted,deleted /*查询修改后的mno*/begincreate table t(sno varchar(10),ccNO char(10))/*创建临时表*/insert into t /*借助临时表作为跳板*/ select s.sNO,ccNO from student as s,student_course as sc where @mmno = mNO and s.sNO = sc.sNOgroup by s.sNO,ccNOupdate t set sNO = @ssnoinsert into student_course(sNO,ccNO) select sno,ccno from tgroup by sno,ccNOdrop table t /*删除临时表*/end
end

#老师写的版本

--加上本学期的限定。使用after,对应使用选课人次等于学生人数来判断ccno
use mydb
go
create trigger trigger_choose_class
on student
after update
as
begindeclare @new_mno char(6),@old_mno char(6)declare @sno varchar(10)declare @term varchar(12)select @new_mno = mno , @sno = sno from insertedselect @old_mno = mno from deletedif @new_mno <> @old_mnobeginif MONTH(getdate()) > 8set  @term = str(year(getdate()),4,0) + '-' + str(year(getdate())+1,4,0) + '-1'elseset  @term = str(year(getdate())-1,4,0) + '-' + str(year(getdate()),4,0) + '-2'insert into student_course(sNO,ccNO)select @sno,ccnofrom student_course as sc, student as s, course_class as ccwhere sc.sNO = s.sNO and ccno = scno and s.mNO = @new_mno and cc.Term = @termgroup by ccNOhaving COUNT(*) = ((select COUNT(*) from student where mNO = @new_mno)-1)end
end

关于inserted和deleted的提要：
update分为两个过程，先删除（Delete）原来的数据，然后插入（Insert）新的数据，所以，deleted是更新前的数据，inserted是更新后的数据

第三题

创建一个存储过程，利用输入的班级编号（学号前7位）和开课编号ccno，为全班所有同学自动选修该课程。

use mydb
go
create procedure choose_class
@snoclass varchar(7),
@class_count char(10) output,
@newsno varchar(10) output
as
begincreate table t(sno varchar(10),ccNO char(10))/*创建临时表*/insert into t /*借助临时表作为跳板*/select sno,ccNO from student_course where sNO like @snoclass+'%' group by sNO,ccNO update t set ccNO = @class_countinsert into student_course(sNO,ccNO) /*选修class_count*/select sno,ccno from tgroup by sno,ccNOdrop table t /*删除临时表*/
end

关于第三题的提要：
由于我不是很确定怎么让它产生一个虚拟表，所以只好用笨办法先创一个表再删掉它。

第四题

创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，然后输出学生的选课门数、平均分以及所选学分。

use mydb
go
create procedure select_classnumber_avgmark_creidit
@sno char(9),
@class_count int output,
@avgmark float output,
@credit int output
as
beginselect @class_count = COUNT(scno),@avgmark = AVG(sc.Mark) from student_course as scwhere sc.sNO = @snoselect @credit = SUM(credit)from student_course as sc,course_class as ccwhere scNO = ccNO and sc.sNO = @sno
end

第五题

调用4小题中的存储过程，并分别获得输出相关信息。

declare @classcount int 
declare @avgMark float 
declare @credits int 
exec select_classnumber_avgmark_creidit '081220101',@classcount output,@avgMark output,@credits output
select '081220101' as '学号',@classcount as '选课门数',@avgMark as '平均分', @credits as '所选学分'