计算机工程系

实 验 报 告

学生信息 系别 计算机学院 班级 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之间，如果在正常插入，否则不做插入，回滚至插入