第10章 用户自定义函数与事务

第10章用户自定义函数与事务学习目标1、用户定义函数的创建、修改及删除2、掌握事务处理的基本方法，编写事务处理程序解决一般的实际应用问题SQLServer不但提供了系统内置函数，而且还允许用户根据实际需要创建用户自定义函数。用户自定义函数是由一条或多条T-SQL语句组成的子程序，保存在数据库内。它可以具有多个输入参数，并返回一个标量值（单个数据值）或一个表。SQLServer支持三种类型的用户自定义函数：标量函数、表值函数及用户定义聚合函数。标量函数标量函数创建格式create

function标量函数名(@参数1类型,@参数2类型2,[…n])returns

返回值类型asbegin return返回值end【例10.1】使用命令行方式在Sales数据库创建名为Fn_Cost的自定义函数，用于计算Goods表的进货金额，并将其绑定到Goods表。usesalesgocreate

function

fn_cost(@xint,@ymoney)returnsmoneyas

beginreturn(@x

*

@y)end使用标量函数 在Goods表中增加一列，列名为金额，并将函数Fn-Cost与其绑定。金额列为计算列。计算列由可以使用同一表中的其他列的表达式计算得来。该表达式可以是非计算列的列名、常量、函数、变量，也可以是用一个或多个运算符连接的这些元素的任意组合。表达式不能为子查询。计算列不允许直接修改列的值。altertablegoodsadd

金额

as

dbo.fn_cost(

数量

,

进货价)10.2.2表值函数1.内联表值函数2.多语句表值函数1.内联表值函数内嵌表值函数没有函数体，其返回的表是单个select语句的结果集。由于视图不支持在where子句的搜索条件中使用参数，内嵌表值函数可弥补视图的这一不足之处，即内嵌表值函数可用于实现参数化的视图功能。create

function函数名(@参数1类型,@参数2类型2,[…n])returns

tableasreturn(select

语句)【例10.2】使用命令行方式在Sales数据库创建名为Fn_Total的自定义函数，用于统计Sell表在某一时间段内的销售情况。create

function

fn_total(@bt

datetime,@et

datetime)returns

tableasreturn(select

goods.商品名称,sell.数量

from

goods,sellwhere(sell.售出时间>=@btandsell.售出时间<=@etandgoods.商品编号=sell.商品编号))select*fromdbo.fn_total('2005-1-1','2005-1-31')2.多语句表值函数多语句表值函数的函数体在begin-end块中定义。函数体可以包含多条T-SQL语句，这些语句可生成行并将行插入将返回的表中。由于视图只能包含单条select语句，而多语句表值函数可包含多条T-SQL语句。因此，多语句表值函数的功能比视图更强大。此外，多语句表值函数还可替换返回单个结果集的存储过程。create

function函数名(@参数1类型,@参数2类型2,[…n])returns

@函数名

table(列名类型,[…n])asbegininsert

@函数名

select

语句returnend【例10.3】使用命令行方式在Sales数据库创建名为Fn_Lan的自定义函数，该函数生成一张表，表的内容为进货价为指定价格以上的商品。create

function

fn_lan(@pricemoney)returns@fn_lantable(商品编号

intprimarykeynotnull,商品名称varchar(20)notnull,生产厂商varchar(30)notnull,

进货价moneynotnull,进货时间

datetimenotnull)as

begin

insert@fn_lan

select商品编号,商品名称,生产厂商,进货价,进货时间fromgoodswhere(进货价>=@price)

returnendselect*fromfn_lan(1000)10.3事务处理10.3.1事务简介事务是一个逻辑工作单元，其中包括了一系列的操作，这些操作要么全部执行，要么都不执行。典型的事务实例是两个银行之间的转账，账号A转出1000元至账号B，这笔转账业务可分解为：（1）账号A减去1000元；（2）账号B增加1000元。当然，要求这两项操作要么同时成功（转账成功），要么同时失败（转账失败）。只有其中一项操作成功则是不可接受的事情。如果确实发生了只有其中一项操作成功的话，那么应该撤销所做的操作（回滚事务），就好像什么操作都没有发生一样。事务的4个属性属性特点原子性事务必须作为工作的最小单位，即原子单位。其所进行的操作要么全部执行，要么都不执行。一致性每个事务必须保证数据的一致性。事务完成后，所有数据必须保持其合法性，即所有数据必须遵守数据库的约束和规则。隔离性一个事务所做的修改必须与其他事务所做的修改隔离。一个事务所使用的数据必须是另一个并发事务完成前或完成后的数据，而不能是另一个事务执行过程的中间结果。也就是说，两个事务是相互隔离的，其中间状态的数据是不可见的。持久性事务完成后对数据库的修改将永久保持。事务的模式事务的模式可分为显式事务、隐式事务和自动事务。显式事务：由用户自己使用T-SQL语言的事务语句定义的事务，具有明显的开始和结束标志。隐式事务：SQLServer为用户而做的事务。例如：在执行一条insert语句时，SQLServer将把它包装到事务中，如果执行此insert语句失败，SQLServer将回滚或取消这个事务。用户可以通过执行以下命令使SQLServer进入或退出隐式事务状态：setimplicittransaction：使系统进入隐式事务模式。setimplicittransactioff：使系统退出隐式事务模式。自动事务：SQLServer的默认事务管理模式。在自动提交模式下，每个T-SQL语句在成功执行完成后，都被自动提交；如果遇到错误，则自动回滚该语句。当用户开始执行一个显式事务时，SQLServer进入显式事务模式。当显式事务被提交或回滚后，SQLServer又重新进入自动事务模式。对于隐式事务也是如此，每当隐式事务被关闭后，SQLServer会返回自动事务模式。10.3.2事务处理T-SQL语言的事务语句包括以下几种。1．begintransaction语句功能：定义一个事务，标志一个显式事务的起始点。2．committranscation

语句其中transaction_name是由前面begintransaction语句指派的事务名称。功能：提交一个事务，标志一个成功的显式事务或隐式事务的结束。说明：当在嵌套事务中使用committranscation语句时，内部事务的提交并不释放资源，也没有执行永久修改。只有在提交了外部事务时，数据修改才具有永久性，资源才会释放。begintransaction[事务名]committranscation[事务名]3．rollbacktranscation

语句其中“事务名”是由前面begintransaction语句指派的事务名称。功能：回滚一个事务，将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点。rollbacktranscation[事务名]说明：（1）执行了committranscation语句后不能再回滚事务。（2）事务在执行过程中出现的任何错误，SQLServer实例将回滚事务。（3）系统出现死锁时会自动回滚事务。（4）由于其他原因(客户端网络连接中断、应用程序中止等)引起客户端和SQLServer实例之间通信的中断，SQLServer实例将回滚事务。（5）在触发器中发出rollbacktranscation命令，将回滚对当前事务中所做的数据修改，包括触发器所做的修改。（6）对于嵌套事务，rollbacktranscation语句将所有内层事务回滚到最远的begintransaction语句，“事务名”也只能是来自最远的begintransaction语句的名称。4．SAVETRANSACTION语句功能：建立一个保存点，使用户能将事务回滚到该保存点的状态，而不是简单回滚整个事务。在编写事务处理程序中，使用到的全局变量有：@@error：最近一次执行的语句引发的错误号，未出错时其值为零。@@rowcount：受影响的行数。savetransaction保存点名在事务中不能包含的语句有：createdatabasealterdatabasedropdatabaserestoredatabasebackuplogrestorelogreconfigureupdatestatistics10.4锁1锁的概念锁作为一种安全机制，用于控制多个用户的并发操作，防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据，确保事务的完整性和数据的一致性。锁定机制的主要属性是锁的粒度和锁的类型。不同粒度的锁可以锁定的资源资源说明行行锁定，锁定表中的一行数据键键值锁定，锁定具有索引的行数据页页面锁定，锁定8KB的数据页或索引页区域区域锁定，锁定8个连续的数据页面或索引页面表表锁定，锁定整个表，包括所有数据和索引在内数据库数据库锁定，锁定整个数据库SQLServer使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。常用的锁模式有以下3种。（1）共享（Shared）锁。（2）更新（Update）锁。（3）独占（Exclusive）锁。各种锁之间的兼容性共享锁更新锁独占锁共享锁是是不更新锁是不不独占锁不不不【例10.8】在命令行方式下，使用系统存储过程SP_LOCK查看当前持有锁的信息。在查询窗口输入以下SQL语句并运行：

USEMASTERGOEXECSP_LOCKGO

运行后，从显示结果可以看到当前持有锁的信息。2、死锁及其排除锁机制的引入能解决并发用户的数据一致性问题，但可能会引起进程间的死锁。引起死锁的主要原因是，两个进程已各自锁住一个页，但又要求访问被对方锁住的页。更一般的情况是，一个事务独占了其他事务正在申请的资源，且若干个这样的事务形成一个等待圈。并发操作中，为了避免死锁，建议采用以下措施。（1）最大限度地减少保持事务打开的时间长度。（2）按同一顺序访问对象。（3）尽量避免事务中的用户交互。（4）保