《数据库原理与应用实验报告十五》由会员分享，可在线阅读，更多相关《数据库原理与应用实验报告十五（8页珍藏版）》请在金锄头文库上搜索。
1、计算机工程系实 验 报 告学生信息系别 计算机学院专业计算机科学与技术班级 17 计科 2+2姓名徐浩俊学号2017031601025实验信息课程名称数据库原理与应用实验名称实验 15 存储过程与触发器指导教师文琦批改情况成绩评阅教师文琦实验目标：1. 通过实验掌握存储过程的概念、功能。2. 掌握用户自定义存储过程的创建、修改、删除和执行。3. 掌握触发器的创建、删除、修改及其使用方法。4. 掌握触发器的功能。实验结果：一、自行完成实验 8、实验 9。二、利用 shiyan15.sql 完成下列内容。1. 创建并使用存储过程，注意创建存储过程前一定要先用 IF EXISTS 判断一下是否已创建
2、同名的存储过程，若有先删除在创建。 创建一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名（涉及的表可能有s、sc、t、c、tc），并调用存储过程查询王青山的选修的课程名、成绩以及任课教师姓名，结果如图所示。USE jxskIF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=PRO_SN AND TYPE=P)DROP PROC PRO_SN GOCREATE PROC PRO_SN SN CHAR(8) ASSELECT SN AS 学生, CN AS 选修课程, SCORE AS 分数, TN AS 教师FROM S,SC,T,C,T
3、CWHERE S.SNO=SC.SNO AND SC.CNO=TC.CNO AND TC.TNO=T.TNO AND SC.CNO=C.CNO 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
4、PRO_AGE_MAX_IN GOCREATE 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, AG
5、E_MIN OUTPUTSELECT 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_QSCORE GOCREATE PROC PRO_QSCORE SNAME_IN CHAR(10), CNAME_IN CHAR(10), GRADE_OUT
6、TINYINT OUTPUTASSELECT GRADE_OUT=SCORE FROM S,SC,CWHERE S.SN=SNAME_IN AND S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CN=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
7、 课程, GRADE_OUT 成绩创建一存储过程利用系名检索该系教师的平均年龄和学生人数。调用该存储过程，查询计算机系的教师平均年龄和学生人数，结果如下图所示。USE jxskIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME=PRO_DEPT_COUNT AND TYPE=P)DROP PROC PRO_DEPT_COUNT GOCREATE PROC PRO_DEPT_COUNT DEPT_IN CHAR(10)ASSELECT A.DEPT 系别,平均年龄,学生人数FROM (SELECT DEPT, COUNT(TNO) AS 教师人数,
8、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 E
9、XISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME=PRO_TNAME_CNAME1 AND TYPE=P)DROP PROC PRO_TNAME_CNAME1 GOCREATE 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=MA
10、X(SCORE) FROM T,C,SC,TCWHERE SC.CNO=C.CNO AND C.CNO=TC.CNO AND TC.TNO=T.TNO and T.TN=TNAME_IN AND C.CNO=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_
11、SUM OUTPUT, GRADE_AVG OUTPUT, GRADE_MAX OUTPUTIF(STUDENT_SUM IS NULL)PRINT没有学生选微机原理这门课程，故总课时数，选课人数，平均成绩等统统为0ELSESELECT TNAME_IN 教师姓名, CNAME_IN 学生姓名, STUDENT_SUM 选课总人数, GRADE_AVG 平均成绩, GRADE_MAX 总成绩2. 创建并使用触发器，注意创建触发器前一定要先用 IF EXISTS 判断一下是否已创建同名的触发器，若有先删除在创建。 为表 sc 创建一触发器：当插入或修改一个记录时，确保此记录的成绩在 0100 分
12、之间。（思路：新的数据行就会被插入 inserted 表，通过检查该表判断插入的行是否在 0100 之间，如果在正常插入，否则不做插入，回滚至插入的状态。）l 插入数据行(s5,c1,101)，是否弹出如图所示的错误。请问该行数据是否插入到 sc 表中l 将 s5 的 c1 成绩更新至 101,能否更新成功？USE jxskIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME=SCORE_SC_TRI AND TYPE=TR)DROP TRIGGER SCORE_SC_TRI GOCREATE TRIGGER SCORE_SC_TRI ON SC
13、FOR INSERT, UPDATE ASDECLARE SCORE_IN TINYINTSELECT SCORE_IN=SCORE FROM INSERTED IF SCORE_IN100 BEGINPRINT 成绩超出0-100！请重新输入.ROLLBACK TRANSACTIONEND GOINSERT INTO SC VALUES (s5,c1,101) /*触发器激活，插入失败*/UPDATE SC SET SCORE=101 WHERE SNO=s5 AND CNO=c1激活，更新失败*/*触发器为数据库表 T 创建一触发器：当职称从“讲师”晋升为“副教授”时， 津贴自动增加 50
14、0 元，从“副教授”晋升为“教授”时，岗位津贴自动增加900 元。更新前：更新后：USE jxskIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME=UT AND TYPE=TR)DROP TRIGGER UT GOCREATE TRIGGER UT ON T FOR UPDATE ASDECLARE PROF_OLD CHAR(10) DECLARE PROF_NEW CHAR(10) DECLARE TNO CHAR(2)SELECT PROF_OLD=PROF FROM DELETED SELECT PROF_NEW=PROF FROM INSERTED SELECT 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=TNOEND GOUPDATE T SET PROF=副教授 WHERE TN=刘伟
