存储过程与触发器

本章内容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提供支持。如：sp_helptext、sp_rename。10.1存储过程概述(2)用户定义存储过程是由用户创建并能完成某一特定功能（例如查询用户所需数据信息）的存储过程。注意：

存储过程名前不能有前缀sp_。10.1存储过程概述(3)临时存储过程临时存储过程与临时表类似，存储过程名称前面添加“#”。当SQLServer关闭后，这些临时存储过程将自动被删除。10.1存储过程概述(4)扩展存储过程扩展存储过程是SQLServer可以动态装载和执行的动态链接库(DLL)。当扩展存储过程加载到SQLServer中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。10.1存储过程概述2.存储过程具有以下优点(5)保证操作一致性。(1)模块化编程。(2)快速执行。

(3)减少网络通信量。(4)提供安全机制。10.2.1创建存储过程10.2.2执行存储过程10.2.3修改存储过程10.2.4删除存储过程10.2.5存储过程参数与状态值10.2存储过程的创建与使用在SQLServer中建立存储过程的两种方法：

一是使用企业管理器；

二是使用CreateProcedure语句。10.2.1

创建存储过程1.使用企业管理器创建存储过程演示操作例：建立存储过程stu_pro，查询所有女生的基本信息。语法格式：CREATEPROC[EDURE]

存储过程名[WITH

ENCRYPTION]ASsql_statement[,...n]2.使用CREATEPROCEDURE语句创建存储过程其中：WITHENCRYPTION：对定义存储过程的的语句文本进行加密。sql_statements：存储过程中实现功能的SQL语句。语法格式：

EXEC

存储过程名称

10.2.2执行存储过程【例】创建一存储过程xs_pro，要求该存储过程返回学生姓名、所学课程名称和成绩的信息。CREATEPROCEDURE

xs_proAS

SELECT姓名,课名,成绩

FROM学生xsINNERJOIN

选课xk

ONxs.学号=xk.学号INNERJOIN

课程kcONxk.课号=kc.课号

如要执行该存储过程，可在查询分析器中执行如下语句：EXEC

xs_pro【练习】①创建存储过程cj_proc，查询每科的课号、最高分、最低分、平均分信息。②创建存储过程rs_proc，统计每科及格的人数。createprocedurecj_procasselect课号,max(成绩)最高分,min(成绩)最低分

from选课表

groupby课号createprocedurers_procasselect课号,count(*)人数from选课表

where成绩>=60groupby课号1.用T-SQL语句查看存储过程⑴查看存储过程的定义

sp_helptext

存储过程名查看存储过程⑵查看有关存储过程的信息

sp_help

存储过程名2.使用企业管理器查看存储过程①展开服务器组，然后展开服务器。②展开“数据库”文件夹，展开存储过程所属的数据库，然后单击“存储过程”文件夹。③在详细信息窗格中，右击需要查看的存储过程，然后单击“属性”命令。也可以直接双击存储过程也可。④该存储过程的定义代码显示在“存储过程属性”窗口的文本框中。创建和执行存储过程proc_grade,查询选课表中选修2号课学生的学号、成绩信息，并使存储过程不能使用sp_helptext查看。CREATEPROCproc_gradeWITHencryption

ASSELECT

学号,成绩FROM

选课表

WHERE

课号=‘2’GOsp_helptextproc_grade【练习】10.2存储过程的创建与使用10.2.3

修改存储过程1.使用企业管理器修改存储过程演示操作例：修改存储过程stu_pro，查询“软件”专业学生的姓名。10.2.3修改存储过程2.使用ALTERPROCEDURE语句修改存储过程ALTER

PROC[EDURE]

存储过程名[WITHENCRYPTION]AS

sql_statement[...n]①创建存储过程proc_sjk,获取选修“程序设计”课的所有学生的姓名，成绩信息。②修改存储过程proc_sjk，使其能够显示出选修该课的女生的姓名、成绩信息，并对其加密。【练习】createprocedurepro_sjkasselect姓名,成绩from学生表xsinnerjoin选课表xkonxs.学号=xk.学号innerjoin课程表kconxk.课号=kc.课号where课名='程序设计'alterprocedurepro_sjkwithencryptionasselect姓名,成绩from学生表xsinnerjoin选课表xkonxs.学号=xk.学号innerjoin课程表kconxk.课号=kc.课号

where课名='程序设计'and性别='女'10.2存储过程的创建与使用10.2.4删除存储过程1.使用企业管理器删除存储过程演示操作例：删除存储过程stu_pro。10.2.4删除存储过程2.使用DROPPROCEDURE删除存储过程语法如下：DROPPROC[EDURE]存储过程名[,...n]10.2存储过程的创建与使用10.2.5存储过程参数与状态值1.参数存储过程的参数在创建过程时声明。SQLServer支持两类参数：

输入参数和输出参数。10.2.5存储过程参数与状态值(1)输入参数CREATEPROC[EDURE]

存储过程名

@参数名1数据类型[长度[,小数位]][，……][WITH

ENCRYPTION]ASsql_statement[,...n]10.2.5存储过程参数与状态值例10-3

创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。CREATEPROCsell_info@employee_namevarchar(20)

AS

SELECTemployee_name,goods_name,classification_name,order_numFROMemployeeeINNERJOINsell_ordersON

e.employee_id=s.employee_idINNER

JOINgoodsgONg.goods_id=s.goods_id

INNER

JOINgoods_classificationgcON

gc.classification_id=g.classification_idWHEREemployee_nameLIKE@employee_name10.2.5存储过程参数与状态值执行时，可以省略参数名，直接给参数值。在SQL查询分析器中输入命令：EXEC

sell_info'东方牧'运行结果如图。参数值可以包含通配符“%”，例如，查找所有姓“钱”的员工的销售情况可以使用以下命令：

EXECsell_info'钱%'10.2.5存储过程参数与状态值执行时，参数可以由位置标识，也可以由名字标识。例如，定义一个具有3个参数的存储过程：CREATEPROC

myproc@val1

int,@val2

int,@val3

intAS...参数以位置传递：EXEC

myproc

10,20,15参数以名字传递，每个值由对应的参数名引导：EXEC

myproc

@val2=20,@val1=10,@val3=15按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。【例】建立一个存储过程，根据所给学生的姓名查询该学生的信息。CREATEPROCxs_proc@xmvarchar(8)ASSELECT*from

学生表

WHERE

姓名=@xmGOEXECxs_proc'李明'创建带参数的存储过程proc_list，根据输入的课号，查询选课表中该科成绩排名前3位的学生的学号和成绩信息。执行该存储过程，查询课号为’1’的成绩排名前3位学生。CREATEPROCEDUREproc_list@kcbhchar(1)AS

SELECTTOP3学号,成绩

FROM

选课表

WHERE

课程编号=@kcbh

ORDERBY

分数DESCGOEXECproc_list'1'【练习】10.2.5存储过程参数与状态值(2)输出参数CREATEPROC[EDURE]

存储过程名

@参数名1数据类型[长度[,小数位]][,……,]

@输出参数1数据类型[长度[,小数位]]

OUTPUT[,……][WITH

ENCRYPTION]ASsql_statement[,...n]10.2.5存储过程参数与状态值例10-4

创建存储过程price_goods，通过输入参数在goods表中查找商品，以输出参数获取商品单价。CREATEPROCprice_goods@goods_namevarchar(80),@price_goodsrealOUTPUTASSELECT@price_goods=unit_priceFROMgoodsWHEREgoods_name=@goods_name10.2.5存储过程参数与状态值执行price_goods存储过程的代码如下：DECLARE@pricerealEXECprice_goods'CanonLBP2900',@priceOUTPUTSELECT@price运行结果是商品名为'CanonLBP2900'的商品单价：1380.0EXEC语句还需要关键字OUTPUT以允许参数值返回给变量。【例】创建一个实现两个整数加法运算并将运算结果作为输出参数的存储过程spadd。CREATEPROCEDUREspAdd@Value1INT,@Value2INT,@ResultValueINT

OUTPUTASSELECT@ResultValue=@Value1+@Value2GODECLARE@resultintEXECspAdd100,20,@resultOUTPUTSELECT@result创建带输入和输出参数的存储过程proc_avg，根据输入的课号，查询选课表中该科的最高分、最低分、平均分。

执行该存储过程，查询课号为‘2’的学生的最高分、最低分和平均分。CREATEPROCproc_avg@kcbhchar(4),@max_fsfloat

OUTPUT,@min_fsfloatOUTPUT,@avg_fsfloatOUTPUTASSELECT@max_fs=MAX(成绩),@min_fs=MIN(成绩),@avg_fs=AVG(成绩)

FROM

选课表

WHERE

课号=@kcbhGODECLARE@maxfloat,@minfloat,@avgfloatEXECproc_avg'0002',@maxOUTPUT,@minOUTPUT,@avgOUTPUTSELECT@maxAS最高分,@minAS

最低分,@avgAS

平均分【练习】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=NULL

RETURN15IF

NOTEXISTS

(SELECT*FROMgoods_classification

WHEREclassification_name=@cl_name)

RETURN-101SELECTg.goods_nameFROMgoods_classificationgc,

goodsg

WHEREgc.classification_id=g.classification_idANDgc.classification_name=@cl_nameRETURN0CREATEPROCspzd@xint,@yintASIF@x>@y

RETURN0ELSERETURN-101GODECLARE@resultintEXEC@result=spzd10,20SELECT@result结果:-101【练习】10.2.5存储过程参数与状态值在执行过程时，要正确接收返回的状态值，必须使用以下语句；EXEC

@status_var=procedure_name(2)捕获返回状态值10.2.5存储过程参数与状态值DECLARE@return_statusintEXEC@return_status=cl_goods'笔记本计算机'IF@return_status=15

SELECT'语法错误'ELSE

IF@return_status=-101

SELECT'没有找到该商品类别'执行时，将对不同的输入值返回不同的状态值及处理结果。例10-5的存储过程cl_goods执行时使用以下语句：触发器（Trigger）

1)触发器是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行UPDATE、INSERT或DELETE语句时自动触发执行。

2）触发器也有三种类型：

INSERT触发器、

UPDATE触发器、

DELETE触发器。10.3触发器概述10.4.1创建触发器10.4.2删除触发器10.4.3修改触发器10.4触发器的创建与使用10.4触发器的创建与使用1.使用企业管理器创建触发器右击要设置触发器的表，在弹出菜单上选择“所有任务”选项。单击“管理触发器”，弹出“触发器属性”对话框。10.4.1创建触发器演示操作例：创建触发器stu_tri，当往学生表中插入记录时，显示学生表，查看是否插入成功。10.4.1创建触发器2.使用CREATETRIGGER语句创建触发器语法格式如下：CREATETRIGGER

触发器名ON

表名|视图名

[WITHENCRYPTION]

FOR[INSERT]

[,

UPDATE]

[,DELETE]AS

sql_statement[...n]10.4.1创建触发器例10-6

在employee表上创建一个DELETE类型的触发器，该触发器的名称为tr_employee。(1)创建触发器tr_employeeCREATETRIGGERtr_employeeONemployeeFORDELETEAS

DECLARE@msgvarchar(50)

SELECT@msg=STR(@@ROWCOUNT)+'个员工被删除'

SELECT@msg

RETURN10.4.1创建触发器(2)执行触发器tr_employee触发器不能通过名字来执行，而是在相应的SQL语句被执行时自动触发的。例如执行以下DELETE语句：DELETEFROMemployeeWHEREemployee_name='张三'显示以下信息：

1个员工被删除1、创建UPDATE触发器up_grade，当选课表中成绩列数据被修改时，显示提示信息“用户了修改了成绩值”。CREATEup_gradeON

选课表FORAS

IFUPDATE(成绩)

BEGIN

PRINT'用户修改了成绩值'

ENDGOUPDATE

选课表

SET

成绩=90WHERE

学号=‘890163’and课号=‘1’TRIGGERUPDATE【练习】2、在选课表上创建一个UPDATE、DELETE类型的触发器up_tri，当对选课表进行更新或删除操作时，显示提示信息：X条记录已被改动了。createtriggerup_tri

on

选课表

forupdate,delete

asselect

str(@@rowcount)+'已被改动了'10.4.1创建触发器对于UPDATE操作，SQLServer先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到Inserted表中。3.Deleted表和Inserted表

在执行DELETE语句时，从触发的表中被删除的行会发送到

Deleted表。在触发器的执行过程中，SQLServer自动建立两个虚拟表：Deleted表和Inserted表。

在执行INSERT语句之后所有被添加的记录都会存储在

Inserted表中。10.4.1创建触发器例10-7

为表customer创建一个名为test_tr的触发器，当执行添加、更新或删除时，激活该触发器。创建test_tr触发器：CREATETRIGGERtest_trONcustomerFORINSERT,UPDATE,DELETEAS

SELECT*FROMinserted

SELECT*FROMdeleted10.4.1创建触发器customer表执行以下插入操作：INSERTINTOcustomer(customer_id,customer_name,telphone)VALUES('12346','张三','1234567')1、建立DELETE触发器del_st_g，当学生表中的记录被删除时,选课表中的所有相应记录能自动删除。CREATETRIGGERdel_st_gON

学生表FORDELETEAS

BEGIN

DELETEFROM

选课表

WHERE

学号IN(SELECT

学号from

deleted)ENDGODELETEFROM

学生表WHERE

学号='890163'【练习】CREATETRIGGERtr_upON

课程表FORUPDATEASUPDATE

选课表

SET

课程编号=(SELECT

课程编号

FROMinserted)

WHERE

课号=(SELECT

课号FROMdeleted)GOUPDATE

课程表SET

课号=‘5'WHERE

课号='1'2、在课程表中建立UPDATE触发器tr_up，如果更新课程表中的课号，则相应更新选课表的课号。

再进行UPDATE操作，将课程表中值为“1”的课号值修改为“5”，查看相应的选课表的记录值是否改变。【练习】3、为选课表建立一个名为insert_tr的INSERT触发器，当用户向选课表中插入记录时，如果插入的是在课程表中没有的课号，则提示用户不能插入记录，否则提示记录插入成功。

【练习】createtriggerinsert_tr

on选课表

forinsertasifexists(select*from课程表

where课号

in

(select课号

frominserted))select'插入成功！'elseselect'用户不能插入记录!'10.4触发器的创建与使用10.4.2删除触发器1.使用企业管理器删除触发器演示操作例：删除触发器stu_tri。10.4.2删除触发器2.使用DROPTRIGGER删除指定触发器删除触发器语句的语法格式如下：DROPTRIGGER

触发器名[,...n]

例如，删除例10-6的触发器tr_employee：DROPTRIGGERtr_employee10.4.3修改触发器1.

修改触发器的名字：sp_renameoldname,newname2.使用企业管理器修改触发器的正文演示操作10.4.3修改触发器3.使用ALTERTRIGGER语句修改触发器修改触发器的语法如下：ALTERTRIGGER

触发器名ON

表名|视图名[WITHENCRYPTION]FORDELETE,INSERT,UPDATEAS

sql_statement[...n]10.4.3修改触发器ALTERTRIGGERtr_employeeONemployeeFORINSERTASDECLARE@msgvarchar(50)

SELECT@msg=STR(@@ROWCOUNT)+'个员工数据被插入'

SELECT@msg

RETURN对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.1事务概述例如，企业取消了仓储部，需要将“仓储部”从department表中

删除，而employee表中的部门编号与仓储部相对应的员

工也应删除。假设仓储部编号为D004，第一条DELETE语句修改department表为：

DELETEFROMdepartmentWHEREdepartment_id='D004'第二条DELETE语句修改employee表为：

DELETEFROMemployeeWHEREdepartment_id='D004’因此，必须保证这两条DELETE语句同时执行，或都不执行。这时可以使用数据库中的事务(Transaction)技术来实现。1．事务的由来

事务(Transaction),是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个不可分割的工作单元。10.5.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][事务名|事务变量名]结束事务语句的语法格式如下：COMMIT[TRAN[SACTION]][事务名|事务变量名]10.5.2事务管理10.5.2事务管理例10-8

建立一个显式事务以显示Sales数据库的employee表的数据。BEGINTRANSACTION

SELECT*FROMemployeeCOMMITTRANSACTION本例创建的事务以BEGINTRANSACTION语句开始，以COMMITTRANSACTION语句结束。10.5.2事务管理DECLARE@transaction_namevarchar(32)SELECT@transaction_name='tran_delete'BEGINTRANSACTION@transaction_name

DELETEFROMdepartment

WHEREdepartment_id='D004'

DELETEFROMemployee

WHEREdepartment_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

隐性事务处理过程。以下代码定义一个事务,为studentsdb数据库的curriculum表插入两条记录,然后提交该事务.--事务开始

INSERTcurriculum(课程编号,课程名称,学分)VALUES(‘0008’,’体育’,6)INSERTcurriculum(课程编号,课程名称,学分)VALUES(‘0009’,’哲学’,2)--提交事务

BEGINTRANCOMMITTRAN【练习】【练习】定义一个显示事务，将存款表中账户号为‘A001’的5000元存款转到账户号为‘B001’的账户上，然后提交该事务.存款表（账户号，账户名，余额）BeginTransactionupdate

存款表set

余额=余额-5000where

账户号=‘A001’

update

存款表set

余额=余额+5000where

账户号=‘B001’CommitTransaction10.5.2事务管理事务回滚使用ROLLBACKTRANSACTION语句实现，其语法格式如下：ROLLBACK[TRAN[SACTION][事务名|事务变量名|保存点名|保存点变量名

]2．事务回滚10.5.2事务管理事务回滚到指定位置如果要让事务回滚到指定位置，则需要在事务中设定保存点(SavePoint)。其语法格式如下：SAVE

TRAN[SACTION]保存点名|保存点变量名10.5.2事务管理例10-11

使用ROLLBACKTRANSACTION语句标识事务结束。BEGINTRANSACTION

UPDATEgoods

SETstock_quantity=stock_quantity-5

WHEREgoods_id='G00006'

INSERTINTOsell_order(order_id1,goods_id,order_num,order_date)

VALUES('S00005','G00006',5,getdate())ROLLBACKTRANSACTION10.5.2事务管理BEGINTRANSACTIONmy_transaction_delete

DELETEFROMdepartmentWHEREdepartment_id='D005'

SAVETRANSACTIONafter_delete--设置保存点

UPDATEemployeeSETdepartment_id='D001'

WHEREdepartment_id='D005'

IF(@@error<>0OR@@rowcount=0)

BEGIN

ROLLBACKTRANSACTIONafter_delete--如果出错回滚到保存点after_delete

COMMITTRANSACTIONmy_transaction_deleteEND

ELSE

COMMITTRANSACTIONmy_transaction_deleteGO例10-12

删除仓储部，再将仓储部的职工划分到总经理办。【练习】定义一个显示事务，首先将存款表中账户号为‘A001’的5000元存款转到账户号为‘B001’的账户上。然后判断‘A001’账户余额是否小于0，如果小于0，则回滚该事务，否则提交该事务.存款表（账户号，账户名，余额）BeginTransactionupdate

存款表set

余额=余额-5000where

账户号=‘A001’

update

存款表set

余额=余额+5000where

账户号=‘B001’

if(select余额from存款表where

账户号=‘A001’)<0

RollbackTransactionelseCommitTransaction10.5.2事务管理CREATETRIGGERtrig_uptabONgoodsFORUPDATEASSAVETRANSACTIONtran_uptabINSERTINTOnewgoodsSELECT*FROMinsertedIF(@@error<>0)BEGIN

ROLLBACKTRANSACTIONtran_uptabEND例10-13为表goods定义触发器trig_uptab，如果goods表更新数据，则把新数据复制到表newgoods中，若出错，则取消复制操作。建立触发器up_credit，当用户试图修改studentsdb数据库的curriculum表的学分列时，禁止更新，并显示提示信息。CREATETRIGGERup_creditONcurriculumFORUPDATEASIFBEGINPRINT'禁止用户修改学分列！

TRANSACTIONEND--执行以下UPDATE操作：GOBEGINTRANSACTIONUPDATEcurriculumSET学分=5where课程编号='0001‘COMMITTRANSACTIONupdate(学分)rollback【练习】createtriggercj_in_trion选课表forinsertas

declare@cjdecimal(5,1)

select@cj=成绩frominserted

if@cj<0or@cj>100

rollbacktranelsecommittraninsert

选课表values('12010','2',50)【练习】在选课表上建立一个插入触发器cj_in_tri，判断插入记录的成绩值是否在0~100间（包括0和100），在，则成功提交，否则回滚。【练习】为选课表创建一个名为del_tr的DELETE触发器，该触发器的作用是禁止删除选课表中的记录。

createtriggerdel_tron

选课表fordeleteas

if

exists(select*fromdeleted)

rollbacktran

print'禁止删除记录'Gobegintrandeletefrom

选课表Committran10.5.2事务管理3．事务嵌套例10-14

提交事务BEGINTRANSACTIONTran1--@@TRANCOUNT为1INSERTINTOemployee_tranVALUES('01','Zhang')

BEGINTRANSACTIONTran2--@@TRANCOUNT为2INSERTINTOemployee_tranVALUES('02','Wang')

BEGINTRANSACTIONTran3--@@TRANCOUNT为3PRINT@@TRANCOUNTINSERTINTOemployee_tranVALUES('03','Li')

COMMITTRANSACTIONTran3--@@TRANCOUNT为2PRINT@@TRANCOUNT

COMMITTRANSACTIONTran2--@@TRANCOUNT为1PRINT@@TRANCOUNTCOMMITTRANSACTIONTran1--@@TRANCOUNT为0PRINT@@TRANCOUNT运行结果：3210定义存储过程，为studentsdb数据库的curriculum表插入过程参数指定的数据，如果课程编号或课程名称为空，则禁止插入，如果插入时产生错误，则撤销该插入操作。CREATEPROCp_ins_c@cnumchar(4)=NULL,@cnamechar(50)=NULL,@creditint=NULLASIF@cnumISNULLOR@cnameISNULLBEGINPRINT'必须提供课程编号，课程名称！'RETURNENDTRANSACTIONINSERTcurriculum(课程编号,课程名称,学分)VALUES(@cnum,@cname,@credit)IF@@error<>0BEGINTRANSACTIONRETURNENDPRINT'新课程已经添加'COMMITTRANSACTIONGO--执行存储过程EXECp_ins_cEXECp_ins_c'0015'EXECp_ins_c'0015','数据结构'

ROLLBACK

BEGIN

CREATETRIGGERtri_insertONstudent_infoFORINSERTASDECLARE@departchar(10)SELECT@depart=院系FROMinsertedIF@depart<>'信息院'AND@depart<>'材料院'AND@depart<>'管理院'

BEGIN

ROLLBACKTRANSACTION

PRINT'不能插入非指定院系的学生信息!'ENDINSERTstudent_info(学号,姓名,院系)VALUES('0010','王海','材料院')创建一个INSERT触发器tri_insert，当student_info表中插入一条新学生记录时，如果不是“信息院”、“材料院”、“管理院”的学生，则撤销该插入操作，并返回出错信息。生成tri_insert触发器后，以数据学号’0010’、姓名’王海’、‘材料院’进行测试。10.6.1锁模式10.6.2隔离级别10.6.3死锁及其防止10.6SQLServer的锁机制10.6SQLServer的锁机制锁(Lock)作为一种安全机制，用于控制多个用户的并发操作，以防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据，从而确保事务完整性和数据库一致性。10.6.1锁模式10.6.1锁模式确定并发事务访问资源方式的锁模式:(1)共享锁(SharedLock)。(2)排它锁(ExclusiveLock)。(3)更新锁(UpdateLock)。从程序员的角度，锁可以分为以下两种类型：(1)乐观锁(OptimisticLock)。乐观锁假定在处理数据时，不需要在应用程序的代码中做任何事情就可以直接在记录上加锁，即完全依靠数据库来管理锁的工作。一般情况下，当执行事务处理时，SQLServer会自动对事务处理范围内更新到的表做锁定。(