第3次报告_存储过程和触发器实验

西安邮电大学
（计算机学院）
课内实验报告
实验：存储过程和触发器实验
课程：数据库原理及应用A 班级：计科1405
学号：04141152
学生姓名：高宏伟
任课教师：孟彩霞
一、实验目的
1. 理解存储过程的概念，掌握存储过程的使用方法。

2. 学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解
二、实验内容
1. 在数据库 JWGL 中，完成第四章例 1，例 3~7 中例题的创建存储过程的操作，并使用EXEC 语句调用这些存储过程执行，观察他们的执行结果。

2. 在 Market 数据库中，完成第四章习题 5 中(1)~(4)创建存储过程的操作。

并使用 EXEC 语句调用这些存储过程执行，观察他们的执行结果。

3. 在 Market 数据库上，进一步完成以下操作。

(1) 创建一个存储过程 Goods_Orders1，查看任何指定货物的订单情况，包括订单号、订货客户姓名以及订货数量。

(2) 执行存储过程 Goods_Orders1时，如果没有给出任何参数，则系统会报错，如果希望不给出参数能查出所有货物的订单，则可以用默认参数值来实现。

为此要求创建另一个存储过程 Goods'_Orders2来完成此功能。

(3) 创建一个存储过程 Goods_OrderSum，来获得某个货物的订单总额。

4. 在 JWGL数据库中，完成第四章例 8~13 中例题的创建触发器的操作，然后在相关的表上执行 INSERT、UPDATE、DELETE 语句，观察他们的执行结果。

5. 在 Market 数据库中，完成第四章习题 5 中(5)~(9)创建触发器的操作，然后在相关的表上执行 INSERT、UPDATE、DELETE 语句，观察他们的执行结果。

三、实验环境
MS SQL Server 2016
四、实验前准备
1. 学会存储过程的使用。

2. 学会触发器的使用。

五、实验步骤
存储过程
4.1
CREATE PROCEDURE student_course
AS SELECT Student.sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND o=o AND Sname = '程宇轩';
IF EXISTS(SELECT name FROM sysobjects
WHERE name= 'student_course'AND type='P')
DROP PROCEDURE student_course;
4.2
EXECUTE student_course
4.3
CREATE PROCEDURE student_course1
@StudentName VARCHAR(10)
AS
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND o=o AND Sname=@StudentName;
EXECUTE student_course1 '李书豪'
4.4
CREATE PROCEDURE student_insert
@Sno CHAR(8),@Sname VARCHAR(10),@Sex CHAR(2),@Age TINYINT,@Sdept VARCHAR(20)
INSERT INTO Student VALUES(@Sno,@Sname,@Sex,@Age,@Sdept);
EXECUTE student_insert '950011','李明','男',20,'CS'
4.5
CREATE PROCEDURE student_course2
@StudentName VARCHAR(10)= NULL
AS
IF @StudentName IS NULL
BEGIN
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND o=o
END
ELSE
BEGIN
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND o=o AND Sname=@StudentName END
EXECUTE student_course2
4.6
CREATE PROCEDURE student_count
@CourseName VARCHAR(20),
@StudentSum INT OUTPUT
AS
SELECT @StudentSum = COUNT(*)
FROM Course,SC
WHERE o=o AND Cname=@CourseName;
DECLARE @StudentNum INT;
EXECUTE student_count '数据结构',@StudentNum OUTPUT;
SELECT 'The result is:',@StudentNum
4.7
CREATE PROCEDURE student_query
@xh CHAR(6),@name VARCHAR(8) OUTPUT,@sex CHAR(2) OUTPUT,@age INT OUTPUT AS
Select @name=Sname,@sex=Sex,@age=Age
FROM Student
WHERE Sno=@xh;
DECLARE @StudentNum INT;
EXECUTE student_query '04141152',@name OUTPUT,@sex OUTPUT,@age OUTPUT;
触发器
4.8
CREATE TRIGGER student_change
ON Student AFTER INSERT, UPDATE, DELETE
AS
SELECT * FROM Student;
CREATE TRIGGER studentDelete ON Student
AFTER DELETE
AS
DELETE FROM SC
WHERE Sno IN
(SELECT deleted.Sno FROM deleted);
4.10
CREATE TRIGGER sc_insert ON SC
AFTER INSERT
AS
IF (SELECT COUNT(*) FROM Student,inserted
WHERE Student.Sno=inserted.Sno)=0
BEGIN
PRINT '学号不存在，不能插入该记录'
ROLLBACK TRANSACTION
END;
4.11
CREATE TRIGGER student_update ON Student
AFTER UPDATE
AS
IF UPDATE(Sno)
BEGIN
PRINT '学生的学号不能修改'
ROLLBACK TRANSACTION
END;
4.12
CREATE TRIGGER sc_insert ON SC AFTER INSERT
AS
IF (SELECT COUNT(*)
FROM Student,inserted
WHERE Student.Sno=inserted.Sno)<>@@ROWCOUNT BEGIN
DELETE FROM SC
WHERE Sno NOT IN
(SELECT Sno FROM Student)
END;
六、实验结果
例4.1
例4.3
例4.4
例4.5
例4.6
例4.8
例4.9
例4.10
4.11
4.12
七、评价分析及心得体会
通过这次实验理解了存储过程的概念，掌握了存储过程的使用方法。

学会了触发器的使
用，体会了触发器执行的时机，加深了对触发器功能和作用的理解。