北京理工大学-数据库-实验_4-存储过程、触发器

1、(封皮页)北京理工大学数据库实验4主讲老师：赵晓林学生：李经2012/11/214(目录页)实验 4： 存储过程、触发器14.1 作业声明14.2 实验目的14.2.1 建立“学籍与成绩管理系统”表格14.3 实验过程14.3.1 在“学籍与成绩管理系统”表格中输入不符合系统要求的数据14.3.2 建立存储过程，计算学生的总学分、总成绩，并保存在另一张表中24.3.3 查询总成绩表，并进行排序34.3.4 在相关的表上建立触发器，实现主外键的功能34.3.5 讨论触发器与主外键的异同44.3.6 在表上建立触发器实现对数据录入、修改的限制44.3.7 讨论视图、存储过程、触发器、用户自定义函数

2、的使用范围及优缺点44.4 实验结论54.5 实验体会5实验 4： 存储过程、触发器4.1 作业声明本次作业全部由本人完成，若存在抄袭或雷同现象，本人愿意接受老师相应的处理4.2 实验目的4.2.1 建立“学籍与成绩管理系统”表格不建立表之间的参照关系输入数据，以便在表上进行各种操作4.3 实验过程建立“学籍与成绩管理系统”表格操作，输入数据操作同实验三4.3.1 在“学籍与成绩管理系统”表格中输入不符合系统要求的数据（如学生学籍表中学号重复），建立适当的存储过程，分别查找和删除这些不合法的数据4.3.1.1 sql代码insert into xs values('孙悟空',&

3、#39;2120100030','05','08111005','1990-5-1','男');-插入一行非法数据，假设学号合法范围为1120100099create proc check_xhstart char(10),end char(10)asbegindelete from xswhere xs.xh<start or xs.xh>endendselect * from xswhere xs.xh<'1120100001'or xs.xh>'1120100099&#

4、39;exec check_xh '1120100001','1120100099' -调用存储过程select * from xswhere xs.xh<'1120100001'or xs.xh>'1120100099'4.3.1.2 结果调用前后对比，非法数据已经被删除4.3.2 建立存储过程，计算学生的总学分、总成绩，并保存在另一张表中4.3.2.1 sql代码create table cjd(xm varchar(8) not null,xf decimal(5,1),cj decimal(5,1),)crea

5、te proc cjd_showstart char(10),end char(10)asbegininsert into cjdselect xs.xm,sum(kc.xf),sum(xk.cj) from xs,kc,xkwhere xs.xh>=start and xs.xh<=endand xs.xh=xk.xhand xk.kcbh=kc.kcbhgroup by xs.xmEndexec cjd_show '1120100001','1120100099' select * from cjd4.3.2.2 结果4.3.3 查询总成绩表，并

6、进行排序4.3.3.1 sql代码select * from cjd order by cj4.3.3.2 结果成绩表按照总成绩升序排列4.3.4 在相关的表上建立触发器，实现主外键的功能4.3.4.1 Sql代码create trigger xdh_pk on xb -模拟主键after insert asif (select COUNT(*)from xb,inserted where xb.xdh=inserted.xdh)>1beginprint '插入数据重复'rollback transactionendcreate trigger xdh_fk on js

7、-模拟外键after insert asif (select COUNT(*)from xb,inserted where xb.xdh=inserted.xdh)=0beginprint '插入数据不合法'rollback transactionEndinsert into xb values('05','德语'); -插入重复数据insert into js values('姜子牙','9920100005','一级','09'); -非法数据，xdh09不存在于系表中4.3.4

8、.2 结果插入以上两行数据时，结果分别为，系统显示：“插入数据重复消息3609，级别16，状态1，第1 行事务在触发器中结束。批处理已中止。”以及：“插入数据不合法消息3609，级别16，状态1，第1 行事务在触发器中结束。批处理已中止。”可知利用触发器实现主外键功能成功4.3.5 讨论触发器与主外键的异同4.3.5.1 结果1.主外键约束与触发器在遇到问题时都可以返回给用户一定的错误信息。但是，触发器可以返回数据库管理员自定义的错误信息。2.用主键和外键约束来实现效率更高、运算速度更快。3.用触发器可以实现比主外键约束更为复杂的约束。4.3.6 在表上建立触发器实现对数据录入、修改的限制触发

9、器功能：向学生表中插入数据时，出生日期不能大于今天、性别只能为男或女4.3.6.1 Sql代码create trigger xsInfo_check on xs -模拟主键after insert asif (select chrq from inserted)>GETDATE()or (select xb from inserted) not in ('男','女')beginprint '插入数据不合法'rollback transactionendinsert into xs values('赵四','1120

10、100001','01','08111001','1990-1-1','仙');-性别不合法insert into xs values('赵五','1120100001','01','08111001','2052-1-1','男');-出生日期不合法insert into xs values('赵六','1120100001','01','08111001',&#

11、39;2011-1-1','男');-合法数据4.3.6.2 结果插入前两行非法数据时，系统显示：“插入数据不合法消息3609，级别16，状态1，第1 行事务在触发器中结束。批处理已中止。”插入第三行合法数据时，成功插入4.3.7 讨论视图、存储过程、触发器、用户自定义函数的使用范围及优缺点4.3.7.1 结果视图：使用范围：适用于需要查看或更新数据的情况优点：简化查询，数据即时更新缺点：无法进行删除操作存储过程：使用范围：需要处理大量数据时优点：存储过程是已编译过的，执行起来速度更快；可以使用控制语句和变量，并且在一个存储过程中可以调用其他存储过程，大大提高了用户设计程序的效率缺点：需要用户自行设计安全可靠的存储过程触发器：使用范围：通常用于实现强制业务规则和数据完整性优点：支持嵌套，当数据发生变化的时候, 自动作处理缺点：可能会引起不必要的触发逻辑 用户自定义函数使用范围：接受