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

第页实验项目(课题)教学实施方案第6次课第周星期第节授课日期:年月日实验项目(课题)名称:SQL数据操作计划学时:实验类型:1.演示性□2.验证性√3.综合性□4.设计性□5.其它□实验六：存储过程和触发器一、实验目的1、理解存储过程的概念，掌握创建与运行存储过程的一般方法，加深对客户/服务器机制的理解。2、掌握触发器的创建方法，学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解。3、比较存储过程和触发器的异同。4、掌握视图的概念、学会使用SQL语句创建和修改视图、学会使用SQL语句更新视图对应的基表二、实验要求1、实验前：预习实验内容，学习相关知识。2、实验中：按照实验内容要求进行实验，实验时注每种SQL语句的基本命令及各个关键字的含义，做好实验记录。3、实验后：分析实验结果，总结实验知识，得出结论，按格式写出实验报告。4、在整个实验过程中，要独立思考、独立按时完成实验任务，不懂的要虚心向教师或同学请教。5、要求按指定格式书写实验报告，且报告中应反映出对次实验的总结，下次实验前交实验报告。三、实验重点与难点1、重点：（1）有参数的存储过程的建立（2）触发器的执行（3）对视图的操作2、难点：设计一系列操作触发触发器的执行。四、仪器设备及用具硬件：每位同学分配PC机一台软件：windows环境安装好SQLServer第页五、教学过程（一）实验预习（1）熟悉SQL中的创建存储过程和触发器的SQL语句（2）准备好实验所用的数据库及原始数据（二）实验原理在查询分析器下利用SQL命令完成对所要求的存储过程的创建和执行，完成触发器的执行和设计相应操作触发触发器的执行。（三）实验内容实验数据：dept、class、student、course和SC五个表及实验三插入到表中的数据实验内容：1、存储过程的建立和执行分别建立有没参数、有参数（定义缺省和不定义缺省）、有返回值的存储过程。存储过程的执行2、触发器的创建与执行各类触发器分别建立一个，再设计一组有效和无效的操作数据，触发各个触发器执行3、视图的建立与操作（四）注意事项1、如果为存储过程指定参数且没有定义参数的缺省值，必须在运行时给出参数值2、对表者操作时触发相应的触发器执行，因此设计操作时必须要设计满足触发器条件和不满足条件两种操作，才能体会到触发器的作用3、只有单表视图才进行插入、删除和修改操作。（五）实验步骤：1、恢复上次课的数据库表结构和数据。2、完成以下存储过程和触发器的创建与执行1）存储过程的创建与执行（1）建立存储过程P1，查询选修了某门课程的学生信息（其中课程名，返回值自行设计）（2）建立存储过程P2，将某班的所有学生的成绩放在一个临时表中。（3）建立存储过程P3，计算某一个专业的某一门课和的平均成绩,显示出专业名称、课程名称和平均成绩（4）建立存储过程p1，功能：查询成绩在X到Y之间的学生信息，设计返回值：当输入的值为空时：返回值10，提示“请提供范围！”；当输入的X或Y不在0到100之间时：返回值11，提示“输入的值不对！”；当输入的值XY时：返回值12，提示“X第页应小于Y！”；当查找后没有满足条件的记录时：返回值-10，提示“对不起！没有满足条件的记录！”（5）运行所建立的存储过程，观察其执行结果。2）触发器的建立首先删除各个表中的外码约束。（1）在课程表和成绩表上建立一组触发器，实现课程表的课程号与成绩表的课程号之间的参照关系。（2）在成绩表上建立一个插入和修改类的触发器，实现当修改成绩时，检查修改后的成绩是否在0到100之间，如果是，插入或修改成功，否则失败。（3）触发器的执行分别对学生表、课程表和成绩表进行插入、删除和修改操作，触发相应触发器的执行，观察其执行结果。3）视图的建立和操作（1）在查询分析器中创建以下的视图：视图1全体学生的学号与姓名视图2既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。视图3某门课程考试成绩相同的学生学号和课程号视图4选修了课程名为“信息系统”的学生学号和姓名（2）在查询分析器中用INSERT、UPDATE、DELETE语句更新视图1中的数据（3）在查询分析器用Select语句完成对视图的查询存储过程示例：createprocd_s2@classno1varchar(20)asbegincreatetable#class11(snovarchar(20),cnovarchar(20),gradeint)insertinto#class11select*fromscwheresnoin(selectsnofromstudentwhereclassno=@classno1)select*from#class11end触发器示例：altertriggerin_sconscforinsert,updateasifnotexists(selectstudent.snofromstudent,insertedwherestudent.sno=inserted.sno)ornotexists(selectcourse.cnofromcourse,insertedwhere第页course.cno=inserted.cno)beginprint('没有该学生学号或没有该课程号')rollbackend（六）思考与练习1、存储过程与触发器的运行方式有什么不同？2、编写存储过程实现对表的插入、删除与修改3、满足什么条件的视图才能进行记录插入、修改和删除？4、原表中数据发生变化，视图中的数据是否可能跟着一起变化？（七）实验报告要求见实验一课件参考文献[1]《数据库系统及应用》，崔巍，高等教育出版社，2005[2]《数据库系统与应用》，赵致格，清华大学出版社，2005[3]《SQLSEVER数据库原理及应用》，张莉，清华大学出版社，2005下次实验内容1、综合设计实验第页实验项目(课题)教学实施方案第7次课第周星期第节授课日期:年月日实验项目(课题)名称:SQL数据操作计划学时:实验类型:1.演示性□2.验证性√3.综合性□4.设计性□5.其它□选作实验实验名称：数据库事务及并发控制实验目的：一、试验目的事务处理及其并发控制是所有数据库产品的一个关键问题，是提供数据一致性和可恢复性的重要保障，事务处理方式的不同会导致数据库性能和功能上的巨大差异，熟练掌握数据库事务处理和并发控制的基本原理对于后续的学习和工作有着重要的帮助。1).通过试验，加深学生对事务的基本概念理解语掌握；2).通过试验，加深学生对并发控制的基本概念理解，认识不正确的并发控制所带来的危害；3).通过试验，加深学生对锁的基本概念的掌握与理解，认识锁带来的问题；二、实验环境硬件：奔腾4处理器，1.8GHz，512M内存操作系统软件：WindowsXP数据库系统：SQLServer2000桌面版数据库用户帐号：sa数据库用户口令：使用的数据库：Northwind数据库表：##temp数据库工具：企业管理器、查询分析器三、试验内容1、内容：事务的控制2、内容：事务的隔离级别试验3、内容：锁的模拟四、试验步骤开机后，打开企业管理器，连接到数据库后，进入查询分析器。1、事务的控制数据准备：执行如下命令Select*into##tempfromCustomers，将Customers的数据倒入到##temp表中。启动事务，执行删除后，回滚事务第页选择##temp的数据，察看记录总数显式启动事务删除##temp表的数据选择##temp数据，察看记录总数回滚事务选择##temp数据，察看记录总数启动事务，执行删除后，提交事务选择##temp数据，察看记录总数显式启动事务删除##temp表的数据选择##temp的数据，察看记录总数回滚事务选择##temp数据，察看记录总数比较这两次执行效果的差异，为什么会有这些差异？2、事务的隔离级别试验数据准备：执行如下命令Select*into##tempfromCustomers，将Customers的数据倒入到##temp表中。脏读启动两个查询分析器，分别叫（A，B）在A中，选择##temp表中数据，察看记录总数在A中，显式启动事务在A中，删除##temp表的数据在B中，将事务隔离级别设为UNCOMMITTED在该查询分析器中选择##temp表数据，察看记录总数回到先前窗口回滚事务选择##temp数据，察看记录总数再次切查询分析器窗口选择##temp数据，察看记录总数观察结果，为什么会有这些现象？不可重复读启动两个查询分析器，分别叫（A，B）在A中，显式启动事务，察看customerid='ANTON'的记录（注意地址中的数据）在B中，显式启动事务，察看customerid='ANTON'的记录（注意地址中的数第页据）在B中，将customerid='ANTON’地址更新为’AAAAAA’在B中，再次察看customerid='ANTON'的记录（注意地址中的数据）在B中，提交事务在A中，再次察看customerid='ANTON'的记录（注意地址中的数据）在A中，提交事务观察结果，为什么会有这些现象？丢失修改启动两个查询分析器，分别叫（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’的记录，结果如何，为什么?3、锁的模拟启动两个查询分析器，分别叫（A，B）在A中，显式启动事务，察看customerid='ANTON'的记录在B中，显式启动事务，察看customerid='ANTON'的记录在B中，将customerid='ANTON’地址更新为’AAAAAA’在A中，将customerid='ANTON’地址更新为’BBBBBB’观察A窗口的结果，为什么？在B中，提交事务观察A窗口的结果，为什么？在A窗口，再次察看customerid='ANTON'的记录，结果如何，为什么?附常用命令：启动事：begintransaction启动事务：commit回滚事务：rollback事务隔离级别设置：settransactionisolationlevel{readcommitted,readuncommitted,第页repeatableread,serializable}参考文献[1]《数据库系统及应用》，崔巍，高等教育出版社，2005[2]《数据库系统与应用》，赵致格，清华大学出版社，2005[3]《SQLSEVER数据库原理及应用》，张莉，清华大学出版社，2005下次实验内容存储过程的建立、修改和执行，触发器的建立和修改，设计一组操作触发触发器的执行