数据库系统原理实验3

数据库系统原理实验报告

姓名成长锦

班级统计1702

学号 17271119

日期：2020/06/01

自我评价

教师评语

成绩

一、实验目的

本实验通过对DBMS 软件的操作，使学生掌握数据库触发器的设计和使用方法，以及存储过程的创建和执行方法。

二、实验环境

SQL Server，主要使用SQL Server Management Studio.

采用操作系统（windows）用户登录方式登录SQL Server.

三、实验内容及要求

请按照实验内容及实验要求，认真完成各项实验，并完成实验报告（包括涉及到的SQL 脚本、实验结果（截图）、实验中出现的问题、解决方法说明以及实验体会）。

实验内容一、数据库完整性之触发器实验

[实验目的]

理解不同类型触发器的作用和执行原理，验证触发器的有效性。

[实验内容及要求]

1）在Student 表上定义一个UPDATE 触发器，将对Student 表的修改记录到表StudentUpdateLog 中

先创建表：

●触发器：

ALTER TRIGGER [dbo].[Student_U] ON [dbo].[Student]

AFTER UPDATE

AS

INSERT INTO StudentUpdateLog(sno,sname,ssex,sage,sdept)

select*from deleted

●测试：

update Student

set sname='李大勇'

where sname='李小勇'

update Student

set sname='李大小勇'

where sname='李大勇'

查看studentupdatelog表：

2）在Student 表上定义一个DELETE 触发器，当删除一个学生记录时，自动删除SC 表上这个学生的所有选课记录

●定义触发器：

CREATE TRIGGER Student_D on Student

AFTER delete

AS

DECLARE @oldsno char(10)

SELECT @oldsno=sno from deleted

DELETE FROM SC

WHERE sno=@oldsno

●测试：

--查看测试删除的学号

select*from Student

where sname='李勇'

--删除

delete

from Student

where sname='李勇'

3）在Course 表上定义一个UPDATE 触发器，当更新一门课程的课程号时，自动修改SC 表上这门课程的课程号

●触发器：

create trigger Courese_U on Course

after update

as

declare @newcno char(5)

declare @oldcno char(5)

select @newcno=cno from inserted

select @oldcno=cno from deleted

update SC

set cno=@newcno

where cno=@oldcno

●测试：将课程cno=1修改为10，并查看SC表

4）在SC 表上定义一个DELETE 触发器，当删除一条选课记录时，若这条记录是这门课程的唯一被选记录，则同时删除Course 表中的这门课程

触发器：

create trigger SC_D on SC

after delete

as

declare @oldcno char

declare @numcno int

select @oldcno=cno from deleted

select @numcno=COUNT(*)from SC

where cno=@oldcno

if(@numcno=0)

delete

from Course

where cno=@oldcno

●测试：cno=7在SC表中只有一行，查看Course表发现cno=7的课程已经删除

5）以上触发器均为AFTER 类型的触发器，请在已有数据库上自己设计并定义一个BEFORE 类型的触发器

●触发器：当插入SC表成绩小于60时，默认成绩为60

ALTER trigger [dbo].[SC_grade] on [dbo].[SC]

for insert

as

declare @oldgrade int

declare @oldcno char(5)

select @oldgrade=grade from inserted select @oldcno=cno from inserted

if(@oldgrade<60 )

update SC

set grade=60

where grade=@oldgrade and cno=@oldcno

测试：

实验内容二、给定要求的存储过程及DBMS 中执行

[实验目的]

学会在SQL Server 中创建存储过程，并通过脚本在SQL Server 中调用执行该存储过程。[实验内容及要求]

1. 对学生-课程数据库编写两个存储过程，完成下述功能：

1）统计任意一门课的平均成绩

执行存储过程：

输出’C3’ 课程的平均成绩

存储过程：

create procedure avggrade

@newcno char(5),

@score float output

AS

declare @search_cno char(5)

set @search_cno=0

select @search_cno=cno from SC

if(@search_cno=0)

begin

set @score=0

print'课程不存在'

end

else

begin

select @score=AVG(grade)from SC

where cno=@search_cno

end

declare @score float

exec avggrade4,@score output

print @score

declare @score float

exec avggrade100,@score output

print @score

2）把某位同学选修课程1 的成绩和课程2 的成绩互换（假定成绩都不为空）

执行存储过程：

把同学‘S1’ 的’C2’ 课程和‘C4’ 课程成绩互换

把同学’S7’ 的’C2’ 课程和‘C4’ 课程成绩互换

●执行存储过程前：SC表，红色框分别是刘晨、王敏准备交换的课程对应成绩。

ALTER procedure [dbo].[change]

@cno1 char(5),

@cno2 char(5),

@sname char(20)

AS

declare @search_sno char(10)

declare @grade1 int,@grade2 int,@num int

select @search_sno=sno from Student where sname=@sname

if(@search_sno is null)

begin

print'该学生不存在'

end

else

begin

select @num=COUNT(*)from SC

where sno=@search_sno and(cno=@cno1 or cno=@cno2)

if(@num<>2)

begin

print'该学生某课程不存在，无法交换'

end

else

begin

select @grade1=grade from SC where sno=@search_sno and cno=@cno1;

select @grade2=grade from SC where sno=@search_sno and cno=@cno2;

update SC

set grade=@grade2

where sno=@search_sno and cno=@cno1;

update SC

set grade=@grade1

where sno=@search_sno and cno=@cno2;

end

end

●测试1：交换刘晨课程2和3成绩

交换后SC 表：

●测试2：交换王敏同学的课程5和8成绩

交换后SC 表：

●测试3：特殊情况一，学生姓名查找为空

测试4：特殊情况二：学生对应课程不存在