第十五课--确保数据的一致性VIP

Copyright 2007创智新程 确保数据的一致性 Copyright 2007 2009创智新程 Copyright 2007创智新程 确保数据的一致性 事务的定义利用SQLServer实现事务的功能事务的创建事务的提交事务回滚和回滚事务的部分游标的使用声明游标打开游标从游标中获取数据关闭游标 Copyright 2007创智新程 数据的一致性和完整性问题 设想现在最流行的电子商务中的一次网上购物交易 其付款过程至少包括以下几步数据库操作 更新客户所购商品的库存信息保存客户付款信息 可能包括与银行系统的交互生成订单并且保存到数据库中更新用户相关信息 例如购物数量等等 Copyright 2007创智新程 数据的一致性和完整性问题 正常的情况下 这些操作将顺利进行 最终交易成功 与交易相关数据都将被写入数据库中保存下来 但是 如果在这一系列过程中任何一个环节出了差错 都将导致交易失败 一旦交易失败 数据库中所有信息都必须保持交易前的状态不变 否则数据库中的数据就是不一致的或者说是错误的 Copyright 2007创智新程 任务一 确保数据的一致性 提出任务FOX公司最近的业务进行调整 合作伙伴也发生了变化 这一切的变化是由公司的不同部门完成的 但是以前的系统出现了很多数据丢失的情况 实际上就是数据发生了不一致的情况 下面我们来看看发生了什么样的事情 Copyright 2007创智新程 任务一 确保数据的一致性 在采购部门 一个职员做了如下操作 SELECT FROMProductsWHERE ProductID 1 取到的数据是 Copyright 2007创智新程 任务一 确保数据的一致性 采购部现在这个产品的供应商发生了变化 供应商编号现在需要改为 2 应用程序产生的SQL语句如下 UPDATEProductsSETProductName Chai SupplierID 2 CategoryID 1 WHEREProductID 1 Copyright 2007创智新程 任务一 确保数据的一致性 在采购部读取产品编号为 1 的产品数据的同时 仓库管理员决定对这个产品仓库进行调整 所有的这个产品必须放到仓库编号为 2 的仓库里边 此时采购部还没有对这条数据进行更新 所以仓库管理员执行语句 SELECT FROMProductsWHERE ProductID 1 Copyright 2007创智新程 任务一 确保数据的一致性 得到的数据和上面的数据完全相同 所以仓库管理员要更新数据的SQL语句如下两个部门更新完数据后就会发现要么仓库管理员的数据更新成功 采购部门的数据丢失 或者采购部门更新数据成功 仓库管理员的数据丢失 UPDATEProductsSETProductName Chai SupplierID 1 CategoryID 2 WHEREProductID 1 Copyright 2007创智新程 任务一 确保数据的一致性 解题思路步骤1 了解防止数据不一致的基本方式步骤2 编写脚本实现步骤3 执行代码 Copyright 2007创智新程 任务一 确保数据的一致性 任务求解步骤1 了解防止数据不一致的基本方式数据库的不一致性通常是由并发操作引起的 数据的不一致性又包括三类 修改丢失 不可重复读和读 脏 数据 Copyright 2007创智新程 任务一 确保数据的一致性 步骤2 编写脚本实现采购部门的脚本 UPDATEProductsSETProductName Chai SupplierID 2 CategoryID 1 WHEREProductID 1 Copyright 2007创智新程 任务一 确保数据的一致性 步骤2 编写脚本实现仓库管理员的脚本 UPDATEProductsSETProductName Chai SupplierID 1 CategoryID 2 WHEREProductID 1 步骤3 执行代码选择先执行仓库管理员脚本或者采购部门的脚本 Copyright 2007创智新程 事务的基本概念 数据库事务的ACID属性原子性一致性隔离性持久性 Copyright 2007创智新程 任务二 事务的创建 提出任务客户到FOX公司的零售部门购买物品 这些物品可能包含了很多的种类 公司要求零售部门的交易系统要能够以每个客户为一次操作的单位 如果操作过程中出现了任何问题那么这次客户的交易都不能算是成功的操作 Copyright 2007创智新程 任务二 事务的创建 解题思路步骤1 分析任务使用到的数据库表步骤2 分析事件的流程和可能产生数据不一致情况的地方步骤3 学习事务的基本操作步骤4 编写脚本实现步骤5 执行验证 Copyright 2007创智新程 任务二 事务的创建 任务求解步骤1 分析任务使用到的数据库表零售部门与客户进行交易的时候使用的表是Orders表和OrderDetails表 Copyright 2007创智新程 任务二 事务的创建 步骤2 分析事件的流程和可能产生数据不一致情况的地方零售部门使用的管理系统是一个完整的流程 但是如果客户购买商品的信息中部分信息发生了错误 那么整个交易都不能算是成功的 那么在OrderDetails表中产生的别的正确的数据因为包含在这次交易中间那么也是无效的 而且在Orders表中生成的记录同样也是无效的 Copyright 2007创智新程 任务二 事务的创建 步骤3 学习事务的基本操作要想避免数据不一致情况的出现那么我们需要引入事务的机制 启动事务显式事务自动提交事务隐性事务结束事务 Copyright 2007创智新程 任务二 事务的创建 步骤4 编写脚本实现 BEGINTRANSACTIONtrnOrders 往Orders表中新增数据INSERTINTOOrders CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry VALUES VINET 5 2004 1 1 2004 1 2 2004 1 1 1 30 78 VinsetAlcools 59road reims 34566 china Copyright 2007创智新程 任务二 事务的创建 往OrderDetails表中新增数据INSERTINTO ORDERDetails VALUES 11078 11 14 23 0 INSERTINTO ORDERDetails VALUES 11078 42 14 23 0 IF ERROR0CommitTRANSACTIONtrnOrdersPRINT 事务成功提交 ELSEROLLBACKTRANSACTIONtrnOrdersPRINT 事务提交失败 数据回滚 ENDTRANSACTION Copyright 2007创智新程 任务二 事务的创建 步骤5 执行验证按F5执行此语句 如果返回信息提示事务成功 那么新增的数据已经永久保存到数据库中去了 Copyright 2007创智新程 任务三 撤销事务 提出任务客户交易数据时不正确的数据进行提交的时候事务操作需要阻止同一个事务操作内其他数据对数据库的更改 Copyright 2007创智新程 任务三 撤销事务 解题思路步骤1 了解事务操主的回滚步骤2 编写事务操主代码步骤3 执行操作 Copyright 2007创智新程 任务三 撤销事务 任务求解步骤1 了解事务操主的回滚如果事务中出现错误 或者用户决定取消事务 可回滚该事务 语法ROLLBACK TRAN SACTION transaction name tran name variable savepoint name savepoint variable Copyright 2007创智新程 任务三 撤销事务 步骤2 编写事务操主代码 BEGINTRANSACTIONtrnOrders 往Orders表中新增数据INSERTINTOOrders CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry VALUES VINET 5 2004 1 1 2004 1 2 2004 1 1 1 30 78 VinsetAlcools 59road reims 34566 china Copyright 2007创智新程 任务三 撤销事务 往OrderDetails表中新增数据INSERTINTO ORDERDetails VALUES 11078 11 14 23 0 INSERTINTO ORDERDetails VALUES 11078 9999 4 23 0 IF ERROR0CommitTRANSACTIONtrnOrdersPRINT 事务成功提交 ELSEROLLBACKTRANSACTIONtrnOrdersPRINT 事务提交失败 数据回滚 ENDTRANSACTION Copyright 2007创智新程 任务三 撤销事务 步骤3 执行操作按F5执行 Copyright 2007创智新程 游标 游标 Cursor 是一个用来保存 数据集 的容器 可以对其中的数据以记录为单位 利用循环技术 一笔 一笔地处理 游标 Cursor 主要用于SQL批 存储过程和触发器中 Copyright 2007创智新程 任务四 使用游标获取数据 提出任务公司的业务部门需要一份关于目前公司经营的一些经营产品和存储产品的仓库信息的详细报告 Copyright 2007创智新程 任务四 使用游标获取数据 解题思路步骤1 确认报表需要的数据来源步骤2 确认需要使用的技术步骤3 编写产生报告的语句步骤4 执行并取得结果 Copyright 2007创智新程 任务四 使用游标获取数据 任务求解步骤1 确认报表需要的数据来源从FOX公司的产品数据库分析可以知道 业务部门需要的数据分别存放于Categories和Products表 Copyright 2007创智新程 任务四 使用游标获取数据 步骤2 确认需要使用的技术声明语法DECLAREcursor name INSENSITIVE SCROLL CURSORFORselect statement FOR READONLY UPDATE OFcolumn name n 打开语法opencursor name关闭语法CLOSEcursor name Copyright 2007创智新程 任务四 使用游标获取数据 删除语法DEALLOCATEcursor name获取语法FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM GLOBAL cursor name cursor variable name INTO variable name n Copyright 2007创智新程 任务四 使用游标获取数据 步骤3 编写产生报告的语句declare CategoryNamenvarchar 15 declare ProductNamenvarchar 40 declarecurCategorycursorforselectCategories CategoryName Products ProductNameFromCategories ProductswhereProducts CategoryID Categories CategoryID 声明两个变量保存执行后的结果 定义游标 Copyright 2007创智新程 任务四 使用游标获取数据 opencurCategoryfetchcurCategoryinto CategoryName ProductNamewhile fetch status 0 beginprint CategoryNameis CategoryNameprint ProductNameis ProductName 取得下一条游标里的数据fetchcurCategoryinto CategoryNam