第15章事务管理.ppt

1、第15章 事务管理第15章 事务管理15.1 Transacat-SQL 编程基础15.2 事务管理15.3 存储过程15.4 触发器315.1 Transact-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语句可以使程序直

2、接跳到标有标识符的指定位置处继续执行，而位于goto语句和标识符之间的程序将不会被执行。语法形式为：goto label 615.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）如果计数值co

3、unt是大于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 语句

4、）可以使用print语句在用户屏幕显示文本型字符串、单个变量的值或字符型全局变量语法形式为：print 字符串|局部变量|全局变量11例例15-3 打印出从1加到5的总和分析：（1）第一个print语句打印字符串；（2）第二个print语句打印单个变量总和sum。1215.1.2 程序设计语句 6终止语句（return语句）return语句用于无条件地终止语句，此时位于return语句之后的程序将不会被执行。语法形式为：return 整数其中，整数为返回的整型值1315.1.2 程序设计语句 7注释语句 注释是程序代码中不可执行的内容，但它也是程序设计中不可缺少的一部分，它的作用是对程序代码的

5、功能进行说明。注释语句的格式有以下两种：（1）整块注释的形式为：/*注释块*/（2）从行的后部分注释语句注释14例例15-4 注释语句举例分析：（1）赋值语句select 后使用行注释；（2）print语句前是注释语句块。1515.2 事务管理事务（transaction）是一个工作单元事务的特点是要么全部完成，要么什么都不做实现事务的语句begin transaction|tran SQL语句组 commit transaction|tran 撤销事务的命令rollback transaction|tran 保存点名称定义保存点save transaction 保存点名称16例例15-15-

6、5 创建一个数据表department（dept_id,dept_name）并执行以下命令。查询出表中数据内容。分析：（1）用begin transaction或begin tran定义事务的开始；（2）用commit transaction或commit tran或commit提交事务，事务提交后，insert命令执行的结果将写入数据库；（3）用rollback transaction或rollback tran或rollback回滚到上一个begin tran语句，数据库将撤销从上一个begin tran语句至rollback语句间的命令对数据库所产生的影响。17例例15-15-6 执行图

7、所示命令，查询出表中数据内容。分析：（1）用save transaction或save tran定义保存点，这里以“A”作为保存点名称；（2）rollback tran A作用将第二条insert 操作从数据库撤销。18例例15-15-7 在期刊采编系统中，假设将中文编辑部一部的刘邵华提升为主任编辑，并担任中文编辑部二部的负责人，建立一个事务，完成这些变动。分析：（1）刘邵华提升为主任编辑，应该将其职务（EmpRole）做更新；（2）刘邵华担任中文编辑部二部的负责人，则说明其所在部门（DepId）也发生变化，并且部门表（magdept）中负责人（DepManager）也将作更新；（3）以上语句

8、应放在一个事务中，使这些更新全部执行，保证数据正确性。1915.3 存储过程（1）系统存储过程 系统存储过程是SQL Server系统创建的存储过程，它的目的在于能够方便完成与更新数据库表相关的管理任务或其他的系统管理任务（2）用户自定义的存储过程 用户自定义的存储过程是用户创建的、由若干SQL命令组所组成的程序2015.3.1 创建和执行存储过程 在创建存储过程前，应考虑以下问题：（1）创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户；（2）存储过程是数据库对象，其名称必须遵守标识符规则；（3）只能在当前数据库中创建存储过程；（4）一个存储过程的最大容量为128M。21

9、15.3.1 创建和执行存储过程 创建存储过程时，需要确定存储过程的三个组成部分：（1）所有的输入参数以及传给调用者的输出参数；（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句；（3）返回给调用者的状态值，以指明调用是成功还是失败。2215.3.1 创建和执行存储过程 创建存储过程的语法为create procedure 过程名 参数名 参数类型=默认值 output as sql语句组执行存储过程的语法为execute 过程名 参数名=参数默认值output23例例15-8 15-8 建立一个名为“具有编稿权限的人员”的存储过程并执行，用来列出有编稿权限的人员情况分析：（1）该

10、存储过程为“具有编稿权限的人员”，不带参数；（2）该存储过程的sql语句组由一条查询语句构成。24例例15-9 建立一个名为“权限查询”的存储过程，查询具有某种权限的雇员的所有情况分析：（1）将权限设计为一个参数permit；（2）使用“+”运算符，实现查询条件中通配符和变量的连接；（3）该存储过程根据参数permit的不同取值查询相应的记录；（4）该存储过程带参数，执行时可以有两种赋值方式。25例例15-9 执行执行结果26例例15-10 建立一个名为“人员查询”的存储过程，查询某雇员的所有情况。参数默认值为空字符串，要求根据参数状态及查询有无结果，返回不同状态值。具体要求：（1）当没有输入

11、雇员姓名时，存储过程返回10；（2）当按照输入的姓名没有检索到相应记录时，存储过程返回20；（3）当按照输入的姓名检索到相应记录时，存储过程返回。27例例15-10 执行结果执行结果28例例15-11 建立一个名为“人员职务”的存储过程，查询某雇员的职务情况，并把查询结果以输出参数形式返回分析：（1）该存储过程的输入参数是雇员姓名，以emp_name 表示；（2）声明输出参数role 保存职务信息，参数的类型必需与存储过程定义时的输出参数类型一致。29例例15-11 执行结果执行结果3015.3.2 修改存储过程 修改存储过程语法形式如下alter procedure 过程名 参数名 参数类型

12、=默认值 output as sql语句组31例例15-12 修改存储过程“权限查询”，增加统计出满足条件的雇员的人数。分析：（1）利用alter procedure命令修改存储过程“雇员查询”的定义；（2）利用count（*）统计出满足条件的雇员数；（3）不能只书写增加的SQL命令。32例例15-12 执行结果执行结果3315.3.3 删除存储过程 删除存储过程语法 drop procedure 过程名组 34例例15-13 删除存储过程“具有核稿权限的人员”3515.3.4 存储过程与事务管理例15-14 编写存储过程“期刊编辑”，保证在期刊采编系统数据库中对mag_info表增加一个新记

13、录时完成日期（DesFinishDate）要早于出版日期（PubDate）分析：（1）要输入的数据作为存储过程的参数；（2）将输入命令insert作为事务的内容；（3）完成日期小于 出版日期即为完成日期早于出版日期。（4）当完成日期早于出版日期，使事务执行（commit）；（5）当完成日期晚于出版日期，说明数据不正确，回滚事务（rollback transaction），取消插入操作。36例例15-14 结果3715.4 触发器 触发器是定义在表上的一个对象，是一种特殊类型的存储过程。触发器不需要专门语句调用，它主要是通过事件进行触发而被执行的，即当执行insert、delete和update

14、语句时自动被触发执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查3815.4.1 创建和执行触发器创建触发器时应考虑以下问题：（1）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户；（2）虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器；（3）虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。创建触发器的语法为：create trigger 触发器名on 表名 for delete,insert ,update as SQL语句组 39例例15-15 创建“更新部门”触发器来保证期刊采编系统数据库中mag_dept表中部

15、门负责人与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

16、_emp中所在部门编号DepId，并判断是否与变量depid一致，如果不一致，将该负责人（manager）在mag_emp中DepId的值修改为depid。40例例15-15 结果4115.4.1 修改触发器修改触发器的语法为：alter trigger 触发器名on 表名 for delete,insert ,update as SQL语句组 42例例15-16 15-16 修改触发器“更新部门”，增加打印语句，输出“更新成功”4315.4.2 删除触发器 语法形式如下：drop trigger 触发器组删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。44例例15-17 15-17 删除触发器“更新部门”4515.4.4 触发器与事务管理触发器最常见的应用是执行复杂的行验证，保证数据完整性。如果触发器能够确定激发触发器的命令语句是无效的，就能够回滚此事务。要实现这一功能，可以在触发器内执行rollback transaction。在触发器中执行rollback transaction 时，应注意以下问题：由触发器所执行的所有工作都被回滚；触发器继续执行rol