第1页 / 共8页
第2页 / 共8页
第3页 / 共8页
第4页 / 共8页
第5页 / 共8页
第6页 / 共8页
第7页 / 共8页
第8页 / 共8页
实验六 存储过程与触发器的创建实验bySixdb.pdf
实验六 存储过程与触发器的创建实验
实验六 存储过程与触发器的创建实验
一、 实验目的
1. 熟悉并掌握 SQL SERVER 中存储过程和触发器的概念；
2. 通过 SQL SERVER 管理平台和 Transact-SQL 语句创建存储过程和触发器的
方法和步骤；
3. 掌握存储过程和触发器的执行方法；
4. 掌握存储过程和触发器的删除方法。
二、 实验内容
存储过程
1. 定义存储过程，查询“学生”表中学号=g0940201 的学生的姓名和已选课
程门数，若选课门数在 3 门以上，输出“XX，已经完成了选课”；否则输出“XX，
还需选课”；
2. 定义存储过程，用于查询“学生选课”数据库中所有教师的姓名、性别、
职称和所授课的课程名称；
3. 修改存储过程
（1） 修改存储过程 dbo.proc_t,用于查询“学生选课”数据库中所有
副教授职称的教师的姓名、性别、职称和所授课的课程名称；
（2） 删除该存储过程；
触发器
4. 创建 INSERT 触发器，如果向教师表中插入记录时，检查该记录的院系编
号在表中是否存在。如果不存在，则不允许插入；
测试：insert [dbo].[教师](教师编号,教师姓名,性别,出生年月, 部门 ID 号,职
称,电话,家庭住址,邮编,类别编号)
values('***','***','*' ,'****','****','****',****,'****',***,*)
5. 创建 UPDATEClass 触发器，当对班级信息表主键"班级号"进行修改时，
应对学生表中相应的"班级号"也进行修改；
6. 定义触发器，向学生表插入一条记录时，将所有学生的学号值加 1，并对
其进行测试。
三、 实验步骤
---------------------------------------------------------------------
源文件——SQLQuery6.1.sql
存储过程
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
-- 定义存储过程,查询 STUDENT 表中学号=g0940201 的学生的姓名和已选课程门数,
-- 若选课门数在 3 门以上,输出"XX,已经完成了选课";否则输出"XX,还需选课"
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='proc_stu')
DROP PROCEDURE proc_stu
GO
CREATE PROCEDURE proc_stu @stuID VARCHAR(20)
AS
BEGIN
DECLARE @courseNUM INT
DECLARE @stuNAME VARCHAR(20)
SELECT @courseNUM=COUNT(课程 ID 号) FROM STUDENT,STUDENT_COURSE
WHERE STUDENT. 学 生 ID 号 =STUDENT_COURSE. 学 生 ID 号 AND STUDENT. 学 生 ID 号
=@stuID
GROUP BY STUDENT.学生 ID 号
SELECT @stuNAME=学生姓名 FROM STUDENT
WHERE 学生 ID 号=@stuID
IF(@courseNUM>3)
PRINT @stuNAME+'，已经完成了选课';
ELSE
PRINT @stuNAME+'，还需选课';
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25. GO
END
---------------------------------------------------------------------
源文件——SQLQuery6.2.sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
-- 定义存储过程，用于查询“学生选课”数据库中所
-- 有教师的姓名、性别、职称和所授课的课程名称
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='proc_t')
DROP PROCEDURE proc_t
GO
CREATE PROCEDURE proc_t
AS
BEGIN
SELECT DISTINCT T.教师姓名,T.性别,T.职称或职业,C.课程名称
FROM TEACHER T,TEACHER_COURSE_CLASS TCC,COURSE C
WHERE T.教师 ID 号=TCC.教师编号 AND C.课程号=TCC.课程号
END
14. GO
---------------------------------------------------------------------
源文件——SQLQuery6.3.1.sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
-- 修改存储过程 dbo.proc_t,用于查询“学生选课”数据库中所有
-- 副教授职称的教师的姓名、性别、职称和所授课的课程名称
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='proc_t')
DROP PROCEDURE proc_t
GO
CREATE PROCEDURE proc_t
AS
BEGIN
SELECT DISTINCT T.教师姓名,T.性别,T.职称或职业,C.课程名称
FROM TEACHER T,TEACHER_COURSE_CLASS TCC,COURSE C
WHERE T.职称或职业='副教授' AND T.教师 ID 号=TCC.教师编号 AND C.课程号=TCC.课
程号
END
13.
14. GO
15. -- 测试
16. EXEC proc_t
17. GO
---------------------------------------------------------------------
源文件——SQLQuery6.3.2.sql
1.
2.
3.
4.
5.
-- 删除该存储过程
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='proc_t')
DROP PROCEDURE proc_t
GO
---------------------------------------------------------------------
源文件——SQLQuery6.4.sql
触发器
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
-- 创建 INSERT 触发器，如果向教师表中插入记录时，检查该记
-- 录的院系编号在表中是否存在。如果不存在，则不允许插入
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='trig_t')
DROP TRIGGER trig_t
GO
CREATE TRIGGER trig_t ON TEACHER AFTER INSERT
AS
BEGIN
DECLARE @ERROR VARCHAR(20)
SET @ERROR=(SELECT 部门 ID 号 FROM inserted)
IF @ERROR NOT IN(SELECT 部门 ID 号 FROM DEPARTMENT)
13.
14.
15.
16.
17.
18.
19.
BEGIN
PRINT '禁止插入含有不合法院系编号的教师,message:'+@ERROR
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
END
---------------------------------------------------------------------
源文件——SQLQuery6.5.sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
-- 创建 UPDATEClass 触发器,当对班级信息表主键"班级号"
-- 进行修改时,应对学生表中相应的"班级号"也进行修改
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='UPDATEClass')
DROP TRIGGER UPDATEClass
GO
CREATE TRIGGER UPDATEClass ON CLASS FOR UPDATE
AS
IF UPDATE(班级号)
UPDATE STUDENT SET 班级 ID 号=(SELECT 班级号 FROM inserted) WHERE 班级 ID 号
=(SELECT 班级号 FROM deleted)
---------------------------------------------------------------------
源文件——SQLQuery6.6.sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
-- 定义触发器,向学生表插入一条记录时,
-- 将所有学生的学号值加 1
USE 学生选课
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='trig_s')
DROP TRIGGER trig_s
GO
CREATE TRIGGER trig_s ON STUDENT AFTER INSERT
AS
BEGIN
UPDATE STUDENT SET 学生 ID 号=SUBSTRING(学生 ID 号,1,5)+CAST(CAST(SUBSTRING(学
生 ID 号,6,3) AS int)+1 AS varchar)
END
四、 实验结果
1. a)输出结果如下：
b)存储过程在目录中显示如下：
2. a)输出结果如下：
b)存储过程在目录中显示如下：
3. a)输出结果如下：
b)存储过程在目录中显示如下：
c)删除存储过程后显示如下：
4. a)插入部门 ID 号不合法的教师
b)触发器在目录中显示如下：
5. 触发器在目录中显示如下：
6. a)插入前
b)插入后
五、 实验小结
1、 深刻掌握课本内容
2、 愈加熟悉 SQL Server 的使用
3、 Transact 语句了解变得深入
4、 激发了学习兴趣，锻炼了动手能力
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
