计算机工程系
实 验 报 告
学生信息 系别 计算机学院 班级 17计科2+2 专业 姓名 计算机科学与技术 徐浩俊 学号 2017031601025 实验信息 课程名称 实验名称 指导教师 数据库原理与应用 实验15 存储过程与触发器 文琦 批改情况 成绩 评阅教师 文琦 实验目标：
1．通过实验掌握存储过程的概念、功能。
2．掌握用户自定义存储过程的创建、修改、删除和执行。 3．掌握触发器的创建、删除、修改及其使用方法。 4．掌握触发器的功能。 实验结果：
一、自行完成实验8、实验9。 二、利用shiyan15.sql完成下列内容。
1．创建并使用存储过程，注意创建存储过程前一定要先用IF EXISTS判断一下是否已创建同名的存储过程，若有先删除在创建。
① 创建一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名（涉及的表可能有s、sc、t、c、tc），并调用存储过程查询王青山的选修的课程名、成绩以及任课教师姓名，结果如图所示。
USE jxsk
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PRO_SN' AND TYPE='P')
DROP PROC PRO_SN GO
CREATE PROC PRO_SN @SN CHAR(8) AS
SELECT SN AS 学生, CN AS 选修课程, SCORE AS 分数, TN AS 教师 FROM S,SC,T,C,TC
WHERE S.SNO=SC.SNO AND SC.CNO=TC.CNO AND TC.TNO=T.TNO AND SC.CNO=C.CNO AND SN=@SN GO
DECLARE @SNAME_IN CHAR(8) SET @SNAME_IN='赵亦' EXEC PRO_SN @SNAME_IN
② 创建一存储过程利用系名查询某系的学生的最大年龄和最小年龄。调用
存储过程查询信息系的学生最大年龄和最小年龄。（涉及的表可能有s）
USE jxsk
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_AGE_MAX_IN' AND TYPE='P')
DROP PROC PRO_AGE_MAX_IN GO
CREATE PROC PRO_AGE_MAX_IN @DEPT_IN CHAR(10),
@AGE_MAX TINYINT OUTPUT, @AGE_MIN TINYINT OUTPUT AS
SELECT @AGE_MAX=MAX(AGE), @AGE_MIN=MIN(AGE) FROM S
WHERE S.DEPT=@DEPT_IN GO
DECLARE @DEPT_IN CHAR(10), @AGE_MAX TINYINT, @AGE_MIN TINYINT SET @DEPT_IN='信息'
EXEC PRO_AGE_MAX_IN @DEPT_IN, @AGE_MAX OUTPUT, @AGE_MIN OUTPUT SELECT @DEPT_IN 系别, @AGE_MAX 最大年龄, @AGE_MIN 最小年龄
③ 创建一存储过程利用学生姓名和课程名检索该生该课程的成绩，涉及的表可能有s,sc,c。调用该存储过程查询李思程序设计的成绩，结果如下图所示。
USE jxsk
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_QSCORE' AND TYPE='P')
DROP PROC PRO_QSCORE GO
CREATE PROC PRO_QSCORE @SNAME_IN CHAR(10), @CNAME_IN CHAR(10),
@GRADE_OUT TINYINT OUTPUT AS
SELECT @GRADE_OUT=SCORE FROM S,SC,C
WHERE S.SN=@SNAME_IN AND S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CN=@CNAME_IN GO
DECLARE @SNAME_IN CHAR(10), @CNAME_IN CHAR(10), @GRADE_OUT TINYINT SET @SNAME_IN='李思' SET @CNAME_IN='程序设计'
EXEC PRO_QSCORE @SNAME_IN, @CNAME_IN, @GRADE_OUT OUTPUT SELECT @SNAME_IN 姓名, @CNAME_IN 课程, @GRADE_OUT 成绩
④ 创建一存储过程利用系名检索该系教师的平均年龄和学生人数。调用该存储过程，查询计算机系的教师平均年龄和学生人数，结果如下图所示。
USE jxsk
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_DEPT_COUNT' AND TYPE='P')
DROP PROC PRO_DEPT_COUNT GO
CREATE PROC PRO_DEPT_COUNT @DEPT_IN CHAR(10) AS
SELECT A.DEPT 系别,平均年龄,学生人数
FROM (SELECT DEPT, COUNT(TNO) AS 教师人数, AVG(AGE) AS 平均年龄 FROM T GROUP BY DEPT) A,
(SELECT DEPT, COUNT(SNO) AS 学生人数 FROM S GROUP BY DEPT) B
WHERE A.DEPT=B.DEPT AND A.DEPT=@DEPT_IN GO
DECLARE @DEPT_IN CHAR(10) SET @DEPT_IN='计算机'
EXEC PRO_DEPT_COUNT @DEPT_IN
⑤ 利用教师姓名和课程名检索该教师该课的课程名、选课人数、平均成绩、最高成绩、并查询教师“张雪”的“微机原理”课程的情况记录及教师“张朋”的“数据库”课程，结果如图所示。
USE jxsk
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_TNAME_CNAME1' AND TYPE='P') DROP PROC PRO_TNAME_CNAME1 GO
CREATE PROC PRO_DEPT_COUNT1 @TNAME_IN CHAR(10), @CNAME_IN CHAR(10),
@STUDENT_SUM INT OUTPUT, @GRADE_AVG INT OUTPUT, @GRADE_MAX INT OUTPUT AS
SELECT @STUDENT_SUM=COUNT(SNO), @GRADE_AVG=AVG(SCORE), @GRADE_MAX=MAX(SCORE) FROM T,C,SC,TC
WHERE SC.CNO=C.CNO AND C.CNO=TC.CNO AND TC.TNO=T.TNO and T.TN=@TNAME_IN AND C.CNO=@CNAME_IN GROUP BY TN,CN GO
DECLARE @TNAME_IN CHAR(10), @CNAME_IN CHAR(10), @STUDENT_SUM INT, @GRADE_AVG INT, @GRADE_MAX INT SET @TNAME_IN='张雪' SET @CNAME_IN='微机原理'
EXEC PRO_DEPT_COUNT1 @TNAME_IN, @CNAME_IN, @STUDENT_SUM OUTPUT, @GRADE_AVG OUTPUT, @GRADE_MAX OUTPUT
IF(@STUDENT_SUM IS NULL)
PRINT'没有学生选微机原理这门课程，故总课时数，选课人数，平均成绩等统统为0' ELSE
SELECT @TNAME_IN 教师姓名, @CNAME_IN 学生姓名, @STUDENT_SUM 选课总人数, @GRADE_AVG 平均成绩, @GRADE_MAX 总成绩
2．创建并使用触发器，注意创建触发器前一定要先用IF EXISTS判断一下是否已创建同名的触发器，若有先删除在创建。
① 为表sc创建一触发器：当插入或修改一个记录时，确保此记录的成绩在0~100分之间。（思路：新的数据行就会被插入inserted表，通过检查该表判断插入的行是否在0~100之间，如果在正常插入，否则不做插入，回滚至插入