网络数据库第十章存储过程与触发器.ppt

第10章存储过程与触发器 本章内容 10 1存储过程概述10 2存储过程的创建与使用10 3触发器概述10 4触发器的创建与使用10 5事务处理10 6SQLServer的锁机制 10 1存储过程概述 存储过程是SQLServer服务器上一组预编译的Transact SQL语句 用于完成某项任务 它可以接受参数 返回状态值和参数值 并且可以嵌套调用 10 1存储过程概述 SQLServer存储过程的类型包括 系统存储过程用户定义存储过程临时存储过程扩展存储过程 1 存储过程的类型 10 1存储过程概述 1 系统存储过程 是指由系统提供的存储过程 主要存储在master数据库中并以sp 为前缀 它从系统表中获取信息 从而为系统管理员管理SQLServer提供支持 通过系统存储过程 SQLServer中的许多管理性或信息性的活动 例如使用sp depends sp helptexts可以了解数据数据库对象 数据库信息 都可以顺利有效地完成 尽管系统存储过程被放在master数据库中 仍可以在其他数据库中对其进行调用 调用时 不必在存储过程名前加上数据库名 当创建一个新数据库时 一些系统存储过程会在新数据库中被自动创建 10 1存储过程概述 2 用户定义存储过程 是由用户创建并能完成某一特定功能 例如查询用户所需数据信息 的存储过程 它处于用户创建的数据库中 存储过程名前没有前缀sp 10 1存储过程概述 3 临时存储过程 临时存储过程与临时表类似 分为局部临时存储过程和全局临时存储过程 且可以分别向该过程名称前面添加 或 前缀表示 表示本地临时存储过程 表示全局临时存储过程 使用临时存储过程必须创建本地连接 当SQLServer关闭后 这些临时存储过程将自动被删除 由于SQLServer支持重新使用执行计划 所以连接到SQLServer2000的应用程序应使用sp executesql系统存储过程 而不使用临时存储过程 10 1存储过程概述 4 扩展存储过程 扩展存储过程是SQLServer可以动态装载和执行的动态链接库 DLL 当扩展存储过程加载到SQLServer中 它的使用方法与系统存储过程一样 扩展存储过程只能添加到master数据库中 其前缀是xp 10 1存储过程概述 2 存储过程的功能特点 SQLServer的存储过程可实现以下功能 1 接收输入参数并以输出参数的形式为调用过程或批处理返回多个值 2 包含执行数据库操作的编程语句 包括调用其他过程 3 为调用过程或批处理返回一个状态值 以表示成功或失败 及失败原因 10 1存储过程概述 存储过程具有以下优点 1 模块化编程 2 快速执行 3 减少网络通信量 4 提供安全机制 5 保证操作一致性 10 2 1创建存储过程10 2 2执行存储过程10 2 3修改存储过程10 2 4删除存储过程10 2 5存储过程参数与状态值 10 2存储过程的创建与使用 10 2 1创建存储过程 1 使用企业管理器创建存储过程 1 启动企业管理器 登录到要使用的服务器 2 选择要创建存储过程的数据库 在左窗格中单击 存储过程 文件夹 10 2存储过程的创建与使用 3 右击 存储过程 文件夹 在弹出菜单中选择 新建存储过程 选项 此时打开创建存储过程对话框 10 2 1创建存储过程 4 在 文本 编辑框中输入存储过程正文 5 单击 检查语法 按钮 检查语法是否正确 6 单击 确定 按钮 保存存储过程 7 在图10 1的右窗格中 右击该存储过程 在弹出菜单中选择 所有任务 选择 管理权限 在 对象属性 对话框中设置权限 如设置PUBLIC用户有EXEC权限 10 2 1创建存储过程 2 使用向导创建存储过程 1 在企业管理器中选择当前服务器 然后执行 工具 向导 菜单命令 弹出 选择向导 对话框 2 在 选择向导 对话框中展开 数据库 项 双击 创建存储过程向导 项 弹出 创建存储过程 对话框 10 2 1创建存储过程 3 单击 下一步 按钮 进入 选择数据库 对话框 10 2 1创建存储过程 4 单击 下一步 按钮 进入 选择存储过程 对话框 在该对话框中 设置该存储过程中的表要执行的操作 可同时设置一个或多个操作 用于插入 删除和更新 如果选择多个操作 每个操作都将创建一个存储过程 这里为customer表创建插入存储过程 10 2 1创建存储过程 5 单击 下一步 按钮 进入 完成存储过程创建 对话框 在该对话框中显示将要创建的存储过程的名称及其操作 这里的存储过程名称是insert customer 1 10 2 1创建存储过程 6 在图10 7中选择一个存储过程 单击 编辑 按钮 进入 编辑存储过程属性 对话框 在此可以重新设置存储过程的名称和要操作的字段 10 2 1创建存储过程 7 如果要通过SQL语句对正在创建的存储过程进行修改 单击 编辑SQL 按钮 弹出 编辑存储过程SQL 对话框 8 SQL语句编辑完成后 单击 分析 按钮 将对存储过程进行分析 分析成功后 单击 确定 按钮 返回图10 7 完成存储过程 对话框 再单击 完成 按钮 开始创建存储过程 并弹出创建成功对话框 10 2 1创建存储过程 3 使用CREATEPROCEDURE语句创建存储过程 使用CREATEPROCEDURE语句创建存储过程应该考虑以下几个方面 1 在一个批处理中 CREATEPROCEDURE语句不能与其他SQL语句合并在一起 2 数据库所有者具有默认的创建存储过程的权限 它可把该权限传递给其他的用户 3 存储过程作为数据库对象其命名必须符合标识符的命名规则 4 只能在当前数据库中创建属于当前数据库的存储过程 10 2 1创建存储过程 创建存储过程语句的语法格式如下 CREATEPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 10 2 1创建存储过程 例10 1创建存储过程 从表goods和表goods classification的联接中返回商品名 商品类别 单价 10 2 1创建存储过程 CREATEPROCEDUREgoods infoASSELECTgoods name classification name unit priceFROMgoodsgINNERJOINgoods classificationgcONg classification id gc classification id存储过程创建后 存储过程的名称存放在sysobject表中 文本存放在syscomments表中 10 2 2执行存储过程 执行存储过程的语法格式 EXEC UTE return status procedure name number procedure name var parameter value variable OUTPUT DEFAULT n WITHRECOMPILE 10 2存储过程的创建与使用 例如 执行例10 1的存储过程goods info 在SQL查询分析器中输入命令 EXECgoods info运行的结果 10 2 2执行存储过程 10 2存储过程的创建与使用 10 2 3修改存储过程 修改存储过程可以通过企业管理器和TransactSQL语句实现 1 使用企业管理器修改存储过程 1 在企业管理器中展开服务器组 再展开服务器 2 展开 数据库 文件夹 再展开要修改存储过程的数据库 3 在要修改的存储过程上右击 并在弹出的快捷菜单中选择 属性 项 或者双击要修改的存储过程 弹出 存储过程属性 窗口 10 2 3修改存储过程 4 在 文本 框中直接对其代码进行修改 修改完成后 先检查语法 正确后单击 确定 按钮 10 2 3修改存储过程 2 使用ALTERPROCEDURE语句修改存储过程 ALTERPROCEDURE的语法规则是 ALTERPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 10 2 3修改存储过程 例10 2使用ALTERPROCEDURE语句更改存储过程 1 创建存储过程employee dep 以获取总经理办的男员工 CREATEPROCEDUREemployee depASSELECTemployee name sex address department nameFROMemployeeeINNERJOINdepartmentdONe department id d department idWHEREsex 男 ANDe department id D001 GO执行存储过程employee dep 结果如图 10 2 3修改存储过程 2 查看employee dep存储过程的文本信息 SELECTo id c textFROMsysobjectsoINNERJOINsyscommentscONo id c idWHEREo type P ANDo name employee dep GO 10 2 3修改存储过程 3 使用ALTERPROCEDURE语句对employee dep过程进行修改 使其能够显示出所有男员工 并使employee dep过程以加密方式存储在表syscomments中 ALTERPROCEDUREemployee depWITHENCRYPTIONASSELECTemployee name sex address department nameFROMemployeeeINNERJOINdepartmentdONe department id d department idWHEREsex 男 GO 10 2 3修改存储过程 执行修改后的存储过程employee dep 结果如图 10 2 3修改存储过程 4 从系统表sysobjects和syscomments提取修改后的存储过程employee dep的文本信息可以运行步骤 2 中的代码 结果如图 这是由于在ALTERPROCEDURE语句中使用WITHENCRYPTION关键字对存储过程employee dep的文本进行了加密 其文本信息显示为乱码 10 2存储过程的创建与使用 10 2 4删除存储过程 存储过程可以被快速删除和重建 因为它没有存储数据 1 使用企业管理器删除存储过程 1 在企业管理器中展开服务器组 再展开相应的服务器 2 展开 数据库 文件夹 再展开要删除存储过程的数据库 10 2 4删除存储过程 3 单击 存储过程 项 在右窗格中右击要删除的存储过程 选择的同时按下Ctrl键可以同时选择多个存储过程 在弹击的快捷菜单中选择 删除 项 弹出 除去对象 对话框 4 在 除去对象 对话框中单击 显示相关性 按钮 可以显示依附该存储过程的对象 在确定不影响其他对象后单击 全部除去 按钮 删除所有选择的存储过程 10 2 4删除存储过程 2 使用DROPPROCEDURE删除存储过程 DROPPROCEDURE的语法如下 DROPPROCEDURE procedure name n 例如删除例10 2创建的存储过程employee dep DROPPROCEDUREemployee depGO 10 2存储过程的创建与使用 10 2 5存储过程参数与状态值 存储过程和调用者之间通过参数交换数据 可以按输入的参数执行 也可由参数输出执行结果 调用者通过存储过程返回的状态值对存储过程进行管理 1 参数存储过程的参数在创建过程时声明 SQLServer支持两类参数 输入参数和输出参数 10 2 5存储过程参数与状态值 1 输入参数 输入参数允许调用程序为存储过程传送数据值 要定义存储过程的输入参数 必须在CREATEPROCEDURE语句中声明一个或多个变量及类型 10 2 5存储过程参数与状态值 例10 3创建带参数的存储过程 从表employee sell order goods goods classification的连接中返回输入的员工名 该员工销售的商品名 商品类别 销售量等信息 CREATEPROCsell info employee namevarchar 20 ASSELECTemployee name goods name classification name order numFROMemployeeeINNERJOINsell ordersONe employee id s employee idJOINgoodsgONg goods id s goods idJOINgoods classificationgcONgc classification id g classification idWHEREemployee nameLIKE employee name 10 2 5存储过程参数与状态值 存储过程sell info以 employee name变量作为输入参数 执行时 可以省略参数名 直接给参数值 在SQL查询分析器中输入命令 EXECsell info 东方牧 运行结果如图 参数值可以包含通配符 例如 查找所有姓 钱 的员工的销售情况可以使用以下命令 EXECsell info 钱 10 2 5存储过程参数与状态值 执行时 参数可以由位置标识 也可以由名字标识 例如 定义一个具有3个参数的存储过程 CREATEPROCmyproc val1int val2int val3intAS 参数以位置传递 EXECmyproc10 20 15参数以名字传递 每个值由对应的参数名引导 EXECmyproc val2 20 val1 10 val3 15按名字传递参数比按位置传递参数具有更大的灵活性 但是 按位置传递参数却具有更快的速度 10 2 5存储过程参数与状态值 2 输出参数 输出参数允许存储过程将数据值或游标变量传回调用程序 OUTPUT关键字用以指出能返回到调用它的批处理或过程中的参数 为了使用输出参数 在CREATEPROCEDURE和EXECUTE语句中都必须使用OUTPUT关键字 10 2 5存储过程参数与状态值 例10 4创建存储过程price goods 通过输入参数在goods表中查找商品 以输出参数获取商品单价 CREATEPROCprice goods goods namevarchar 80 NULL price goodsrealOUTPUTASSELECT price goods unit priceFROMgoodsWHEREgoods name goods name 10 2 5存储过程参数与状态值 执行price goods存储过程的代码如下 DECLARE pricerealEXECprice goods CanonLBP2900 priceOUTPUTSELECT price运行结果是商品名为 CanonLBP2900 的商品单价 1380 0 EXECUTE语句还需要关键字OUTPUT以允许参数值返回给变量 10 2 5存储过程参数与状态值 1 用RETURN语句定义返回值存储过程可以返回整型状态值 表示过程是否成功执行 或者过程失败的原因 如果存储过程没有显式设置返回代码的值 则SQLServer返回代码为0 表示成功执行 若返回 1 99之间的整数 表示没有成功执行 也可以使用RETURN语句 用大于0或小于 99的整数来定义自己的返回状态值 以表示不同的执行结果 2 返回存储过程的状态 10 2 5存储过程参数与状态值 例10 5创建存储过程 输入商品类别 返回各种商品名称 在存储过程中 用值15表示用户没有提供参数 值 l01表示没有输入商品类别 值0表示过程运行没有出错 CREATEPROCcl goods cl namevarchar 40 NULLASIF cl name NULLRETURN15IFNOTEXISTS SELECT FROMgoods classificationWHEREclassification name cl name RETURN 101SELECTg goods nameFROMgoods classificationgc goodsgWHEREgc classification id g classification idANDgc classification name cl nameRETURN0 10 2 5存储过程参数与状态值 在执行过程时 要正确接收返回的状态值 必须使用以下语句 EXECUTE status var procedure name 2 捕获返回状态值 10 2 5存储过程参数与状态值 DECLARE return statusintEXEC return status cl goods 笔记本计算机 IF return status 15SELECT 语法错误 ELSEIF return status 101SELECT 没有找到该商品类别 执行时 将对不同的输入值返回不同的状态值及处理结果 例10 5的存储过程cl goods执行时使用以下语句 触发器是一种特殊类型的存储过程 触发器主要是通过事件进行触发而被执行的 而存储过程可以通过过程名字直接调用 当对某一表进行UPDATE INSERT DELETE操作时 SQLServer就会自动执行触发器所定义的SQL语句 从而确保对数据的处理必须符合由这些SQL语句所定义的规则 触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性 10 3触发器概述 10 4 1创建触发器10 4 2删除触发器10 4 3修改触发器 10 4触发器的创建与使用 10 4触发器的创建与使用 1 使用企业管理器创建触发器 1 启动企业管理器 登录到指定的服务器上 2 展开数据库 选择要在其上创建触发器的表所在的数据库 单击该表 如customer 10 4 1创建触发器 10 4 1创建触发器 3 在选择的数据表上右击鼠标 在弹出菜单上选择 所有任务 选项 单击 管理触发器 弹出 触发器属性 对话框 10 4 1创建触发器 4 在 触发器属性 对话框的名称列表框中选择 新建 项 在文本框中输入触发器文本 单击 检查语法 检查语句是否正确 5 在 触发器属性 对话框中单击 应用 按钮 在名称下拉列表中会有新创建的触发器名字 单击 确定 按钮 关闭窗口 触发器创建成功 10 4 1创建触发器 2 使用CREATETRIGGER语句创建触发器 CREATETRIGGER语句的语法格式如下 CREATETRIGGERtrigger nameON table name view WITHENCRYPTION FOR AFTER INSTEADOF INSERT UPDATE DELETE ASsql statement n 10 4 1创建触发器 例10 6在employee表上创建一个DELETE类型的触发器 该触发器的名称为tr employee 1 创建触发器tr employeeCREATETRIGGERtr employeeONemployeeFORDELETEASDECLARE msgvarchar 50 SELECT msg STR ROWCOUNT 个员工被删除 SELECT msgRETURN 10 4 1创建触发器 2 执行触发器tr employee 触发器不能通过名字来执行 而是在相应的SQL语句被执行时自动触发的 例如执行以下DELETE语句 DELETEFROMemployeeWHEREemployee name 张三 该语句要删除员工姓名为 张三 记录 由此激活了表employee的DELETE类型的触发器tr employee 系统执行tr employee触发器中AS之后的语句 并显示以下信息 1个员工被删除 10 4 1创建触发器 在触发器的执行过程中 SQLServer建立和管理两个临时的虚拟表 Deleted表和Inserted表 这两个表包含了在激发触发器的操作中插入或删除的所有记录 在执行INSERT或UPDATE语句之后所有被添加或被更新的记录都会存储在Inserted表中 在执行DELETE或UPDATE语句时 从触发程序表中被删除的行会发送到Deleted表 对于更新操作 SQLServer先将要进行修改的记录存储到Deleted表中 然后再将修改后的数据复制到Inserted表以及触发程序表 3 Deleted表和Inserted表 10 4 1创建触发器 例10 7为表customer创建一个名为test tr的触发器 当执行添加 更新或删除时 激活该触发器 创建test tr触发器 CREATETRIGGERtest trONcustomerFORINSERT UPDATE DELETEASSELECT FROMinsertedSELECT FROMdeletedcustomer表执行以下插入操作 INSERTINTOcustomer customer id customer name telphone VALUES 12346 张三 1234567 10 4 1创建触发器 INSERT操作激活触发器test tr 输出如图10 19所示的表格 10 4触发器的创建与使用 10 4 2删除触发器 1 使用企业管理器删除触发器 1 启动企业管理器 登录到指定的服务器 2 选择数据库和表 其操作过程与创建触发器相似 参见图10 17 10 4 2删除触发器 3 从快捷菜单项中选择 所有任务 管理触发器 选项 打开 触发器属性 对话框 4 在 触发器属性 对话框的 名称 列表中选择要删除的触发器名称 再选择 删除 按钮完成删除操作 10 4 2删除触发器 2 使用DROPTRIGGER删除指定触发器 删除触发器语句的语法格式如下 DROPTRIGGERtrigger name n 例如 删除例10 6的触发器tr employee DROPTRIGGERtr employee 10 4 3修改触发器 通过企业管理器 系统存储过程或Transact SQL语句 可以修改触发器的名字和正文 1 使用sp rename系统存储过程修改触发器的名字 sp renameoldname newname 10 4触发器的创建与使用 10 4 3修改触发器 2 使用企业管理器修改触发器的正文 操作步骤如下 1 启动企业管理器 登录到指定的服务器 2 选择数据库和表 其操作过程与创建触发器相似 参见图10 17 3 从快捷菜单项中选择 所有任务 管理触发器 选项 打开 触发器属性 对话框 参见图10 20 4 在 触发器属性 对话框的 名称 列表中选择要修改的触发器名 在 文本 编辑框中可以修改选择的触发器正文 再使用 检查语法 选项对语法进行检查 10 4 3修改触发器 3 使用ALTERTRIGGER语句修改触发器 修改触发器的语法如下 ALTERTRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE ASsql statement n 10 4 3修改触发器 ALTERTRIGGERtr employeeONemployeeFORINSERTASDECLARE msgvarchar 50 SELECT msg STR ROWCOUNT 个员工数据被插入 SELECT msgRETURN对employee表执行以下插入语句 INSERTemployee employee id employee name VALUES E016 王五 激活INSERT触发器tr employee 显示信息 1个员工数据被插入 例如 将例10 6的触发器tr employee修改为INSERT操作后进行 10 5 1事务概述10 5 2事务管理 10 5事务处理 10 5事务处理 事务 Transaction 是SQLServer中的一个逻辑工作单元 该单元将被作为一个整体进行处理 事务保证连续多个操作必须全部执行成功 否则必须立即回复到未执行任何操作的状态 即执行事务的结果要不全部将数据所要执行的操作完成 要不全部数据都不修改 10 5 1事务概述 10 5 1事务概述 例如 企业取消了仓储部 需要将 仓储部 从department表中删除 而employee表中的部门编号与仓储部相对应的员工也应删除 假设仓储部编号为D004 第一条DELETE语句修改department表为 DELETEFROMdepartmentWHEREdepartment id D004 第二条DELETE语句修改employee表为 DELETEFROMemployeeWHEREdepartment id D004 因此 必须保证这两条DELETE语句同时执行 或都不执行 这时可以使用数据库中的事务 Transaction 技术来实现 1 事务的由来 2 事务属性 由于事务作为一个逻辑工作单元 当事务执行遇到错误时 将取消事务所做的修改 一个逻辑单元必须具有4个属性 原子性 Atomicity 一致性 Consistency 隔离性 Isolation 持久性 Durability 这些属性称为ACID 10 5 1事务概述 3 事务模式 SQLServer以3种事务模式管理事务 1 自动提交事务模式 每条单独的语句都是一个事务 在此模式下 每条Transact SQL语句在成功执行完成后 都被自动提交 如果遇到错误 则自动回滚该语句 该模式为系统默认的事务管理模式 2 显式事务模式 该模式允许用户定义事务的启动和结束 事务以BEGINTRANSACTION语句显式开始 以COMMIT或ROLLBACK语句显式结束 3 隐性事务模式 在当前事务完成提交或回滚后 新事务自动启动 隐性事务不需要使用BEGINTRANSACTION语句标识事务的开始 但需要以COMMIT或ROLLBACK语句来提交或回滚事务 10 5 1事务概述 10 5事务处理 1 启动和结束事务启动事务语句的语法格式如下 BEGINTRAN SACTION transaction name tran name variable WITHMARK description 结束事务语句的语法格式如下 COMMIT TRAN SACTION transaction name tran name variable 10 5 2事务管理 10 5 2事务管理 例10 8建立一个显式事务以显示Sales数据库的employee表的数据 BEGINTRANSACTIONSELECT FROMemployeeCOMMITTRANSACTION本例创建的事务以BEGINTRANSACTION语句开始 以COMMITTRANSACTION语句结束 10 5 2事务管理 DECLARE transaction namevarchar 32 SELECT transaction name tran delete BEGINTRANSACTION transaction nameDELETEFROMdepartmentWHEREdepartment id D004 DELETEFROMemployeeWHEREdepartment id D004 COMMITTRANSACTIONtran delete 例10 9建立一个显式命名事务以删除department表的 仓储部 记录行 10 5 2事务管理 CREATETABLEimp tran numchar 2 NOTNULL cnamechar 6 NOTNULL GOSETIMPLICIT TRANSACTIONSON 启动隐性事务模式GO 第一个事务由INSERT语句启动INSERTINTOimp tranVALUES 01 Zhang INSERTINTOimp tranVALUES 02 Wang COMMITTRANSACTION 提交第一个隐性事务GO 第二个隐式事务由SELECT语句启动SELECTCOUNT FROMimp tranINSERTINTOimp tranVALUES 03 Li SELECT FROMimp tranCOMMITTRANSACTION 提交第二个隐性事务GOSETIMPLICIT TRANSACTIONSOFF 关闭隐性事务模式GO 例10 10隐性事务处理过程 10 5 2事务管理 当事务事务回滚使用ROLLBACKTRANSACTION语句实现 其语法格式如下 ROLLBACK TRAN SACTION transaction name tran name variable savepoint name savepoint variable 2 事务回滚 10 5 2事务管理 事务回滚到指定位置 如果要让事务回滚到指定位置 则需要在事务中设定保存点 SavePoint 所谓保存点是指定其所在位置之前的事务语句 不能回滚的语句即此语句前面的操作被视为有效 其语法格式如下 SAVETRAN SACTION savepoint name savepoint variable 10 5 2事务管理 例10 11使用ROLLBACKTRANSACTION语句标识事务结束 BEGINTRANSACTIONUPDATEgoodsSETstock quantity stock quantity 5WHEREgoods id G00006 INSERTINTOsell order order id1 goods id order num order date VALUES S00005 G00006 5 getdate ROLLBACKTRANSACTION 10 5 2事务管理 BEGINTRANSACTIONmy transaction deleteDELETEFROMdepartmentWHEREdepartment id D005 SAVETRANSACTIONafter delete 设置保存点UPDATEemployeeSETdepartment id D001 WHEREdepartment id D005 IF error 0OR rowcount 0 BEGINROLLBACKTRANSACTIONafter delete 如果出错回滚到保存点after deleteCOMMITTRANSACTIONmy transaction deleteENDELSECOMMITTRANSACTIONmy transaction deleteGO 例10 12删除仓储部 再将仓储部的职工划分到总经理办 10 5 2事务管理 CREATETRIGGERtrig uptabONgoodsFORUPDATEASSAVETRANSACTIONtran uptabINSERTINTOnewgoodsSELECT FROMinsertedIF error0 BEGINROLLBACKTRANSACTIONtran uptabEND 例10 13定义为表goods触发器trig uptab 如果goods表更新数据 则把新数据复制到表newgoods中 若出错 则取消复制操作 10 5 2事务管理 和BEGIN END语句类似 BEGINTRANSACTION和COMMITTRANSACTION语句也可以进行嵌套 即事务可以嵌套执行 3 事务嵌套 10 5 2事务管理 CREATETABLEemployee tran numchar 2 NOTNULL cnamechar 6 NOTNULL GOBEGINTRANSACTIONTran1 TRANCOUNT为1INSERTINTOemployee tranVALUES 01 Zhang BEGINTRANSACTIONTran2 TRANCOUNT为2INSERTINTOemployee tranVALUES 02 Wang BEGINTRANSACTIONTran3 TRANCOUNT为3PRINT TRANCOUNTINSERTINTOemployee tranVALUES 03 Li COMMITTRANSACTIONTran3 TRANCOUNT为2PRINT TRANCOUNTCOMMITTRANSACTIONTran2 TRANCOUNT为1PRINT TRANCOUNTCOMMITTRANSACTIONTran1 TRANCOUNT为0PRINT TRANCOUNT 例10 14提交事务 运行结果 3210 10 6 1锁模式10 6 2隔离级别10 6 3查看和终止锁10 6 4死锁及其防止 10 6SQLServer的锁机制 10 6SQLServer的锁机制 锁 Lock 作为一种安全机制 用于控制多个用户的并发操作 以防止用户读取下在由其他用户更改的数据或者多个用户同时修改同一数据 从而确保事务完整性和数据库一致性 10 6 1锁模式 10 6 1锁模式 当对一个数据源加锁后 此数据源就有了一定的访问限制 称对此数据源进行了 锁定 SQLServer有多种粒度锁 允许一个事务锁定不同类型的资源 数据行 Row 数据页中的单行数据 索引行 Key 索引页中的单行数据 即索引的键值 页 Page 页是SQLServer存取数据的基本单位 其大小为8KB 扩展盘区 Extent 一个盘区由8个连续的页组成 表 Table 数据库 Database 允许一个事务锁定的资源类型 10 6 1锁模式 确定并发事务访问资源方式的锁模式 1 共享锁 SharedLock 2 排它锁 ExclusiveLock 3 更新锁 UpdateLock 从程序员的角度 锁可以分为以下两种类型 1 乐观锁 OptimisticLock 乐观锁假定在处理数据时 不需要在应用程序的代码中做任何事情就可以直接在记录上加锁 即完全依靠数据库来管理锁的工作 一般情况下 当执行事务处理时 SQLServer会自动对事务处理范围内更新到的表做锁定 2 悲观锁 PessimisticLock 悲观锁需要程序员直接管理数据或对象上的加锁处理 并负责获取 共享和放弃正在使用的数据上的任何锁 10 6 1锁模式 10 6SQLServer的锁机制 隔离 Isolation 是计算机安全技术中的概念 其本质上是一种封锁机制 它是指自动数据处理系统中的用户和资源的相关牵制关系 也就是用户和进程彼此分开 且和操作系统的保护控制也分开来 事务准备接受不一致数据的级别称为隔离级别 IsolationLevel 10 6 2隔离级别 10 6 2隔离级别 较低的隔离级别可以增加并发 但代价是降低数据的正确性 相反 较高的隔离级别可以确保数据的正确性 但可能对并发产生负面影响 应用程序要求的隔离级别确定了SQLServer使用的锁定行为 隔离级别是一个事务必须与其他事务进行隔离的程度 10 6 2隔离级别 1 提交读 READCOMMITTED 它是SQLServer的默认级别 在此隔离级别下 SELECT语句不会也不能返回尚未提交 Committed 的数据 即脏数据 2 未提交读 READUNCOMMITTED 与提交读隔离级别相反 它允许读取脏数据 即已经被其他用户修改但尚未提交的数据