实验教案—实验六(存储过程和触发器)(2005

1、实验项目(课题)教学实施方案第 6 次课第 周 星期 第 节授课日期: 年 月 日实验项目(课题)名称:SQL 数据操作计划学时: 实验类型: 1.演示性 2.验证性 3.综合性 4.设计性 5.其它实验六：存储过程和触发器一、实验目的1、理解存储过程的概念，掌握创建与运行存储过程的一般方法，加深对客户/服务器机制的理解。2、掌握触发器的创建方法，学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解。3、比较存储过程和触发器的异同。4、掌握视图的概念、学会使用SQL 语句创建和修改视图、学会使用SQL 语句更新视图对应的基表二、实验要求1、实验前：预习实验内容，学习相关知识。2

2、、实验中：按照实验内容要求进行实验，实验时注每种SQL语句的基本命令及各个关键字的含义，做好实验记录。3、实验后：分析实验结果，总结实验知识，得出结论，按格式写出实验报告。4、在整个实验过程中，要独立思考、独立按时完成实验任务，不懂的要虚心向教师或同学请教。5、要求按指定格式书写实验报告，且报告中应反映出对次实验的总结，下次实验前交实验报告。三、实验重点与难点1、重点：（1）有参数的存储过程的建立（2）触发器的执行（3）对视图的操作2、难点： 设计一系列操作触发触发器的执行。四、仪器设备及用具硬件：每位同学分配PC机一台软件：windows环境安装好SQL Server五、教学过程（一）实验预

3、习（1）熟悉SQL中的创建存储过程和触发器的SQL语句（2）准备好实验所用的数据库及原始数据（二）实验原理在查询分析器下利用SQL命令完成对所要求的存储过程的创建和执行，完成触发器的执行和设计相应操作触发触发器的执行。（三）实验内容实验数据：dept、class、student、course和SC五 个表及实验三插入到表中的数据实验内容：1、存储过程的建立和执行 分别建立有没参数、有参数（定义缺省和不定义缺省）、有返回值的存储过程。存储过程的执行2、触发器的创建与执行 各类触发器分别建立一个，再设计一组有效和无效的操作数据 ，触发各个触发器执行3、视图的建立与操作（四）注意事项1、如果为存储过

4、程指定参数且没有定义参数的缺省值，必须在运行时给出参数值2、对表者操作时触发相应的触发器执行，因此设计操作时必须要设计满足触发器条件和不满足条件两种操作，才能体会到触发器的作用3、只有单表视图才进行插入、删除和修改操作。（五）实验步骤：1、恢复上次课的数据库表结构和数据。 2、完成以下存储过程和触发器的创建与执行1）存储过程的创建与执行（1）建立存储过程P1，查询选修了某门课程的学生信息（其中课程名，返回值自行设计）（2）建立存储过程P2，将某班的所有学生的成绩放在一个临时表中。（3）建立存储过程P3，计算某一个专业的某一门课和的平均成绩,显示出专业名称、课程名称和平均成绩（4）建立存储过程p

5、1，功能：查询成绩在X到Y之间的学生信息，设计返回值： 当输入的值为空时：返回值10，提示“请提供范围 ！”；当输入的X或Y不在0到100之间时：返回值11，提示“输入的值不对！”；当输入的值XY时：返回值12，提示“X应小于Y！”；当查找后没有满足条件的记录时：返回值-10，提示“对不起！没有满足条件的记录！”（5）运行所建立的存储过程，观察其执行结果。2）触发器的建立 首先删除各个表中的外码约束。（1）在课程表和成绩表上建立一组触发器，实现课程表的课程号与成绩表的课程号之间的参照关系。（2）在成绩表上建立一个插入和修改类的触发器，实现当修改成绩时，检查修改后的成绩是否在0到100之间，如果

6、是，插入或修改成功，否则失败。（3）触发器的执行 分别对学生表、课程表和成绩表进行插入、删除和修改操作，触发相应触发器的执行，观察其执行结果。3）视图的建立和操作（1）在查询分析器中创建以下的视图：视图1 全体学生的学号与姓名视图2 既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。 视图3 某门课程考试成绩相同的学生学号和课程号视图4 选修了课程名为“信息系统”的学生学号和姓名（2）在查询分析器中用INSERT、UPDATE、DELETE语句更新视图1中的数据（3）在查询分析器用Select语句完成对视图的查询存储过程示例：create proc d_s2 classno1 var

7、char(20) asbegincreate table #class11 (sno varchar(20),cno varchar(20),grade int )insert into #class11 select * from sc where sno in (select sno from student where classno=classno1) select * from #class11end触发器示例：alter trigger in_sc on sc for insert,updateasif not exists (select student.sno from stu

8、dent,inserted where student.sno=inserted.sno) or not exists (select o from course ,inserted where o=o)beginprint(没有该学生学号或没有该课程号)rollbackend（六）思考与练习1、存储过程与触发器的运行方式有什么不同？2、编写存储过程实现对表的插入、删除与修改3、满足什么条件的视图才能进行记录插入、修改和删除？4、原表中数据发生变化，视图中的数据是否可能跟着一起变化？（七）实验报告要求 见实验一课件参考文献1数据库系统及

9、应用，崔巍，高等教育出版社，20052 数据库系统与应用，赵致格，清华大学出版社，20053SQL SEVER数据库原理及应用，张莉，清华大学出版社，2005下次实验内容1、 综合设计实验实验项目(课题)教学实施方案第 7 次课第 周 星期 第 节授课日期: 年 月 日实验项目(课题)名称:SQL 数据操作计划学时: 实验类型: 1.演示性 2.验证性 3.综合性 4.设计性 5.其它选作实验实验名称：数据库事务及并发控制实验目的：一、 试验目的事务处理及其并发控制是所有数据库产品的一个关键问题，是提供数据一致性和可恢复性的重要保障，事务处理方式的不同会导致数据库性能和功能上的巨大差异，熟练掌

10、握数据库事务处理和并发控制的基本原理对于后续的学习和工作有着重要的帮助。1). 通过试验，加深学生对事务的基本概念理解语掌握；2). 通过试验，加深学生对并发控制的基本概念理解，认识不正确的并发控制所带来的危害；3). 通过试验，加深学生对锁的基本概念的掌握与理解，认识锁带来的问题；二、 实验环境硬件： 奔腾4 处理器，1.8GHz，512M内存操作系统软件： WindowsXP数据库系统： SQLServer 2000 桌面版数据库用户帐号：sa数据库用户口令： 使用的数据库： Northwind数据库表： #temp数据库工具： 企业管理器、查询分析器三、 试验内容1、内容：事务的控制2、

11、内容：事务的隔离级别试验3、内容：锁的模拟四、 试验步骤开机后，打开企业管理器，连接到数据库后，进入查询分析器。1、 事务的控制数据准备：执行如下命令Select * into #temp from Customers，将Customers的数据倒入到#temp表中。n 启动事务，执行删除后，回滚事务 选择#temp的数据，察看记录总数 显式启动事务 删除#temp表的数据 选择#temp数据，察看记录总数 回滚事务 选择#temp数据，察看记录总数n 启动事务，执行删除后，提交事务 选择#temp数据，察看记录总数 显式启动事务 删除#temp表的数据 选择#temp的数据，察看记录总数 回

12、滚事务 选择#temp数据，察看记录总数 比较这两次执行效果的差异，为什么会有这些差异？2、 事务的隔离级别试验数据准备：执行如下命令Select * into #temp from Customers，将Customers的数据倒入到#temp表中。n 脏读 启动两个查询分析器，分别叫（A，B） 在A中，选择#temp表中数据，察看记录总数 在A中，显式启动事务 在A中，删除#temp表的数据 在B中，将事务隔离级别设为UNCOMMITTED 在该查询分析器中选择#temp表数据，察看记录总数 回到先前窗口 回滚事务 选择#temp数据，察看记录总数 再次切查询分析器窗口 选择#temp数据

13、，察看记录总数 观察结果，为什么会有这些现象？n 不可重复读 启动两个查询分析器，分别叫（A，B） 在A中，显式启动事务，察看customerid = ANTON的记录（注意地址中的数据） 在B中，显式启动事务，察看customerid = ANTON的记录（注意地址中的数据） 在B中，将customerid = ANTON地址更新为AAAAAA 在B中，再次察看customerid = ANTON的记录（注意地址中的数据） 在B中，提交事务 在A中，再次察看customerid = ANTON的记录（注意地址中的数据） 在A中，提交事务 观察结果，为什么会有这些现象？n 丢失修改 启动两个查

14、询分析器，分别叫（A，B） 在A中，显式启动事务，察看customerid = ANTON的记录（注意地址中的数据） 在B中，显式启动事务，察看customerid = ANTON的记录（注意地址中的数据） 在B中，将customerid = ANTON地址更新为AAAAAA 在B中，再次察看customerid = ANTON的记录（注意地址中的数据） 在B中，提交事务 在A中，再次察看customerid = ANTON的记录（注意地址中的数据） 在A中，将customerid = ANTON地址更新为BBBBBB 在A中，提交事务 在A，B窗口分别察看customerid = ANTON

15、的记录，结果如何，为什么?3、 锁的模拟 启动两个查询分析器，分别叫（A，B） 在A中，显式启动事务，察看customerid = ANTON的记录 在B中，显式启动事务，察看customerid = ANTON的记录 在B中，将customerid = ANTON地址更新为AAAAAA 在A中，将customerid = ANTON地址更新为BBBBBB 观察A窗口的结果，为什么？ 在B中，提交事务 观察A窗口的结果，为什么？ 在A窗口，再次察看customerid = ANTON的记录，结果如何，为什么?附常用命令：启动事：begin transaction 启动事务：commit回滚事务：rollback 事