数据库原理与应用实验报告十五

计算机工程系实验报告学生信息系别计算机学院专业计算机科学与技术班级17计科2+2 姓名徐浩俊学号*************实验信息课程名称数据库原理与应用实验名称实验15 存储过程与触发器指导教师文琦批改情况成绩评阅教师文琦实验目标：1．通过实验掌握存储过程的概念、功能。

2．掌握用户自定义存储过程的创建、修改、删除和执行。

3．掌握触发器的创建、删除、修改及其使用方法。

4．掌握触发器的功能。

实验结果：一、自行完成实验8、实验9。

二、利用shiyan15.sql完成下列内容。

1．创建并使用存储过程，注意创建存储过程前一定要先用IF EXISTS判断一下是否已创建同名的存储过程，若有先删除在创建。

①创建一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名（涉及的表可能有s、sc、t、c、tc），并调用存储过程查询王青山的选修的课程名、成绩以及任课教师姓名，结果如图所示。

USE jxskIF EXISTS(SELECT*FROM SYSOBJECTS WHERE NAME='PRO_SN'AND TYPE='P')DROP PROC PRO_SNGOCREATE PROC PRO_SN@SN CHAR(8)ASSELECT SN AS学生,CN AS选修课程,SCORE AS分数,TN AS教师FROM S,SC,T,C,TCWHERE S.SNO=SC.SNO AND O=O AND TC.TNO=T.TNO AND O=O AND SN=@SNGODECLARE@SNAME_IN CHAR(8)SET@SNAME_IN='赵亦'EXEC PRO_SN@SNAME_IN②创建一存储过程利用系名查询某系的学生的最大年龄和最小年龄。

调用存储过程查询信息系的学生最大年龄和最小年龄。

（涉及的表可能有s）USE jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_AGE_MAX_IN' AND TYPE='P')DROP PROC PRO_AGE_MAX_INGOCREATE PROC PRO_AGE_MAX_IN@DEPT_IN CHAR(10),@AGE_MAX TINYINT OUTPUT,@AGE_MIN TINYINT OUTPUTASSELECT@AGE_MAX=MAX(AGE),@AGE_MIN=MIN(AGE)FROM SWHERE S.DEPT=@DEPT_INGODECLARE@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 jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_QSCORE'AND TYPE='P')DROP PROC PRO_QSCOREGOCREATE PROC PRO_QSCORE@SNAME_IN CHAR(10),@CNAME_IN CHAR(10),@GRADE_OUT TINYINT OUTPUTASSELECT@GRADE_OUT=SCOREFROM S,SC,CWHERE S.SN=@SNAME_IN AND S.SNO=SC.SNO AND O=O AND =@CNAME_INGODECLARE@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 jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='PRO_DEPT_COUNT' AND TYPE='P')DROP PROC PRO_DEPT_COUNTGOCREATE PROC PRO_DEPT_COUNT@DEPT_IN CHAR(10)ASSELECT 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) BWHERE A.DEPT=B.DEPT AND A.DEPT=@DEPT_INGODECLARE@DEPT_IN CHAR(10)SET@DEPT_IN='计算机'EXEC PRO_DEPT_COUNT@DEPT_IN⑤利用教师姓名和课程名检索该教师该课的课程名、选课人数、平均成绩、最高成绩、并查询教师“张雪”的“微机原理”课程的情况记录及教师“张朋”的“数据库”课程，结果如图所示。

USE jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERENAME='PRO_TNAME_CNAME1'AND TYPE='P')DROP PROC PRO_TNAME_CNAME1GOCREATE PROC PRO_DEPT_COUNT1@TNAME_IN CHAR(10),@CNAME_IN CHAR(10),@STUDENT_SUM INT OUTPUT,@GRADE_AVG INT OUTPUT,@GRADE_MAX INT OUTPUTASSELECT@STUDENT_SUM=COUNT(SNO),@GRADE_AVG=AVG(SCORE),@GRADE_MAX=MAX(SCORE)FROM T,C,SC,TCWHERE O=O AND O=O AND TC.TNO=T.TNO andT.TN=@TNAME_IN AND O=@CNAME_INGROUP BY TN,CNGODECLARE@TNAME_IN CHAR(10),@CNAME_IN CHAR(10),@STUDENT_SUM INT, @GRADE_AVG INT,@GRADE_MAX INTSET@TNAME_IN='张雪'SET@CNAME_IN='微机原理'EXEC PRO_DEPT_COUNT1@TNAME_IN,@CNAME_IN,@STUDENT_SUM OUTPUT, @GRADE_AVG OUTPUT,@GRADE_MAX OUTPUTIF(@STUDENT_SUM IS NULL)PRINT'没有学生选微机原理这门课程，故总课时数，选课人数，平均成绩等统统为0'ELSESELECT@TNAME_IN教师姓名,@CNAME_IN学生姓名,@STUDENT_SUM选课总人数,@GRADE_AVG平均成绩,@GRADE_MAX总成绩2．创建并使用触发器，注意创建触发器前一定要先用IF EXISTS判断一下是否已创建同名的触发器，若有先删除在创建。

①为表sc创建一触发器：当插入或修改一个记录时，确保此记录的成绩在0~100分之间。

（思路：新的数据行就会被插入inserted表，通过检查该表判断插入的行是否在0~100之间，如果在正常插入，否则不做插入，回滚至插入的状态。

）●插入数据行('s5','c1','101')，是否弹出如图所示的错误。

请问该行数据是否插入到sc表中●将s5的c1成绩更新至101,能否更新成功？USE jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='SCORE_SC_TRI' AND TYPE='TR')DROP TRIGGER SCORE_SC_TRIGOCREATE TRIGGER SCORE_SC_TRION SC FOR INSERT,UPDATEASDECLARE@SCORE_IN TINYINTSELECT@SCORE_IN=SCORE FROM INSERTEDIF@SCORE_IN<0 OR@SCORE_IN>100BEGINPRINT'成绩超出0-100！请重新输入.'ROLLBACK TRANSACTIONENDGOINSERT INTO SC VALUES ('s5','c1','101')/*触发器激活，插入失败*/UPDATE SC SET SCORE=101 WHERE SNO='s5'AND CNO='c1'/*触发器激活，更新失败*/②为数据库表T创建一触发器：当职称从“讲师”晋升为“副教授”时，津贴自动增加500元，从“副教授”晋升为“教授”时，岗位津贴自动增加900元。

更新前：更新后：USE jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='UT'AND TYPE='TR')DROP TRIGGER UTGOCREATE TRIGGER UTON T FOR UPDATEASDECLARE@PROF_OLD CHAR(10)DECLARE@PROF_NEW CHAR(10)DECLARE@TNO CHAR(2)SELECT@PROF_OLD=PROF FROM DELETEDSELECT@PROF_NEW=PROF FROM INSERTEDSELECT@TNO=TNO FROM DELETEDIF@PROF_OLD='讲师'AND@PROF_NEW='副教授'BEGINUPDATE T SET COMM=COMM+500 WHERE TNO=@TNOENDIF@PROF_OLD='副教授'AND@PROF_NEW='教授' BEGINUPDATE T SET COMM=COMM+900 WHERE TNO=@TNO ENDGOUPDATE T SET PROF='副教授'WHERE TN='刘伟'。