数据库自定义函数事务锁

数据库自定义函数事务锁第1页，共58页，2023年，2月20日，星期六本章主要介绍SQLServer2005的高级应用，包括用户自定义函数、实现交叉表查询、事务处理以及锁。通过本章的学习，读者可以创建和管理用户自定义函数，可以使用PIVOT、UNPIVOT以及CASE实现交叉表查询，并了解事务处理机制和锁，应用事务和锁优化对数据的访问。9.1用户自定义函数9.2使用SQLServer2005实现交叉表查询9.3事务处理9.4锁主要内容第2页，共58页，2023年，2月20日，星期六9.1用户自定义函数

SQLServer2005还可以根据用户需要来自定义函数，以便用在允许使用系统函数的任何地方。用户自定义函数有两种方法，一种是利用MicrosoftSQLServerManager管理器直接创建，另一种是利用T-SQL代码创建。第3页，共58页，2023年，2月20日，星期六9.1.1创建用户自定义函数图9.1创建自定义函数第4页，共58页，2023年，2月20日，星期六9.1.2使用T-SQL语言创建用户自定义函数1、创建自定义函数利用Transact-SQL创建函数的语法如下：createfunction

函数名（@parameter变量类型[,@parameter变量类型]）returns参数ASbegin

命令行或程序块End第5页，共58页，2023年，2月20日，星期六函数可以有0个或若干个输入参数，但必须有返回值，returns后面就是设置函数的返回值类型。用户自定义函数为标量值函数或表值函数。如果returns子句指定了一种标量数据类型，则函数为标量值函数；如果returns子句指定TABLE，则函数为表值函数。根据函数主体的定义方式，表值函数可分为内联函数和多语句函数。第6页，共58页，2023年，2月20日，星期六【例7-7】为CollegeMIS数据库创建一个名为ScoreGrade的用户自定义函数，该函数用来对百分制成绩进行判断，并返回相应的等级。成绩大于等于90分时，返回的等级为“优秀”；成绩大于等于80分而小于90分时，返回的等级为“良好”；成绩大于等于70分而小于80分时，返回的等级为“中等”；成绩大于等于60分而小于70分时，返回的等级为“及格”；其它情况返回不及格。并调用该函数显示出所有选修了“0002”号课的人的成绩等级。2、创建自定义标量函数标量函数返回值的类型为SQLServer2000的系统数据类型，但不可以是text、ntext、image、cursor、timestamp、table等类型。标量函数的函数体语句定义在BEGIN．．．END语句内。7-4-1节中创建的FailureNum函数和ScoreGrade函数均为标量函数。第7页，共58页，2023年，2月20日，星期六CREATEFUNCTIONScoreGrade(@Scoretinyint)RETURNSvarchar(8)BEGINDECLARE@GradeStrvarchar(8)SET@GradeStr=CaseWHEN@Score>=90THEN'优秀'WHEN@Score>=80THEN'良好'WHEN@Score>=70THEN'中等'WHEN@Score>=60THEN'及格'ELSE'不及格'ENDRETURN@GradeStrEND第8页，共58页，2023年，2月20日，星期六3、调用自定义标量函数Transact-SQL调用函数的语法格式如下：

Printdbo.函数（[实参]）或

selectdbo.函数（[实参]）

dbo：是系统自带的一个公共用户名。USECollegeMISGOSELECTs.学号,s.姓名,dbo.ScoreGrade(sc.成绩)AS等级FROM成绩表SCINNERJOIN学生表SONSC.学号=S.学号WHERE课程号='102'GO第9页，共58页，2023年，2月20日，星期六9.1.3修改、删除用户自定义函数1．修改自定义函数利用Transact-SQL修改函数的语法如下：alterfunction函数名（@parameter变量类型[,@parameter变量类型]）returns参数asbegin

命令行或程序块End第10页，共58页，2023年，2月20日，星期六【例】现在规定考试成绩低于55分为不及格，则可使用如下语句对FailureNum函数进行修改。

ALTERFUNCTION[dbo].[FailureNum](@CourseNochar(4))RETURNSintASBEGINDECLARE@NumintSELECT@Num=Count(StuNo)FROMSelectCourseWHERECourseNo=@CourseNoANDScore<55RETURN@NumEND

2．修改自定义函数第11页，共58页，2023年，2月20日，星期六（1）通过企业管理器查看

【格式1】[EXEC]sp_help<用户自定义函数名>（2）使用系统存储过程查看用户自定义函数信息

【功能】查看“用户自定义函数名”指定的函数的概要信息。如语句：execsp_helpFailureNum【格式2】[EXEC]sp_helptext<用户自定义函数名>【功能】查看“用户自定义函数名”指定的函数的定义文本信息。

3．查看自定义函数第12页，共58页，2023年，2月20日，星期六4．删除自定义函数删除自定义函数的Transact-SQL语法如下：

Dropfunction函数名DropfunctionFailureNum第13页，共58页，2023年，2月20日，星期六9.2使用SQLServer2005实现交叉表查询

9.2.1使用PIVOT和UNPIVOT实现交叉表查询PIVOT和UNPIVOT运算符是SQLServer2005新增的功能。通过PIVOT和UNPIVOT就完全可以实现交叉表的查询，用PIVOT和UNPIVOT编写更简单，更易于理解。在查询的FROM子句中使用PIVOT和UNPIVOT，可以对一个输入表值表达式执行某种操作，以获得另一种形式的表。PIVOT运算符将输入表的行旋转为列，并能同时对行执行聚合运算。而UNPIVOT运算符则执行与PIVOT运算符相反的操作，它将输入表的列旋转为行。第14页，共58页，2023年，2月20日，星期六

PIVOT和UNPIVOT的语法如下：[FROM{<table_source>}[,...n]]<table_source>::={

table_or_view_name[[AS]table_alias]

<pivoted_table>|<unpivoted_table>}<pivoted_table>::=table_sourcePIVOT<pivot_clause>table_alias<pivot_clause>::=(aggregate_function(value_column)

FORpivot_column

IN(<column_list><unpivoted_table>::=table_sourceUNPIVOT<unpivot_clause>table_alias<unpivot_clause>::=(value_columnFORpivot_columnIN(<column_list><column_list>::=column_name[,...]table_sourcePIVOT<pivot_clause>第15页，共58页，2023年，2月20日，星期六例如：图9．3所示的商品表就是一个典型的交叉表，其中“数量”和“月份”可以继续添加。但是，这种格式在进行数据表存储的时候并不容易管理。图9.3商品表第16页，共58页，2023年，2月20日，星期六图9.4商品表结构图9.5sp表例如：存储图9．4所示的表格数据时，通常需要设计成图9.5所示的结构。这样就带来一个问题，用户既希望数据容易管理，又希望能够生成一种方便阅读的表格数据。恰好PIVOT运算符能够满足这两个条件。现设计图9.5所示的SP（商品）表，其中有商品名称、销售数量和月份列，并存储相应的数据。第17页，共58页，2023年，2月20日，星期六图9.5sp表图9.8由sp表经行转列得到的最终结果集将图9.5存储数据的sp表转换成人容易阅读的图9.8表格，SQL语句如下：usedbstudentselect商品名称,a.[9]as[九月],a.[101as[十月],a.[11]as[十一月],a.[12]as[＋二月]fromsppivot(sum（销售数量）for月份in（[9]，[10]，[ll]，[12]））asa其中：SP是输入表，月份是透视列（pivotcolumn），销售数量是值列（valuecolumn)。第18页，共58页，2023年，2月20日，星期六转换过程：上面的语句将按下面的步骤获得输出结果集。（1)PIVOT首先按值列之外的列（商品名称和月份）对输入表sp进行分组汇总，类似执行下面的SQL语句：Select商品名称，月份，sum（销售数量）astotalfromspgroupby商品名称，月份执行上述SQL语句将得到图9.6所示的中间结果集。图9.6sp表经过分组汇总后的结果第19页，共58页，2023年，2月20日，星期六

图9.7使用for月份in([9],[10],[11],[12])后得到的结果集

（2）PIVOT根据for月份in指定的值9,10,11,12在结果集中建立名为9、10,11,12的列，然后在中间结果集从月份列中取出相符合的值，分别放置到9,10、11、12列。此时得到别名为a（见语句中ASa的指定）的结果集如图9.7所示。（3）最后根据select商品名称，a.[9]as[九月],a.[10]as[十月],a.[11]as[十一月],a.[12]as[＋二月]from的指定，从别名是as的结果集中检索数据，并分别将名为9,10，11，12的列在最终结果集中重新命名为：九月、十月、十一月、十二月。注意：FROM是从通过PIVOT关系运算符得到的a结果集中检索数据，而不是从SP表中检索数据。最终得到的结果集如图9.8所示。

图9.8由sp表经行转列得到的最终结果集第20页，共58页，2023年，2月20日，星期六图9.9使用UNPIVOT得到的结果集UNPIVOT与PIVOT执行几乎完全相反的操作，将列转换为行。假设图9.8所示的结果集存储在一个名为temp的表中，现在需要将列标识符“九月”、“十月”、“十一月”和“十二月”转换到对应于相应商品名称的行值中。这意味着必须另外标识两个列，一个用于存储月份，一个用于存储销售数量。为了便于理解，仍旧将这两个列命名为月份和销售数量。SQL语句如下select*fromtempunpivot(销售数量for月份in([九月]，[十月]，I十一月]，[十二月]))asb运行上述SQL语句后的结果集如图9.9所示。

图9.8由sp表经行转列得到的最终结果集UNPIVOT注：UNPIVOT有时并不会重现原始表值表达式的结果。第21页，共58页，2023年，2月20日，星期六

1．用PIVOT举例例9-2】使用PIVOT运算符实现交叉表查询。SQL语句如下：select*fromsppivot(sum(销售数量)for商品名称in([李小葱专辑],[周木人专辑],[国产E601],[920演唱会DVD]))as统计实现的过程如图9.10所示。图9.10sp表按商品名称交叉查询第22页，共58页，2023年，2月20日，星期六有时还需要根据表的其他字段进行交叉查询。例如，在SP表中，按“月份”交叉查询。逐月进行聚合计算。SQL语句如下：select商品名称,a.[9]as[九月],a.[10]as[十月],a.[11］as[十一月],a.[12]as[十二月]fromsppivot(sum（销售数量）for月份in([9]，[10]，[11]，[12]))asa实现的过程如图9.11所示。图9.11sp表按月份交叉查询第23页，共58页，2023年，2月20日，星期六

2．用UNPIVOT举例UNPIVOT是PIVOT的逆操作。假设图2.71所示的结果集存储在结果表temp1中，图2.72所示的结果集存储在结果表temp2中。【例9-3】使用UNPIVOT运算符实现交叉表查询。用unpivot实现把templ表中的列标识李小葱专辑、周木人专辑、国产E601和920演唱会DVD转换到商品名称的行值中。相当于示例PIVOT的逆操作。SQL语句如下：select*fromtemp1unpivot(销售数量for商品名称in([李小葱专辑],[周木人专辑],[国产E601],[920演唱会DVD]))asa图9.12unpivot对temp1表实现逆操作第24页，共58页，2023年，2月20日，星期六图9.13unpivot对temp2实现逆操作用unpivot实现把tempt中的列标识9月份、10月份、11月份和12月份列标识名称的行值中。相当于把示例的pivot实现逆操作。SQL语句如下：select*fromtemptunpivot（销售数量for月份in([九月]，[十月]，[＋一月]，[十二月]))asa

实现的操作如图9.13所示。第25页，共58页，2023年，2月20日，星期六

9.2.2CASE实现交叉表查询利用CASE语句可以返回多个可能结果的表达式。CASE具有简单CASE和CASE查询两种函数格式。下面介绍简单CASE语句的语法。简单CASE语句：将某个表达式与一组简单表达式进行比较以确定结果。其语法形式如下：CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][ELSEelse_result_expressionEND第26页，共58页，2023年，2月20日，星期六【例9-4】使用CASE语句实现交叉表查询。

SQL语句如下：实现的过程如图9.14所示SELECT月份,SUM(CASE商品名称WHEN'李小葱专辑'THEN销售数量ELSENULLEND)AS[李小葱专辑],SUM(CASE商品名称WHEN'周木人专辑'THEN销售数量ELSENULLEND)as[周木人专辑],SUM(CASE商品名称WHEN'国产E601'THEN销售数量ELSENULLEND)AS[E601],SUM(CASE商品名称WHEN'920演唱会DVD'THEN销售数量ELSENULLEND)AS[920演唱会DVD]FROMspgroupby月份图9.14sp表按照商品名称交叉表查询第27页，共58页，2023年，2月20日，星期六图9.15sp表按照月份交叉表查询在SP表中，按照“月份”进行交叉表查询。SQL语句如下：SELECT商品名称,SUM(CASE月份WHEN,‘

9’THEN销售数量ELSENULLEND)AS[9月份],SUM(CASE月份WHEN,‘

10’

THEN销售数量ELSENULLEND)as[10月份],SUM(CASE月份WHEN,‘

11’

THEN销售数量ELSENULLEND)AS(11月份]，SUM(CASE月份WHEN,‘

12’

THEN销售数量ELSENULLEND)AS[12月份]FROMspGROUPBY商品名称实现的过程如图9.15所示。第28页，共58页，2023年，2月20日，星期六9.3事务处理事务：能确保把对多个数据操作作为一个单元来处理例如：银行转帐:转入和转出问题:假定张三账户直接转1000元到李四账户,模拟过程如下:准备:建立账户表bank,包括客户名字和当前余额银行规定，帐户余额不能少于1元，否则视为销户存钱:插入测试数据，张三开户，开户金额为1000元；李四开户，开户金额为1元演示开户过程为什么需要事务第29页，共58页，2023年，2月20日，星期六模拟实现转帐：使用UPDATE语句,从张三的帐户直接转帐1000元到李四的帐户

,两账户总额应保持不变--张三帐户减少1000元，李四帐户增多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=’张三’UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=’李四’转帐后:两账户总余额为2001元转帐前:两账户总余额为1001元第30页，共58页，2023年，2月20日，星期六事务的概念及特性

事务提供了一种机制、是一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤消操作请求事务是作为单个逻辑工作单元执行的一系列操作事务的四个特性:原子性（Atomicity）：事务能确保把对多个数据修改作为一个单元来处理，也就是原子操作。一致性（Consistency）：当事务完成时，数据必须处于一致状态隔离性（Isolation）:对数据进行修改的所有并发事务是彼此隔离的。这表明事务必须是独立的，它不应以任何方式依赖或影响其他事务持久性（Durability）：当事务完成之后，它对于系统的影响是永久性的。第31页，共58页，2023年，2月20日，星期六事务的分类

事务分类：事务运行的3种模式如下显式事务隐性事务自动提交事务显式事务：显式事务是显式地定义其开始和结束的事务

BEGINTRANSACTION--事务的起点

…数据库操作如:插入记录

…数据库操作如:删除记录COMMITTRANSACTION--事务的起终点根据系统的设置两种类型：一种是系统提供的事务，系统提供的事务是指在执行某些语句时，一条语句就是一个事务。另一种是用户定义的事务。第32页，共58页，2023年，2月20日，星期六隐性事务：通过T-SQL的SETIMPLICIT_TRANSACTIONSON

语句，将隐性事务模式设置为打开。

SETIMPLICIT_TRANSACTIONSONGO/*第一次执行Insert语句的时候将自动启动一个隐性事务*/INSERTINTOImpTranVALUES(1,'aaa')INSERTINTOImpTranVALUES(2,'bbb')GO/*提交第一个事务*/COMMITTRANSACTIONGO/*执行SELECT语句将启动第二个隐性事务*/SELECTCOUNT(*)FROMImpTranGOINSERTINTOImpTranVALUES(3,'ccc')GOSELECT*FROMImpTranGO/*提交第二个事务*/COMMITTRANSACTIONGOSETIMPLICIT_TRANSACTIONSOFFGO第33页，共58页，2023年，2月20日，星期六自动提交事务：所有Transact-SQL语句在完成时，都会提交或回滚。如果一条语句成功完成，则将其提交，如果遇到任何错误，则将其回滚;默认操作模式用T-SQL表示事务

Transact-SQL使用下列语句来管理事务开始事务：BEGINTRANSACTION提交事务：COMMITTRANSACTION回滚（撤消）事务：ROLLBACKTRANSACTION下列变量在事务处理中非常有用。@@ERROR@@TRANCOUNT第34页，共58页，2023年，2月20日，星期六事务的应用-----模拟实现转帐USEempDBGOSETNOCOUNTON--不显示受影响的行数信息print‘查看转帐事务前的余额’SELECT*FROMbankGO/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）*/BEGINTRANSACTION/*定义变量，用于累计事务执行过程中的错误--*/DECLARE@errorNoINTSET@errorNo=0--初始化为0，即无错误/*--张三的帐户减少1000元，李四的帐户多1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=’张三’Set@errorNo=@errorNo+@@error--累计是否有错误Print‘查看转帐过程中的余额’SELECT*FROMbank第35页，共58页，2023年，2月20日，星期六/*--根据语句执行情况，确定事务是提交或撤消--*/IF@errorNo<>0--如果有错误

BEGINPrint‘交易失败，回滚事务’

ROLLBACKTRANSACTIONENDELSEBEGINPrint‘交易成功，提交事务，永久保存’

COMMITTRANSACTIONENDGOPrint‘查看转帐事务后的余额’SELECT*FROMbankGO第36页，共58页，2023年，2月20日，星期六

9.3.2

T-SQL处理事务处理1．事务的起点事务以BEGINTRANSACTION语句开始。BEGINTRANSACTION语句使全局变量@@TRANCOUNT按1递增。语法如下：BEGIN{TRAN|TRANSACTION}[{transaction_name|@tran_name_variable}[WITHMARK['description']]][;]第37页，共58页，2023年，2月20日，星期六

2．事务的终点事务以COMMITTRANSACTION作为隐性事务或显式事务成功结束的标志。语法如下：COMMIT{TRAN|TRANSACTION}[transaction_name|@tran_name_variable]][;]3．数据回滚

使用ROLLBACKTRANSACTION语句将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法如下：ROLLBACK{TRAN|TRANSACTION}[transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable][;]第38页，共58页，2023年，2月20日，星期六

4．事务保存点使用SAVETRANSACTION语句在事务内设置保存点。语法如下：SAVE{TRAN|TRANSACTION}{savepoint_name|@savepoint_variable}[;]5．事务的应用

（1）常用事务（2）隐式事务第39页，共58页，2023年，2月20日，星期六第40页，共58页，2023年，2月20日，星期六9.4锁

9.4.1锁简介

1．锁的概念锁是保护事务和数据的方式，这种保护方式类似于日常生活中使用的锁。锁是防止其他事务访问指定资源的手段，是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。在MicrosoftSQLServer中可以锁定的资源有多种，这些可以锁定的资源分别是行、页、Extent、表和数据库，他们对应的锁分别是行级锁、页级锁、Extent级锁、表级锁和数据库级锁。数据行存放在页上，页存放在Extent上，一个表有若干个Extent组成，而若干个表组成了数据库。在这些可以锁定的资源中，最基本的资源是行、页和表，而Extent和数据库是特殊的可以锁定的资源。第41页，共58页，2023年，2月20日，星期六锁锁防止更新冲突，用户不能读取或修改其他用户正在进行修改的数据锁使得事务的串行成为可能，同一时间只有一个人可以修改数据元素SQLServer在事务执行期间动态设置和调整适当的锁定级别，也可以手动控制如何使用锁对允许用户同时间内访问和更新数据的并发事务来说，锁是必需的第42页，共58页，2023年，2月20日，星期六锁防止的并发问题丢失更新当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题未确认的相关性（脏读）当第二个事务选择其他事务正在更新的行时，会发生未确认的相关性问题不一致的分析（非重复读）当第二个事务多次访问同一行，而且每次读取不同的数据时，会发生不一致的分析问题幻像读当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围的时候，会发生幻像读问题第43页，共58页，2023年，2月20日，星期六SQLServer可以锁定以下项目类型SQLServer使用DB锁来确定数据库是否在使用中项目描述RID行标识符。用于单独锁定表中的一行键索引中的行锁。用于保护可串行事务中的键范围页8KB的数据页或索引页扩展盘区相邻的八个数据页或索引页构成的一组，在空间分配中使用表包括所有数据和索引在内的整个表数据库整个数据库，在数据库的还原中使用第44页，共58页，2023年，2月20日，星期六锁的类型SQLServer主要有两种类型的锁：基本锁和特殊情况锁基本锁：包括共享锁和排它锁总的来说，读操作获得共享锁，写操作获得排它锁共享锁：用于不更改或不更新数据的操作（只读操作），如SELECT语句排它锁：用于数据修改操作，例如INSERT、UPDATE或DELETE特殊情况锁意向锁：SQLServer内部使用意向锁，以使锁定冲突减至最少更新锁：用于可更新的资源中，防止当多个会话在读取、锁定及随后可能进行的资源更新时发生常见形式的死锁架构锁：在执行依赖于表架构的操作时使用，确保表或索引在被另外的会话引用时不被删除或更改架构。架构锁包括架构稳定性(Sch-S)锁和架构修改(Sch-M)锁大容量更新锁：向表中大容量复制数据并指定了TABLOCK提示时使用第45页，共58页，2023年，2月20日，星期六可使用sp_lock命令查看锁的信息第46页，共58页，2023年，2月20日，星期六锁的兼容性只有兼容的锁类型才可以放置在已锁定的资源上。当在对象上请求的锁与对象上现有的锁不兼容时，请求必须等待现有的授权模式请求模式ISSUIXSIXXSch-SSch-MBUIS是是是是是否是否否S是是是否否否是否否U是是否否否否是否否IX是否否是否否是否否SIX是否否否否否是否否X否否否否否否是否否Sch-S是是是是是是是否是Sch-M否否否否否否否否否BU否否否否否否是否是第47页，共58页，2023年，2月20日，星期六锁模式描述共享(S)用于不更改或不更新数据的操作（只读操作），如SELECT语句。更新(U)用于可更新的资源中。防止当多个会话在