第 存储过程与触发器.pptx

《第 存储过程与触发器.pptx》由会员分享，可在线阅读，更多相关《第 存储过程与触发器.pptx（87页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、10.1 存储过程概述 存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。第1页/共87页10.1 10.1 存储过程概述存储过程概述SQL Server存储过程的类型包括：系统存储过程用户定义存储过程临时存储过程扩展存储过程。1.存储过程的类型第2页/共87页10.1 10.1 存储过程概述存储过程概述(1)系统存储过程 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，它从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。通过系统存储过程，SQL

2、Server中的许多管理性或信息性的活动(例如使用sp_depends、sp_helptexts可以了解数据数据库对象、数据库信息)都可以顺利有效地完成。尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。第3页/共87页10.1 10.1 存储过程概述存储过程概述(2)用户定义存储过程是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。第4页/共87页10.1 10.1 存储过程概述存储过

3、程概述(3)临时存储过程临时存储过程与临时表类似，分为局部临时存储过程和全局临时存储过程，且可以分别向该过程名称前面添加“#”或“#”前缀表示。“#”表示本地临时存储过程，“#”表示全局临时存储过程。使用临时存储过程必须创建本地连接，当SQL Server关闭后，这些临时存储过程将自动被删除。由于SQL Server支持重新使用执行计划，所以连接到SQL Server 2000的应用程序应使用sp_executesql系统存储过程，而不使用临时存储过程。第5页/共87页10.1 10.1 存储过程概述存储过程概述(4)扩展存储过程扩展存储过程是SQL Server可以动态装载和执行的动态链接库

4、(DLL)。当扩展存储过程加载到SQL Server中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。第6页/共87页10.1 10.1 存储过程概述存储过程概述2.存储过程的功能特点SQL Server的存储过程可实现以下功能：(1)接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。(2)包含执行数据库操作的编程语句，包括调用其他过程。(3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。第7页/共87页10.1 10.1 存储过程概述存储过程概述存储过程具有以下优点(1)模块化编程。(2)快速执行。(3)减少网络通信

5、量。(4)提供安全机制。(5)保证操作一致性。第8页/共87页10.2.1 创建存储过程10.2.2 执行存储过程10.2.3 修改存储过程10.2.4 删除存储过程10.2.5 存储过程参数与状态值10.2 存储过程的创建与使用第9页/共87页10.2.1 创建存储过程1使用SQL Server管理平台创建存储过程（1）打开SQL Server管理平台，展开节点“对象资源管理器”“数据库服务器”“可编程性”“存储过程”，在窗口的右侧显示出当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令。10.2 10.2 存储过程的创建与使用存储过程的创建与使用第10页/共

6、87页（2）在打开的SQL命令窗口中，系统给出了创建存储过程命令的模板，如图10-2所示。在模板中可以输入创建存储过程的Transact-SQL语句后，单击“执行”按钮即可创建存储过程。10.2.1 10.2.1 创建存储过程创建存储过程第11页/共87页（3）建立存储过程的命令被成功执行后，在“对象资源管理器”“数据库服务器”“可编程性”“存储过程”中可以看到新建立的存储过程 10.2.1 10.2.1 创建存储过程创建存储过程第12页/共87页2.使用CREATE PROCEDURE语句创建存储过程使用CREATE PROCEDURE语句创建存储过程应该考虑以下几个方面：(1)在一个批处理

7、中，CREATE PROCEDURE语句不能与其他SQL语句合并在一起。(2)数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其他的用户。(3)存储过程作为数据库对象其命名必须符合标识符的命名规则。(4)只能在当前数据库中创建属于当前数据库的存储过程。10.2.1 10.2.1 创建存储过程创建存储过程第13页/共87页创建存储过程语句的语法格式如下：CREATE PROCEDURE procedure_name;number parameter data_type VARYING=default OUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMP

8、ILE,ENCRYPTION FOR REPLICATION AS sql_statement,.n 10.2.1 10.2.1 创建存储过程创建存储过程第14页/共87页例10-1 创建存储过程，从表goods和表goods_classification的联接中返回商品名、商品类别、单价。10.2.1 10.2.1 创建存储过程创建存储过程CREATE PROCEDURE goods_info ASSELECT goods_name,classification_name,unit_priceFROM goods g INNER JOIN goods_classification gcON

9、g.classification_id=gc.classification_id存储过程创建后，存储过程的名称存放在sysobject表中，文本存放在syscomments表中。第15页/共87页10.2.2 执行存储过程执行存储过程的语法格式：EXECUTE return_status=procedure_name;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2 10.2 存储过程的创建与使用存储过程的创建与使用第16页/共87页例如，执行例10-1的存储过程g

10、oods_info在SQL查询分析器中输入命令：EXEC goods_info运行的结果：10.2.2 10.2.2 执行存储过程执行存储过程第17页/共87页10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.3 修改存储过程修改存储过程可以通过SQL Server管理平台和Transact-SQL语句实现。1使用SQL Server管理平台修改存储过程 第18页/共87页10.2.3 10.2.3 修改存储过程修改存储过程2.使用ALTER PROCEDURE语句修改存储过程ALTER PROCEDURE的语法规则是：ALTER PROCEDURE procedure_n

11、ame ;number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATION AS sql_statement .n 第19页/共87页10.2.3 10.2.3 修改存储过程修改存储过程例10-2 使用ALTER PROCEDURE语句更改存储过程。(1)创建存储过程employee_dep，以获取总经理办的男员工。CREATE PROCEDURE employee_dep AS SELECT employee_name,sex,a

12、ddress,department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男 AND e.department_id=D001GO执行存储过程employee_dep，结果如图 第20页/共87页10.2.3 10.2.3 修改存储过程修改存储过程(2)查看employee_dep存储过程的文本信息SELECT o.id,c.textFROM sysobjects o INNER JOIN syscomments c ON o.id=c.idWHERE o

13、.type=P AND o.name=employee_depGO第21页/共87页10.2.3 10.2.3 修改存储过程修改存储过程(3)使用ALTER PROCEDURE语句对employee_dep过程进行修改，使其能够显示出所有男员工，并使employee_dep过程以加密方式存储在表syscomments中ALTER PROCEDURE employee_depWITH ENCRYPTION AS SELECT employee_name,sex,address,department_nameFROM employee e INNER JOIN department d ON e.

14、department_id=d.department_id WHERE sex=男 GO第22页/共87页10.2.3 10.2.3 修改存储过程修改存储过程执行修改后的存储过程employee_dep，结果如图：第23页/共87页10.2.3 10.2.3 修改存储过程修改存储过程(4)从系统表sysobjects和syscomments提取修改后的存储过程employee_dep的文本信息可以运行步骤（2）中的代码，结果如图 这是由于在ALTER PROCEDURE语句中使用WITH ENCRYPTION关键字对存储过程employee_dep的文本进行了加密，其文本信息显示为NULL。第

15、24页/共87页10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.4 删除存储过程 存储过程可以被快速删除和重建，因为它没有存储数据。1使用SQL Server管理平台删除存储过程（1）打开SQL Server管理平台，展开节点“对象资源管理器”“数据库服务器”“可编程性”“存储过程”，选择要删除的存储过程，单击鼠标右键，在弹出的快捷菜单中选择“删除”命令。（2）在弹出的“删除对象”对话框中单击“确定”按钮即可删除存储过程。第25页/共87页10.2.4 10.2.4 删除存储过程删除存储过程2.使用DROP PROCEDURE删除存储过程DROP PROCEDURE的语法

16、如下：DROP PROCEDURE procedure_name ,.n 例如删除例10-2创建的存储过程employee_dep：DROP PROCEDURE employee_depGO第26页/共87页10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.5 存储过程参数与状态值存储过程和调用者之间通过参数交换数据，可以按输入的参数执行，也可由参数输出执行结果。调用者通过存储过程返回的状态值对存储过程进行管理。1.参数存储过程的参数在创建过程时声明。SQL Server支持两类参数：输入参数和输出参数。第27页/共87页10.2.5 10.2.5 存储过程参数与状态值存储

17、过程参数与状态值(1)输入参数输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须在CREATE PROCEDURE语句中声明一个或多个变量及类型。第28页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例10-3 创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。CREATE PROC sell_info employee_name varchar(20)AS SELECT employee_name,go

18、ods_name,classification_name,order_numFROM employee e INNER JOIN sell_order s ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification_id=g.classification_idWHERE employee_name LIKE employee_name第29页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值

19、存储过程sell_info以employee_name变量作为输入参数，执行时，可以省略参数名，直接给参数值。在SQL查询分析器中输入命令：EXEC sell_info 东方牧运行结果如图。n参数值可以包含通配符“%”，例如，查找所有姓“钱”的员工的销售情况可以使用以下命令：nEXEC sell_info 钱%第30页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值执行时，参数可以由位置标识，也可以由名字标识。例如，定义一个具有3个参数的存储过程：CREATE PROC myproc val1 int,val2 int,val3 intAS.参数以位置传递：EXE

20、C myproc 10,20,15参数以名字传递，每个值由对应的参数名引导：EXEC myproc val2=20,val1=10,val3=15按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。第31页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(2)输出参数输出参数允许存储过程将数据值或游标变量传回调用程序。OUTPUT关键字用以指出能返回到调用它的批处理或过程中的参数。为了使用输出参数，在CREATE PROCEDURE和EXECUTE语句中都必须使用OUTPUT关键字。第32页/共87页10.2.5 10.2.5 存

21、储过程参数与状态值存储过程参数与状态值例10-4 创建存储过程price_goods，通过输入参数在goods表中查找商品，以输出参数获取商品单价。CREATE PROC price_goods goods_name varchar(80)=NULL,price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsWHERE goods_name=goods_name第33页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值执行price_goods存储过程的代码如下：DECLARE price rea

22、lEXEC price_goods Canon LBP2900,price OUTPUTSELECT price运行结果是商品名为Canon LBP2900的商品单价：1380.0EXECUTE语句还需要关键字OUTPUT以允许参数值返回给变量。第34页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(1)用RETURN语句定义返回值存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。如果存储过程没有显式设置返回代码的值，则SQL Server返回代码为 0，表示成功执行；若返回-1-99之间的整数，表示没有成功执行。也可以使用RETURN语句，

23、用大于0或小于-99的整数来定义自己的返回状态值，以表示不同的执行结果。2.返回存储过程的状态第35页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例10-5 创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，用值15表示用户没有提供参数；值-l01表示没有输入商品类别；值0表示过程运行没有出错。CREATE PROC cl_goods cl_name varchar(40)=NULLASIF cl_name=NULL RETURN 15IF NOT EXISTS(SELECT*FROM goods_classification WHERE class

24、ification_name=cl_name)RETURN-101SELECT g.goods_name FROM goods_classification gc,goods gWHERE gc.classification_id=g.classification_id AND gc.classification_name=cl_nameRETURN 0第36页/共87页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值在执行过程时，要正确接收返回的状态值，必须使用以下语句；EXECUTE status_var=procedure_name(2)捕获返回状态值第37页/共87

25、页10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值DECLARE return_status intEXEC return_status=cl_goods 笔记本计算机IF return_status=15 SELECT 语法错误ELSE IF return_status=-101 SELECT 没有找到该商品类别执行时，将对不同的输入值返回不同的状态值及处理结果。例10-5的存储过程cl_goods执行时使用以下语句：第38页/共87页触发器是一种特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过过程名字直接调用。当对某一表进行UPDATE、I

26、NSERT、DELETE操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。10.3 触发器概述第39页/共87页10.4.1 创建触发器10.4.2 删除触发器10.4.3 修改触发器10.4 触发器的创建与使用第40页/共87页10.4 10.4 触发器的创建与使用触发器的创建与使用1使用SQL Server管理平台创建触发器 10.4.1 创建触发器第41页/共87页10.4.1 10.4.1 创建触发器创建触发器2.使用CREATE TR

27、IGGER语句创建触发器CREATE TRIGGER语句的语法格式如下：CREATE TRIGGER trigger_name ON table_name|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE AS sql_statement .n 第42页/共87页10.4.1 10.4.1 创建触发器创建触发器例10-6 在employee表上创建一个DELETE类型的触发器，该触发器的名称为tr_employee。(1)创建触发器tr_employeeCREATE TRIGGER tr_employee ON e

28、mployeeFOR DELETEAS DECLARE msg varchar(50)SELECT msg=STR(ROWCOUNT)+个员工被删除SELECT msgRETURN第43页/共87页10.4.1 10.4.1 创建触发器创建触发器(2)执行触发器tr_employee触发器不能通过名字来执行，而是在相应的SQL语句被执行时自动触发的。例如执行以下DELETE语句：DELETE FROM employeeWHERE employee_name=张三该语句要删除员工姓名为“张三”记录，由此激活了表employee 的DELETE类型的触发器tr_employee，系统执行tr_em

29、ployee触发器中AS之后的语句，并显示以下信息：1个员工被删除第44页/共87页10.4.1 10.4.1 创建触发器创建触发器在触发器的执行过程中，SQL Server建立和管理两个临时的虚拟表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。在执行INSERT或UPDATE语句之后所有被添加或被更新的记录都会存储在Inserted表中。在执行DELETE或UPDATE语句时，从触发程序表中被删除的行会发送到Deleted表。对于更新操作，SQL Server先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到Insert

30、ed表以及触发程序表。3.Deleted表和Inserted表第45页/共87页10.4.1 10.4.1 创建触发器创建触发器例10-7 为表customer创建一个名为test_tr的触发器，当执行添加、更新或删除时，激活该触发器。创建test_tr触发器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT*FROM inserted SELECT*FROM deletedcustomer表执行以下插入操作：INSERT INTO customer(customer_id,customer_name,tel

31、phone)VALUES(12346,张三,1234567)第46页/共87页10.4.1 10.4.1 创建触发器创建触发器INSERT操作激活触发器test_tr，输出如图10-19所示的表格。第47页/共87页10.4.2 修改触发器通过SQL Server管理平台、系统存储过程或Transact_SQL语句，可以修改触发器的名字和正文。1.使用sp_rename系统存储过程修改触发器的名字：sp_rename oldname,newname10.4 10.4 触发器的创建与使用触发器的创建与使用第48页/共87页10.4.3 10.4.3 修改触发器修改触发器2使用SQL Server

32、管理平台修改触发器的正文 修改触发器的操作步骤如下：（1）打开SQL Server管理平台，展开节点“对象资源管理器”“Sales”数据库|“表”“customer”表|“触发器”，选择要删除的触发器（如例10-7创建的test_tr触发器），单击鼠标右键，在弹出的快捷菜单中选择“修改”命令。（2）此时在右边的编辑器窗口中出现触发器的源代码（将CREATE TRIGGER改为了ALTER TRIGGER），可以直接进行修改。修改完后单击工具栏中的“执行”按钮执行该触发器代码，从而达到目的。第49页/共87页10.4.3 10.4.3 修改触发器修改触发器3.使用ALTER TRIGGER语句修

33、改触发器修改触发器的语法如下：ALTER TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OFDELETE ,INSERT ,UPDATE AS sql_statement .n 第50页/共87页10.4.3 10.4.3 修改触发器修改触发器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50)SELECT msg=STR(ROWCOUNT)+个员工数据被插入SELECT msgRETURN对employee表

34、执行以下插入语句：INSERT employee(employee_id,employee_name)VALUES(E016,王五)激活INSERT触发器tr_employee，显示信息：1个员工数据被插入例如，将例10-6的触发器tr_employee修改为INSERT操作后进行。第51页/共87页10.4 10.4 触发器的创建与使用触发器的创建与使用10.4.3 删除触发器1使用SQL Server管理平台删除触发器 操作步骤如下：（1）打开SQL Server管理平台，展开节点“对象资源管理器”“Sales”数据库|“表”“customer”表|“触发器”，选择要删除的触发器（如例10

35、-7创建的test_tr触发器），单击鼠标右键，在弹出的快捷菜单中选择“删除”命令。（2）在弹出的“删除对象”对话框中单击“确定”按钮即可删除触发器。第52页/共87页10.4.2 10.4.2 删除触发器删除触发器2.使用DROP TRIGGER删除指定触发器删除触发器语句的语法格式如下：DROP TRIGGER trigger_name ,.n 例如，删除例10-6的触发器tr_employee：DROP TRIGGER tr_employee第53页/共87页10.5.1 事务概述10.5.2 事务管理10.5 事务处理第54页/共87页10.5 10.5 事务处理事务处理事务(Tran

36、saction)是SQL Server中的一个逻辑工作单元，该单元将被作为一个整体进行处理。事务保证连续多个操作必须全部执行成功，否则必须立即回复到未执行任何操作的状态，即执行事务的结果要不全部将数据所要执行的操作完成，要不全部数据都不修改。10.5.1 事务概述第55页/共87页10.5.1 10.5.1 事务概述事务概述例如，企业取消了仓储部，需要将“仓储部”从department表中删除，而employee表中的部门编号与仓储部相对应的员工也应删除。假设仓储部编号为D004，第一条DELETE语句修改department表为：DELETE FROM department WHERE de

37、partment_id=D004第二条DELETE语句修改employee表为：DELETE FROM employee WHERE department_id=D004因此，必须保证这两条DELETE语句同时执行，或都不执行。这时可以使用数据库中的事务(Transaction)技术来实现。1事务的由来第56页/共87页2事务属性由于事务作为一个逻辑工作单元，当事务执行遇到错误时，将取消事务所做的修改。一个逻辑单元必须具有4个属性：原子性(Atomicity)、一致性(Consistency)隔离性(Isolation)持久性(Durability)，这些属性称为ACID。10.5.1 10.

38、5.1 事务概述事务概述第57页/共87页3事务模式SQL Server以3种事务模式管理事务。(1)自动提交事务模式：每条单独的语句都是一个事务。在此模式下，每条Transact-SQL语句在成功执行完成后，都被自动提交，如果遇到错误，则自动回滚该语句。该模式为系统默认的事务管理模式。(2)显式事务模式：该模式允许用户定义事务的启动和结束。事务以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。(3)隐性事务模式：在当前事务完成提交或回滚后，新事务自动启动。隐性事务不需要使用BEGIN TRANSACTION语句标识事务的开始，但需要以COMMIT

39、或ROLLBACK语句来提交或回滚事务。10.5.1 10.5.1 事务概述事务概述第58页/共87页10.5 10.5 事务处理事务处理1启动和结束事务启动事务语句的语法格式如下：BEGIN TRANSACTION transaction_name|tran_name_variable WITH MARK description 结束事务语句的语法格式如下：COMMIT TRANSACTION transaction_name|tran_name_variable 10.5.2 事务管理第59页/共87页10.5.2 10.5.2 事务管理事务管理例10-8 建立一个显式事务以显示Sales

40、数据库的employee表的数据。BEGIN TRANSACTION SELECT*FROM employee COMMIT TRANSACTION本例创建的事务以BEGIN TRANSACTION语句开始，以COMMIT TRANSACTION语句结束。第60页/共87页10.5.2 10.5.2 事务管理事务管理DECLARE transaction_name varchar(32)SELECT transaction_name=tran_deleteBEGIN TRANSACTION transaction_name DELETE FROM department WHERE depart

41、ment_id=D004 DELETE FROM employee WHERE department_id=D004COMMIT TRANSACTION tran_delete例10-9 建立一个显式命名事务以删除department表的“仓储部”记录行。第61页/共87页10.5.2 10.5.2 事务管理事务管理CREATE TABLE imp_tran(num char(2)NOT NULL,cname char(6)NOT NULL)GO SET IMPLICIT_TRANSACTIONS ON -启动隐性事务模式GO-第一个事务由INSERT语句启动INSERT INTO imp_t

42、ran VALUES(01,Zhang)INSERT INTO imp_tran VALUES(02,Wang)COMMIT TRANSACTION -提交第一个隐性事务GO-第二个隐式事务由SELECT语句启动SELECT COUNT(*)FROM imp_tranINSERT INTO imp_tran VALUES(03,Li)SELECT*FROM imp_tranCOMMIT TRANSACTION -提交第二个隐性事务GOSET IMPLICIT_TRANSACTIONS OFF -关闭隐性事务模式GO例10-10 隐性事务处理过程。第62页/共87页10.5.2 10.5.2 事

43、务管理事务管理当事务事务回滚使用ROLLBACK TRANSACTION语句实现，其语法格式如下：ROLLBACK TRANSACTION transaction_name|tran_name_variable|savepoint_name|savepoint_variable 2事务回滚第63页/共87页10.5.2 10.5.2 事务管理事务管理事务回滚到指定位置如果要让事务回滚到指定位置，则需要在事务中设定保存点(SavePoint)。所谓保存点是指定其所在位置之前的事务语句，不能回滚的语句即此语句前面的操作被视为有效。其语法格式如下：SAVE TRANSACTION savepoint

44、_name|savepoint_variable第64页/共87页10.5.2 10.5.2 事务管理事务管理例10-11 使用ROLLBACK TRANSACTION语句标识事务结束。BEGIN TRANSACTION UPDATE goods SET stock_quantity=stock_quantity-5 WHERE goods_id=G00006 INSERT INTO sell_order(order_id1,goods_id,order_num,order_date)VALUES(S00005,G00006,5,getdate()ROLLBACK TRANSACTION第65

45、页/共87页10.5.2 10.5.2 事务管理事务管理BEGIN TRANSACTION my_transaction_delete DELETE FROM department WHERE department_id=D005 SAVE TRANSACTION after_delete -设置保存点 UPDATE employee SET department_id=D001 WHERE department_id=D005 IF(error=0 OR rowcount=0)BEGIN ROLLBACK TRANSACTION after_delete -如果出错回滚到保存点after_d

46、elete COMMIT TRANSACTION my_transaction_delete END ELSE COMMIT TRANSACTION my_transaction_deleteGO例10-12 删除仓储部，再将仓储部的职工划分到总经理办。第66页/共87页10.5.2 10.5.2 事务管理事务管理CREATE TRIGGER trig_uptab ON goodsFOR UPDATEASSAVE TRANSACTION tran_uptabINSERT INTO newgoods SELECT*FROM insertedIF(error0)BEGIN ROLLBACK TRA

47、NSACTION tran_uptabEND例10-13 定义为表goods触发器trig_uptab，如果goods表更新数据，则把新数据复制到表newgoods中，若出错，则取消复制操作。第67页/共87页10.5.2 10.5.2 事务管理事务管理和BEGINEND语句类似，BEGIN TRANSACTION和COMMIT TRANSACTION语句也可以进行嵌套，即事务可以嵌套执行。3事务嵌套第68页/共87页10.5.2 10.5.2 事务管理事务管理CREATE TABLE employee_tran(num char(2)NOT NULL,cname char(6)NOT NUL

48、L)GO BEGIN TRANSACTION Tran1 -TRANCOUNT为1 INSERT INTO employee_tran VALUES(01,Zhang)BEGIN TRANSACTION Tran2 -TRANCOUNT为2 INSERT INTO employee_tran VALUES(02,Wang)BEGIN TRANSACTION Tran3 -TRANCOUNT为3 PRINT TRANCOUNT INSERT INTO employee_tran VALUES(03,Li)COMMIT TRANSACTION Tran3 -TRANCOUNT为2 PRINT TR

49、ANCOUNT COMMIT TRANSACTION Tran2 -TRANCOUNT为1 PRINT TRANCOUNTCOMMIT TRANSACTION Tran1 -TRANCOUNT为0PRINT TRANCOUNT例10-14 提交事务。运行结果：3210第69页/共87页10.6.1 锁模式10.6.2 隔离级别10.6.3 查看和终止锁10.6.4 死锁及其防止10.6 SQL Server的锁机制第70页/共87页10.6 SQL Server10.6 SQL Server的锁机制的锁机制锁(Lock)作为一种安全机制，用于控制多个用户的并发操作，以防止用户读取下在由其他用户

50、更改的数据或者多个用户同时修改同一数据，从而确保事务完整性和数据库一致性。10.6.1 锁模式第71页/共87页10.6.1 10.6.1 锁模式锁模式当对一个数据源加锁后，此数据源就有了一定的访问限制，称对此数据源进行了“锁定”。SQL Server有多种粒度锁，允许一个事务锁定不同类型的资源：数据行(Row)：数据页中的单行数据。索引行(Key)：索引页中的单行数据，即索引的键值。页(Page)：页是SQL Server存取数据的基本单位，其大小为8KB。扩展盘区(Extent)：一个盘区由8个连续的页组成。表(Table)。数据库(Database)。允许一个事务锁定的资源类型第72页/