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

1、计算机工程系实验报告学生信息系别计算机学院专业计算机科学与技术班级17计科2+2姓名徐浩俊 学号 2021031601025实验信息课程名称数据库原理与应用实验名称实验15存储过程与触发器指导教师义埼批改情况成绩评阅教师文琦实验目标：1 .通过实验掌握存储过程的概念、功能.2 .掌握用户自定义存储过程的创立、修改、删除和执行.3 .掌握触发器的创立、删除、修改及其使用方法.4 .掌握触发器的功能.实验结果：5 、自行完成实验8、实验9.6 、利用shiyan15.sql完成以下内容.1.创立并使用存储过程,注意创立存储过程前一定要先用IF EXISTS判断 一下是否已创立同名的存储过程,假设有

2、先删除在创立.创立一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课 教师姓名涉及的表可能有s、sc、t、c、tc,并调用存储过程查询王青山的 选修的课程名、成绩以及任课教师姓名,结果如下图.学生选修课程分额浏币1海亦程序设计90李力2赵亦程序设计90划伟3赵亦微机原理85张雪教师姓名课程名 选课总人数平均成绩 最高成绩UialKIUIBIUIBIKIUIBlUiallU1 冰雪 i微机原理48289USE jxskIF EXISTS( SELECT* FROMSYSOBJECTSWHERENAM='PRO_SN' ANDTYPE='P')DROPPRO

3、CPRO_SNGOCREATEPROCPRO_SNSNCHAR8)ASSELECT SN AS学生,CN AS选修课程,SCOREAS分数,TN AS教师FROMS, SC T, C, TCWHERES. SNO=SC. SNOAND SC. CNGTC. CNOAND TC. TNO=T. TNO ANDSC CNO=C. CNOAND SN=SNGODECLARESNAME_INCHAR8)SET SNAME_IN 赵亦EXEC PRO_SNSNAME_IN创立一存储过程利用系名查询某系的学生的最大年龄和最小年龄.调用存储过程查询信息系的学生最大年龄和最小年龄.涉及的表可能有s系别最大年龄

4、最力年龄JBBI BIBai21| 信息 | 2口 16'h15, n,r*i rm r ie fUSE jxskIF EXISTS( SELECTNAMEFROMSYSOBJECTSWHEREJAM='PRO_AGE_MAX_IN' ANDTYPE='P')DROPPROC PRO_AGE_MAX_INGOCREATEPROCPRO_AGE_MAX_INDEPT_INCHAR10),AGE_MAXINYINT OUTPUTAGE_MINTINYINT OUTPUTASSELECT AGE_MAMAX AGE, AGE_MINMIN( AGEFROMSW

5、HERES. DEPT=DEPT_INGODECLAREDEPT_INCHAR 10), AGE_MAXINYINT , AGE_MINTINYINTSET DEPT_II='信息EXEC PRO_AGE_MAX_INDEPT_IN AGE_MAX)UTPUT AGE_MINOUTPUTSELECTDEPT_IN系另1J, AGE_MAXi大年龄,AGE_MINft小年龄创立一存储过程利用学生姓名和课程名检索该生该课程的成绩,涉及的表可能有s,sc,c .调用该存储过程查询李思程序设计的成绩,结果如以下图所示.姓名】手恿7程序设计93USE jxskIF EXISTS( SELECTN

6、AMEFROMSYSOBJECTSWHEREJAM='PRO_QSCORE'ANDTYPE='P')DROPPROCPRO_QSCOREGOCREATEPROCPRO_QSCORESNAME_INCHAR10),CNAME_INDHAR10),GRADE_OUT1NYINT OUTPUTASSELECT GRADE_OUTCOREFROMS, SC CWHERES. SN=SNAME_INAND S SNG=SG SNOAND SC CN3C. CNOANDC. CN=CNAME_INGODECLARESNAME_IICHAR 10), CNAME_ICHAR1

7、0), GRADE_OUTTNYINTSET SNAME_IN 李思SET CNAME_IN 程序设计'EXEC PRO_QSCORSNAME_I,N CNAME_,N GRADE_OUOUTPUT SELECT SNAME_IN生名,CNAME_IN1程,GRADE_OU成绩 创立一存储过程利用系名检索该系教师的平均年龄和学生人数.调用该存储过程,查询计算机系的教师平均年龄和学生人数,结果如以下图所示.系别平均年龄学生人数1 讦苴机：393USE jxskIF EXISTS( SELECTNAMEFROMSYSOBJECTSWHERENAM='PRO_DEPT_COUNT&#

8、39; ANDTYPE='P')DROPPROC PRO_DEPT_COUNTGOCREATEPROCPRO_DEPT_COUNTDEPT_INCHAR10)ASSELECT A. DEPT系另1,平土匀年龄,学生人数FROM( SELECT DEPT COUNTTNO AS 教师人数,AVG( AGE AS 平土匀年 龄 FROMT GROUPBY DEPT) A,(SELECT DEPT, COUNQSNO AS 学生人数 FROMS GROUPBY DEPT) BWHEREA. DEPFB. DEPT AND A. DEPT=DEPT_INGODECLAREDEPT_IN

9、CHAR 10)SET DEPT_I肆 6算机,EXEC PRO_DEPT_COUNDEPT_IN 利用教师姓名和课程名检索该教师该课的课程名、 选课人数、平均成绩、 最高成绩、并查询教师“张雪的“微机原理课程的情况记录及教师“张朋 的“数据库课程,结果如下图.教师姓名课程名选课总人数 平均成绩 最高成缰1陈雪7微机原理4醒S9UU.EJ消息张朋没有选微机原理这门深程,故总座时画,选深入即年均成绩等统统为口USE jxskIF EXISTS( SELECT NAMEFROMSYSOBJECTSWHERE NAM='PRO_TNAME_CNAME1'ANDTYPE='P&

10、#39;) DROPPROC PRO_TNAME_CNAME1 GOCREATEPROCPRO_DEPT_COUNT1TNAME_INCHAR10),CNAME_INDHAR10), STUDENT_SUMUT OUTPUT GRADE_AVINT OUTPUT GRADE_MANT OUTPUT ASSELECT STUDENT_SUMOUNTSNO, GRADE_AVAVQSCOR 氏 GRADE_MAXAX SCOREFROMT, C, SC, TCWHERESC CNOC. CNOAND C. CNGTC. CNOANDTC. TNOT. TNO and T. TN=TNAME_INA

11、ND C. CNO=CNAME_INGROUPBY TN, CN GODECLARETNAME_MHAR10), CNAME_MHAR 10), STUDENT_SUNT, GRADE_AVGJT, GRADE_MANT SET TNAME_IH 张雪 SET CNAME_IN 微机原理'EXEC PRO_DEPT_COUNTTNAME_IN CNAME_I,N STUDENT_SUMUTPUT GRADE_AVGUTPUT GRADE_MAXUTPUTIF (STUDENT_SUIS NULL)PRINT'没有学生选微机原理这门课程,故总课时数,选课人数,平均成绩等统 统为0

12、'ELSESELECTTNAME_INt师44名,CNAME_IF#生姓名,STUDENT_SUM课总 人数,GRADE_AVG均成绩,GRADE_MAX成绩2 .创立并使用触发器,注意创立触发器前一定要先用IF EXISTS判断一下 是否已创立同名的触发器,假设有先删除在创立. 为表sc创立一触发器：当插入或修改一个记录时,保证此记录的成绩 在0100分之间.(思路：新的数据行就会被插入 inserted表,通过检查该表 判断插入的行是否在0100之间,如果在正常插入,否那么不做插入,回滚至插入的状态.),是否弹出如下图的错误.请问该行数插入数据行(s5','c1&#

13、39;,'101')据是否插入到sc表中药消息|成绩超出口而,清重新输入, 消息3609,级刑18,状态I,第1行 事务在触发器中结束'批处理已中止.USE jxskIF EXISTS ( SELECTNAMEFROMSYSOBJECTSWHERENAM='SCORE_SC_TRI' ANDTYPE='TR')DROPTRIGGER SCORE_SC_TRIGOCREATETRIGGER SCORE_SC_TRION SC FOR INSERT, UPDATEASDECLARESCORE_INTINYINTSELECT SCORE_I=S

14、COREFROMINSERTEDIF SCORE_IN0 ORSCORE_IN100BEGINPRINT '成绩超出0-100 !请重新输入.'ROLLBACKTRANSACTIONENDGOINSERT INTO SC VALUES ('s5' , 'c1' , '101' )/* 触发器激活,插入失败*/UPDATESC SET SCOR=101 WHERESNO='s5' ANDCNO'cl'/* 触发器激活,更新失败*/ 为数据库表T创立一触发器：当职称从“讲师晋升为“副教授时,津贴自动增加

15、500元,从“副教授晋升为“教授时,岗位津贴自动增加900元.更新前:tnotnageprofsalcommdept1|riniBinini1 tl"1i李力男47教授16003000计算机2t2,王平女26讲师8S01200信息3刘伟30讲师9001200计算机414张雪女51教授3000自动化St5张兰女39副教授13002000信息更新后:tnotn为0pr&£salcorrimd'&pt1jraiBIHIBIVtl李力黑4T教授15003000计算机2t2"王平女28讲师8501200信息3t3文帷勇30副教授9叩1TOO计算机4t4张雪女51教授19003000自动化5七5张兰女39副教授13002000信息USE jxskIF EXISTS (SELECT NAMEFROMSYSOBJECTSWHERENAM='UT' AND TYPE='TR')DROPTRIGGERUTGOCREATETRIGGERUTONT FOR UPDATEASDECLAREPROF_OLECHAR 10)DECLAREPROF_NEWHAR10)DECLARETNOCHAR2)SELECT PROF_OLPROF FROM DELETEDSELE