2023年6月27日发(作者：)

实验六 视图、存储过程、触发器 ( By Zhu Jing )

1． 定义视图并在视图上完成数据的查询功能。

1） 建立“查询有选课记录的学生学号、课程号、课程名称、成绩信息”操作的视图。

CREATE VIEW S_score

AS

SELECT ,,

,

FROM sc join sections on

= Join

course on =

2） 在上述视图的基础上完成查询：查询所有学生都及格的课程名称。

SELECT cname AS '所有学生都及格的课名称'

FROM S_score

WHERE cname NOT IN (SELECT cname

FROM S_score WHERE score<60)

或

SELECT cname AS 所有学生都及格的课程名称

FROM S_score

GROUP BY cname Having min(score)>=60

1实验六 视图、存储过程、触发器 ( By Zhu Jing )

2． 存储过程的建立与执行

1） 建立存储过程，其功能为显示所有学生的基本信息

CREATE PROCEDURE 学生信息

AS

SELECT * FROM student

==========

exec 学生信息

2） 建立存储过程，其功能是查询给定出生年份信息（已知出生日期的前4位的信息）的学生信息

CREATE PROC student_proc

@_year INT

AS

2实验六 视图、存储过程、触发器 ( By Zhu Jing )

SELECT *

FROM student

WHERE YEAR(birthday)=@_year

===========

exec student_proc 1988

3． 建立一组触发器，并设计一组必要的数据操作，验证触发器的功能

1） 自定义一个触发器，完成选课表sc属性snum的参照完整性控制。

CREATE TRIGGER SC_insert ON sc

FOR INSERT,UPDATE AS

BEGIN

IF((SELECT snum FROM inserted) NOT IN

(SELECT snum FROM student))

BEGIN

PRINT '没有该学生，撤销操作'

ROLLBACK

END

3实验六 视图、存储过程、触发器 ( By Zhu Jing )

IF((SELECT secnum FROM inserted) NOT IN

(SELECT secnum FROM sections))

BEGIN

PRINT '没有该班级，撤销操作'

ROLLBACK

END

END

========

测试1:插入一个不存在的学生的选课记录

insert into sc

values('s009','11601',78)

测试2:插入一个不存在的班级的选课记录

insert into sc

values('s008','11622',78)

4实验六 视图、存储过程、触发器 ( By Zhu Jing )

2） 自定义一个触发器，完成教课表sections的数据完整性控制。

CREATE TRIGGER sections_delete

ON sections FOR DELETE AS

BEGIN

If exists(select * from sc where

secnum in(select secnum from deleted))

begin

Raiserror('该班级有学生选课，

同时删除学生的选课记录！',16,1)

DELETE FROM sc WHERE secnum in

(select secnum from deleted)

end

else

Raiserror('该班级无学生选课，

删除该班级！',16,1)

END

测试1:删除一个有学生选课的班级.

delete from sections where secnum='11601'

5实验六 视图、存储过程、触发器 ( By Zhu Jing )

测试2:删除一个无学生选课的班级.

首先插入一个新开班级14001:

insert into sections

values('14001','c120','p005')

然后删除14001班级：

delete from sections where secnum='14001'

6