网络数据库第十章存储过程与触发器.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创建存储过程,创建存储过程语句的语法格式如下：,CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_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执行存储过程,执行存储过程的语法格式：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE,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的语法规则是：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_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=D001GO执行存储过程employee_dep，结果如图,10.2.3修改存储过程,(2)查看employee_dep存储过程的文本信息,SELECTo.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWHEREo.type=PANDo.name=employee_depGO,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的语法如下：DROPPROCEDUREprocedure_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_infoemployee_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_nameLIKEemployee_name,10.2.5存储过程参数与状态值,存储过程sell_info以employee_name变量作为输入参数，执行时，可以省略参数名，直接给参数值。在SQL查询分析器中输入命令：EXECsell_info东方牧运行结果如图。,参数值可以包含通配符“%”，例如，查找所有姓“钱”的员工的销售情况可以使用以下命令：EXECsell_info钱%,10.2.5存储过程参数与状态值,执行时，参数可以由位置标识，也可以由名字标识。,例如，定义一个具有3个参数的存储过程：CREATEPROCmyprocval1int,val2int,val3intAS.参数以位置传递：EXECmyproc10,20,15参数以名字传递，每个值由对应的参数名引导：EXECmyprocval2=20,val1=10,val3=15按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。,10.2.5存储过程参数与状态值,(2)输出参数,输出参数允许存储过程将数据值或游标变量传回调用程序。OUTPUT关键字用以指出能返回到调用它的批处理或过程中的参数。为了使用输出参数，在CREATEPROCEDURE和EXECUTE语句中都必须使用OUTPUT关键字。,10.2.5存储过程参数与状态值,例10-4创建存储过程price_goods，通过输入参数在goods表中查找商品，以输出参数获取商品单价。,CREATEPROCprice_goodsgoods_namevarchar(80)=NULL,price_goodsrealOUTPUTASSELECTprice_goods=unit_priceFROMgoodsWHEREgoods_name=goods_name,10.2.5存储过程参数与状态值,执行price_goods存储过程的代码如下：DECLAREpricerealEXECprice_goodsCanonLBP2900,priceOUTPUTSELECTprice运行结果是商品名为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_goodscl_namevarchar(40)=NULLASIFcl_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存储过程参数与状态值,在执行过程时，要正确接收返回的状态值，必须使用以下语句；EXECUTEstatus_var=procedure_name,(2)捕获返回状态值,10.2.5存储过程参数与状态值,DECLAREreturn_statusintEXECreturn_status=cl_goods笔记本计算机IFreturn_status=15SELECT语法错误ELSEIFreturn_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_nameONtable_name|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETEASsql_statement.n,10.4.1创建触发器,例10-6在employee表上创建一个DELETE类型的触发器，该触发器的名称为tr_employee。,(1)创建触发器tr_employeeCREATETRIGGERtr_employeeONemployeeFORDELETEASDECLAREmsgvarchar(50)SELECTmsg=STR(ROWCOUNT)+个员工被删除SELECTmsgRETURN,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_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEASsql_statement.n,10.4.3修改触发器,ALTERTRIGGERtr_employeeONemployeeFORINSERTASDECLAREmsgvarchar(50)SELECTmsg=STR(ROWCOUNT)+个员工数据被插入SELECTmsgRETURN对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启动和结束事务启动事务语句的语法格式如下：BEGINTRANSACTIONtransaction_name|tran_name_variableWITHMARKdescription结束事务语句的语法格式如下：COMMITTRANSACTIONtransaction_name|tran_name_variable,10.5.2事务管理,10.5.2事务管理,例10-8建立一个显式事务以显示Sales数据库的employee表的数据。,BEGINTRANSACTIONSELECT*FROMemployeeCOMMITTRANSACTION本例创建的事务以BEGINTRANSACTION语句开始，以COMMITTRANSACTION语句结束。,10.5.2事务管理,DECLAREtransaction_namevarchar(32)SELECTtransaction_name=tran_deleteBEGINTRANSACTIONtransaction_nameDELETEFROMdepartmentWHEREdepartment_id=D004DELETEFROMemployeeWHEREdepartment_id=D004COMMITTRANSACTIONtran_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语句实现，其语法格式如下：ROLLBACKTRANSACTIONtransaction_name|tran_name_variable|savepoint_name|savepoint_variable,2事务回滚,10.5.2事务管理,事务回滚到指定位置,如果要让事务回滚到指定位置，则需要在事务中设定保存点(SavePoint)。所谓保存点是指定其所在位置之前的事务语句，不能回滚的语句即此语句前面的操作被视为有效。其语法格式如下：SAVETRANSACTIONsavepoint_name|savepoint_variable,10.5.2事务管理,例10-11使用ROLLBACKTRANSACTION语句标识事务结束。,BEGINTRANSACTIONUPDATEgoodsSETstock_quantity=stock_quantity-5WHEREgoods_id=G00006INSERTINTOsell_order(order_id1,goods_id,order_num,order_date)VALUES(S00005,G00006,5,getdate()ROLLBACKTRANSACTION,10.5.2事务管理,BEGINTRANSACTIONmy_transaction_deleteDELETEFROMdepartmentWHEREdepartment_id=D005SAVETRANSACTIONafter_delete-设置保存点UPDATEemployeeSETdepartment_id=D001WHEREdepartment_id=D005IF(error=0ORrowcount=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事务管理,和BEGINEND语句类似，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为3PRINTTRANCOUNTINSERTINTOemployee_tranVALUES(03,Li)COMMITTRANSACTIONTran3-TRANCOUNT为2PRINTTRANCOUNTCOMMITTRANSACTIONTran2-TRANCOUNT为1PRINTTRANCOUNTCOMMITTRANSACTIONTran1-TRANCOUNT为0PRINTTRANCOUNT,例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)。与提交读隔离级别相反，它允许读取脏数据，即已经被其他用户修改但尚未提交的数据。它是最低的事务隔离级别，仅可保证不读取物理损坏的数据。(3)可重复读(REPEATABLEREAD)。在此隔离级别下，用SELECT语句读取的数据在整个语句执行过程中不会被更改。此选项会影响系统的效能，非必要情况最好不用此隔离级别。(4)可串行读(SERIALIZABLE)。将共享锁保持到事务完成，而不是不管事务是否完成都在不再需要所需的表或数据页时就立即释放共享锁。它是最高的事务隔离级别，事务之间完全隔离。,SQLServer支持以下4种隔离级别：,10.6.2隔离级别,使用SETTRANSACTIONISOLATIONLEVEL语句设置会话的隔离级别,其语法格式如下：SETTRANSACTIONISOLATIONLEVELREADCOMMITTED|READUNCOMMITTED|REPEATABLEREAD|SERIALIZABLE,10.6SQLServer的锁机制,1用企业管理器查看和终止锁在企业管理器中选择目录树窗口中“管理”文件夹下“当前活动”项中的“锁/进程ID”选项，可以查看当前锁定的进程。选择同级的“锁/对象”选项下的相应选项，则可以查看当前锁定的对象。,10.6.3查看和终止锁,10.6.3查看和终止锁,右键单击任务板窗口中的对象，从快捷菜单中选择“属性”选项，则会出现锁的进程细节对话框。在此，可以刷新或终止锁的进程。,10.6.3查看和终止锁,系统存储过程sp_lock的语法格式如下：sp_lockspidspid是SQLServer的进程编号，它可以在master.dbo.sysprocesses系统表中查到。spid数据类型为int，如果不指定spid，则显示所有的锁。,2用系统存储过程sp_lock查看锁,10.6.3查看和终止锁,例如，显示当前系统中所有的锁，结果如图。,10.6.3查看和终止锁,又如，显示编号为52的锁的信息,10.6.3查看和终止锁,终止进程：,KILLspidspid是系统进程编号。例如，终止的进程52的语句如下：KILL52,10.6.3查看和终止锁,终止进程：,死锁(Deadlocking)是在多用户或多进程状况下，为使用同一资源而产生的无法解决的争用状态。死锁会造成资源的大量浪费，甚至会使系统崩溃。因此，在SQLServer2000中，通常由锁监视器线程自动定期对死锁进行检测。当识别死锁后，SQLServer自动设置一个事务结束死锁进程。,10.6.3查看和终止锁,防止死锁应遵循以下原则：,(1)尽量避免并发地执行涉及到修改数据的语句。(2)要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行。(3)预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁。例如，不同的过程在事务内部对对象的更新执行顺序应尽量保持一致。(4)每个事务的执行时间不可太长，对程序段长的事务可考虑将其分割为几个事务。,10.6SQLServer的锁机制,本章小结(1)存储过程是一组SQL语句和流程控制语句的集合，以一个名字存储并作为一个单元处理。存储过程用于完成某项任务，它可以接受参数、返回状态值和参数值，并且实现嵌套调用。(2)触发器就其本质而言是一种特殊的存储过程，有3种类型：插入触发器、更新触发器和删除触发器。(3)创建、删除、查看、修改存储过程和触发器可以使用企业管理器或Transact-SQL语句。(4)存储过程和触发器的各种信息的查看、修改还可以使用系统存储过程sp_helptext、sp_rename、sp_helptrigger、sp_depends实现。(5)事务是一个操作序列，它包含了一组数据库操作命令，所有的命令作为一个整体一起向系统提交或撤消操作请求，即要么都执行，要么都不执行。(6)锁是在多用户环境下对资源访问的一种限制。当对一个数据源加锁后，此数据源就有了一定的访问限制。(7)事务与锁也是保证数据完整性和正确性的机制，可以确保数据能够正确地被存储、修改，而不会造成数据在存储或修改过程中因事故或其他用户的中断而导致的数据不完整。,