第15章事务管理.ppt

第15章事务管理.ppt

《第15章事务管理.ppt》由会员分享，可在线阅读，更多相关《第15章事务管理.ppt（47页珍藏版）》请在冰豆网上搜索。

第15章事务管理第15章事务管理15.1Transacat-SQL编程基础15.2事务管理15.3存储过程15.4触发器315.1Transact-SQL编程基础15.1.1函数

（1）字符串函数

（2）日期和时间函数（3）数学函数（4）转换函数（5）系统函数（6）聚合函数（7）用户自定义函数415.1.2程序设计语句1beginend语句beginend语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。

语法形式为：

begin语句1语句2语句nend515.1.2程序设计语句2跳转语句（goto语句）goto语句可以使程序直接跳到标有标识符的指定位置处继续执行，而位于goto语句和标识符之间的程序将不会被执行。

语法形式为：

gotolabel615.1.2程序设计语句3条件分支语句（ifelse语句）ifelse语句是条件判断语句。

语法形式为：

if条件表达式语句1|语句块1else语句2|语句块27例例15-1利用条件分支语句和跳转语句求出从1加到5的总和分析：

（1）利用declare声明两个局部变量sum（总和）和count（计数），初值分别为0和1；

（2）如果计数值count是小于等于5，则进行总和累加和计数值加1，并通过goto语句使程序转到label_1处再次执行此操作；（3）如果计数值count是大于5，则输出计数值count和总和sum；（4）当计数值count小于等于5时需要执行多条语句，因此通过beginend语句将这几条命令组成语句块。

815.1.2程序设计语句4whilecontinuebreak语句用于设置重复执行SQL语句或语句块的条件。

语法形式为：

while条件表达式语句|语句块break|continue9例例15-2利用循环语句计算出从1加到5的总和分析：

（1）利用declare声明两个局部变量sum（总和）和count（计数），初值分别为0和1；

（2）通过while语句实现累加。

1015.1.2程序设计语句5打印输出语句（print语句）可以使用print语句在用户屏幕显示文本型字符串、单个变量的值或字符型全局变量语法形式为：

print字符串|局部变量|全局变量11例例15-3打印出从1加到5的总和分析：

（1）第一个print语句打印字符串；

（2）第二个print语句打印单个变量总和sum。

1215.1.2程序设计语句6终止语句（return语句）return语句用于无条件地终止语句，此时位于return语句之后的程序将不会被执行。

语法形式为：

return整数其中，整数为返回的整型值1315.1.2程序设计语句7注释语句注释是程序代码中不可执行的内容，但它也是程序设计中不可缺少的一部分，它的作用是对程序代码的功能进行说明。

注释语句的格式有以下两种：

（1）整块注释的形式为：

/*注释块*/

（2）从行的后部分注释语句注释14例例15-4注释语句举例分析：

（1）赋值语句select后使用行注释；

（2）print语句前是注释语句块。

1515.2事务管理事务（transaction）是一个工作单元事务的特点是要么全部完成，要么什么都不做实现事务的语句begintransaction|tranSQL语句组committransaction|tran撤销事务的命令rollbacktransaction|tran保存点名称定义保存点savetransaction保存点名称16例例15-15-5创建一个数据表department（dept_id,dept_name）并执行以下命令。

查询出表中数据内容。

分析：

（1）用begintransaction或begintran定义事务的开始；

（2）用committransaction或committran或commit提交事务，事务提交后，insert命令执行的结果将写入数据库；（3）用rollbacktransaction或rollbacktran或rollback回滚到上一个begintran语句，数据库将撤销从上一个begintran语句至rollback语句间的命令对数据库所产生的影响。

17例例15-15-6执行图所示命令，查询出表中数据内容。

分析：

（1）用savetransaction或savetran定义保存点，这里以“A”作为保存点名称；

（2）rollbacktranA作用将第二条insert操作从数据库撤销。

18例例15-15-7在期刊采编系统中，假设将中文编辑部一部的刘邵华提升为主任编辑，并担任中文编辑部二部的负责人，建立一个事务，完成这些变动。

分析：

（1）刘邵华提升为主任编辑，应该将其职务（EmpRole）做更新；

（2）刘邵华担任中文编辑部二部的负责人，则说明其所在部门（DepId）也发生变化，并且部门表（magdept）中负责人（DepManager）也将作更新；（3）以上语句应放在一个事务中，使这些更新全部执行，保证数据正确性。

1915.3存储过程

（1）系统存储过程系统存储过程是SQLServer系统创建的存储过程，它的目的在于能够方便完成与更新数据库表相关的管理任务或其他的系统管理任务

（2）用户自定义的存储过程用户自定义的存储过程是用户创建的、由若干SQL命令组所组成的程序2015.3.1创建和执行存储过程在创建存储过程前，应考虑以下问题：

（1）创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户；

（2）存储过程是数据库对象，其名称必须遵守标识符规则；（3）只能在当前数据库中创建存储过程；（4）一个存储过程的最大容量为128M。

2115.3.1创建和执行存储过程创建存储过程时，需要确定存储过程的三个组成部分：

（1）所有的输入参数以及传给调用者的输出参数；

（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句；（3）返回给调用者的状态值，以指明调用是成功还是失败。

2215.3.1创建和执行存储过程创建存储过程的语法为createprocedure过程名参数名参数类型=默认值outputassql语句组执行存储过程的语法为execute过程名参数名=参数默认值output23例例15-815-8建立一个名为“具有编稿权限的人员”的存储过程并执行，用来列出有编稿权限的人员情况分析：

（1）该存储过程为“具有编稿权限的人员”，不带参数；

（2）该存储过程的sql语句组由一条查询语句构成。

24例例15-9建立一个名为“权限查询”的存储过程，查询具有某种权限的雇员的所有情况分析：

（1）将权限设计为一个参数permit；

（2）使用“+”运算符，实现查询条件中通配符和变量的连接；（3）该存储过程根据参数permit的不同取值查询相应的记录；（4）该存储过程带参数，执行时可以有两种赋值方式。

25例例15-9执行执行结果26例例15-10建立一个名为“人员查询”的存储过程，查询某雇员的所有情况。

参数默认值为空字符串，要求根据参数状态及查询有无结果，返回不同状态值。

具体要求：

（1）当没有输入雇员姓名时，存储过程返回10；

（2）当按照输入的姓名没有检索到相应记录时，存储过程返回20；（3）当按照输入的姓名检索到相应记录时，存储过程返回。

27例例15-10执行结果执行结果28例例15-11建立一个名为“人员职务”的存储过程，查询某雇员的职务情况，并把查询结果以输出参数形式返回分析：

（1）该存储过程的输入参数是雇员姓名，以emp_name表示；

（2）声明输出参数role保存职务信息，参数的类型必需与存储过程定义时的输出参数类型一致。

29例例15-11执行结果执行结果3015.3.2修改存储过程修改存储过程语法形式如下alterprocedure过程名参数名参数类型=默认值outputassql语句组31例例15-12修改存储过程“权限查询”，增加统计出满足条件的雇员的人数。

分析：

（1）利用alterprocedure命令修改存储过程“雇员查询”的定义；

（2）利用count（*）统计出满足条件的雇员数；（3）不能只书写增加的SQL命令。

32例例15-12执行结果执行结果3315.3.3删除存储过程删除存储过程语法dropprocedure过程名组34例例15-13删除存储过程“具有核稿权限的人员”3515.3.4存储过程与事务管理例15-14编写存储过程“期刊编辑”，保证在期刊采编系统数据库中对mag_info表增加一个新记录时完成日期（DesFinishDate）要早于出版日期（PubDate）分析：

（1）要输入的数据作为存储过程的参数；

（2）将输入命令insert作为事务的内容；（3）完成日期小于出版日期即为完成日期早于出版日期。

（4）当完成日期早于出版日期，使事务执行（commit）；（5）当完成日期晚于出版日期，说明数据不正确，回滚事务（rollbacktransaction），取消插入操作。

36例例15-14结果3715.4触发器触发器是定义在表上的一个对象，是一种特殊类型的存储过程。

触发器不需要专门语句调用，它主要是通过事件进行触发而被执行的，即当执行insert、delete和update语句时自动被触发执行。

触发器可以用于SQLServer约束、默认值和规则的完整性检查3815.4.1创建和执行触发器创建触发器时应考虑以下问题：

（1）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户；

（2）虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器；（3）虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。

创建触发器的语法为：

createtrigger触发器名on表名fordelete,insert,updateasSQL语句组39例例15-15创建“更新部门”触发器来保证期刊采编系统数据库中mag_dept表中部门负责人与mag_emp表中部门信息的参照完整性分析：

（1）触发器名“更新部门”是mag_dept表的update触发器；

（2）当对mag_dept进行更新操作时，更新后的数据插入到系统的inserted表中，而对应的原数据移至deleted表中；（3）mag_dept数据发生变化，要保证该部门负责人在mag_emp表中所属部门一致，如果数据不一致，则将mag_emp中部门编号DepId修改为inserted表中DepId值；（4）语句流程为：

a）从inserted表中获取部门编号DepId和负责人姓名DepManager，分别保存在变量depid和manager中；b）查询出该负责人在mag_emp中所在部门编号DepId，并判断是否与变量depid一致，如果不一致，将该负责人（manager）在mag_emp中DepId的值修改为depid。

40例例15-15结果4115.4.1修改触发器修改触发器的语法为：

altertrigger触发器名on表名fordelete,insert,updateasSQL语句组42例例15-1615-16修改触发器“更新部门”，增加打印语句，输出“更新成功”4315.4.2删除触发器语法形式如下：

droptrigger触发器组删除触发器所在的表时，SQLServer将会自动删除与该表相关的触发器。

44例例15-1715-17删除触发器“更新部门”4515.4.4触发器与事务管理触发器最常见的应用是执行复杂的行验证，保证数据完整性。

如果触发器能够确定激发触发器的命令语句是无效的，就能够回滚此事务。

要实现这一功能，可以在触发器内执行rollbacktransaction。

在触发器中执行rollbacktransaction时，应注意以下问题：

由触发器所执行的所有工作都被回滚；触发器继续执行rol