1、数据库基础与实践实验报告实验五 存储过程和触发器班级：惠普测试 142学号：1408090213姓名：闫伟明日期：2016-11-14 1 实验目的：1) 掌握 SQL 进行存储过程创建和调用的方法；2) 掌握 SQL 进行触发器定义的方法，理解触发器的工作原理；3) 掌握触发器禁用和重新启用的方法。2 实验平台：操作系统：Windows xp。实验环境：SQL Server 2000 以上版本。3 实验内容与步骤利用实验一创建的 sch_id 数据库完成下列实验内容。1. 创建存储过程 JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。存储过程定义代码：CREATE
2、PROCEDURE JSXX_PROCASSELECT tn 教师姓名,cn 所教课程 FROM T,TC,C WHERE T.tno=TC.tno AND TC.cno=C.cno存储过程执行语句与执行结果截图：EXECUTE JSXX_PROC2. 创建存储过程 XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。存储过程定义代码：CREATE PROCEDURE XM_PROC sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE s
3、n=sname)SELECT S.sno 学号,cn 课程,score 成绩 FROM S,SC,C WHERE SC.cno=C.cno AND SC.sno=S.sno AND S.sn=snameELSEPRINT 无该姓名的同学。END运行截图：3. 创建存储过程 XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。如果没有该专业，则显示“无此专业”。存储过程定义代码：CREATE PROCEDURE XBNL_PROC departName VARCHAR(30)=计算机,begin INT,end INTASDECLA
4、RE 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 b
5、egin 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_PRO
6、C 贾慧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. 执
7、行 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(2
8、0) UNIQUE NOT NULL,num INT)-S 表添加班级属性：ALTER TABLE S ADD bno VARCHAR(3)执行结果截图：11. 创建触发器 T_insertS，当向 S 表添加记录时，验证学生的班级号是否出现在 B 表中，如果不是则撤销 S 表添加记录的操作；，如果学生的班号在班级表中则自动修改相应班级的人数字段值。触发器创建语句：CREATE TRIGGER T_insertS ON S FOR INSERTASBEGINDECLARE bno VARCHAR(3)DECLARE sno VARCHAR(10)SELECT bno=bno FROM inse
9、rtedSELECT 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)插入后查
10、询：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=翠花
