数据库原理与应用实验报告十五 下载本文

计算机工程系

实 验 报 告

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