数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试142学号：**********姓名：***日期：2016-11-141 实验目的：1)掌握SQL进行存储过程创建和调用的方法；2)掌握SQL进行触发器定义的方法，理解触发器的工作原理；3)掌握触发器禁用和重新启用的方法。
2 实验平台：操作系统：Windows xp。
实验环境：SQL Server 2000以上版本。
3 实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。
1.创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。
存储过程定义代码：CREATE PROCEDURE JSXX_PROCASSELECT tn 教师姓名,cn 所教课程FROM T,TC,C WHERE T.tno=TC.tno AND o=o存储过程执行语句与执行结果截图：EXECUTE JSXX_PROC2.创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。
存储过程定义代码：CREATE PROCEDURE XM_PROC @sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE sn=@sname)SELECT S.sno 学号,cn 课程,score 成绩FROM S,SC,C WHERE o=o AND SC.sno=S.sno ANDS.sn=@snameELSEPRINT'无该姓名的同学。
'END运行截图：3.创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。
如果没有该专业，则显示“无此专业”。
存储过程定义代码：CREATE PROCEDURE XBNL_PROC@departName VARCHAR(30)='计算机',@begin INT,@end INTASDECLARE @numOfBoys INTDECLARE @numOfGirls INTDECLARE @d# VARCHAR(3)DECLARE @result VARCHAR(50)BEGINSELECT @d# = dno FROM D WHERE dn=@departNameIF @d# IS NOT NULLBEGINSELECT @numOfBoys =COUNT(sno)FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='男'SELECT@numOfGirls =COUNT(sno)FROM S WHERE age BETWEEN@begin AND@end AND dno=@d# AND sex='女'SET @result = @departName+'专业年龄在'+CAST(@begin AS VARCHAR(3))+'-'+CAST(@end AS VARCHAR(3))+'之间的男生有'+CAST(@numOfBoys AS VARCHAR(3))+'人，'+'女生有'+CAST(@numOfGirls AS VARCHAR(3))+'人'ENDELSESET @result='无此专业。
'PRINT @resultEND执行结果：4.执行XM_PROC存储过程，查询“贾慧”同学的学号、所学课程名称和成绩。
存储过程调用语句及其执行结果截图：EXEC XM_PROC'贾慧'5.如果学生表中无“贾慧”同学，则另查询一位学生表中出现过的学生姓名；如果学生表中有“贾慧”同学，则另查询一位学生表中没有的学生姓名。
存储过程调用语句及其执行结果截图：EXEC XM_PROC'张明'6.执行XBNL_PROC存储过程，查询计算机专业各年龄的男、女生人数。
（要求计算机专业必须有至少3个年龄段的男女学生）存储过程调用语句及其执行结果截图：EXEC XBNL_PROC@begin=21,@end=25 --默认：计算机EXEC XBNL_PROC'计算机',18,25EXEC XBNL_PROC'计算机',23,25EXEC XBNL_PROC'计算机',20,217.执行XBNL_PROC存储过程，输入一个专业表中没有的专业。
存储过程调用语句及其执行结果截图：EXEC XBNL_PROC'音乐',18,258.分别查看XBNL_PROC存储过程的一般信息。
执行语句及结果截图：EXEC sp_help XM_PROC9.删除XM_PROC存储过程。
执行语句及结果截图：DROP PROC XM_PROC10.创建班级表B(bno,bn,num)，三个属性分别表示班号，班名，人数；在学生S表中增加一个属性班号。
表创建语句：--创建班级表：CREATE TABLE B(bno VARCHAR(3)PRIMARY KEY,bn VARCHAR(20)UNIQUE NOT NULL,num INT)--S表添加班级属性：ALTER TABLE S ADD bno VARCHAR(3)执行结果截图：11.创建触发器T_insertS，当向S表添加记录时，验证学生的班级号是否出现在B表中，如果不是则撤销S表添加记录的操作；，如果学生的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：CREATE TRIGGER T_insertSON SFOR INSERTASBEGINDECLARE @bno VARCHAR(3)DECLARE @sno VARCHAR(10)SELECT @bno=bno FROM insertedSELECT @sno=sno FROM insertedIF EXISTS(SELECT NULL FROM B WHERE bno=@bno)BEGINUPDATE B SET num=num+1 WHERE bno=@bnoPRINT'添加成功！'ENDELSEBEGINDELETE FROM S WHERE sno=@snoPRINT'添加失败！'ENDEND向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：插入记录前查询：SELECT*FROM S WHERE sn='翠翠'插入记录：INSERT INTO S VALUES('S19','翠翠','女',18,'D2',3)插入后查询：SELECT*FROM S WHERE sn='翠翠'向S表中插入一位学生，其班号在B表出现过。
测试触发器的功能截图：插入前查询：SELECT*FROM S WHERE sn='翠翠'插入记录：INSERT INTO S VALUES('S19','翠翠','女',18,'D2',1)插入后查询：SELECT*FROM S WHERE sn='翠翠'禁用T_insertS触发器语句；ALTER TABLE S DISABLE TRIGGER T_insertS向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：INSERT INTO S VALUES('S20','翠花','女',18,'D2',3)SELECT*FROM S WHERE sn='翠花'启用T_insertS触发器语句；ALTER TABLE S ENABLE TRIGGER T_insertS向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：INSERT INTO S VALUES('S21','翠花儿','女',19,'D2',3)SELECT*FROM S WHERE sn='翠花儿'12.创建触发器T_updateS，当修改S表一位同学的班级字段值时，验证学生的班级号是否出现在B表中，如果不是则撤销对S表中该条记录的修改操作，如果修改后的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：CREATE TRIGGER T_updateSON SFOR UPDATEASBEGINDECLARE @b# VARCHAR(3)DECLARE @b#old VARCHAR(3)DECLARE @numOld INTSELECT @b#=bno FROM insertedSELECT @b#old=bno FROM deletedSELECT @numOld=num FROM B WHERE bno=@b#oldIF EXISTS(SELECT NULL FROM B WHERE bno=@b#)BEGINIF (@numOld IS NOT NULL)BEGINUPDATE B SET num=num+1 WHERE bno=@b#UPDATE B SET num=num-1 WHERE bno=@b#oldENDELSEUPDATE B SET num=num+1 WHERE bno=@b#PRINT'更新成功!'ENDELSEBEGINUPDATE S SET bno=@b#old WHERE bno=@b#PRINT'更新失败!'ENDEND修改S表中一位学生的班级号，修改后的班号不在B表中。
测试触发器的功能截图：UPDATE S SET bno='88'WHERE sno='S1'修改S表中一位学生的班级号，修改后的班号在B表中。
测试触发器的功能截图：SELECT sno AS'学号',bno AS'班级编号'FROM S WHERE sno='S18'SELECT bno '班级编号',num AS'班级人数'FROM BUPDATE S SET bno='8'WHERE sno='S18'SELECT sno AS'学号',bno AS'班级编号'FROM S WHERE sno='S18'SELECT bno '班级编号',num AS'班级人数'FROM B4 深入思考与讨论1）请按自己的理解，说明一下触发器的工作原理。