数据库技术及应用（SQL Server ）2.8

数据库技术及应用（SQLServer）教学单元2.8第10章T-SQL程序设计案例2-10-1图书管理存储过程的创建与管理案例2-10-2图书管理触发器的创建与管理案例2-10-3图书管理用户定义函数的创建与管理数据库服务器编程学习导航2T-SQL程序设计知识框架3单元2.8T-SQL程序设计能力目标能够根据数据库应用系统的功能需求和完整性需求设计存储过程设计触发器设计用户定义函数能够阅读并熟练书写存储过程、触发器、用户定义函数有关的T-SQL命令（英文）知识目标存储过程的概念与编程方法触发器的概念与编程方法用户定义函数的概念与编程方法存储过程、触发器和用户定义函数有关的英文4单元2.8T-SQL程序设计素质目标遵循《软件生存周期过程》《数据库语言SQL》等软件开发国家标准，培养严谨、严格和规范的软件开发职业素养通过介绍学校实际应用的数据库应用软件“图书馆集成管理系统”的流通外借出纳管理界面，加强对“案例2-10-2图书管理触发器的创建与管理”中借书与还书触发器T-SQL程序设计的理解*拓展阅读10-1图书馆集成管理系统—应用界面35案例2图书管理系统案例2-10-1图书管理存储过程的创建与管理案例2-10-2图书管理触发器的创建与管理案例2-10-3图书管理用户定义函数的创建与管理工作任务单元2.8T-SQL程序设计6

单元2.8T-SQL程序设计7创建与管理存储过程一创建与管理触发器二创建与管理用户定义函数三一、创建与管理存储过程8案例2-10-1图书管理存储过程的创建与管理根据图书管理系统的功能需求和完整性需求：创建与管理数据库“Library”的存储过程，实现数据操作以及完整性控制。工作任务一、创建与管理存储过程9存储过程概述1创建存储过程2管理存储过程3（一）存储过程概述10存储过程（StoredProcedure）一组编译好的、存储在数据库服务器上的和完成特定功能的T-SQL程序，是某数据库的对象。客户端应用程序可以通过调用（指定存储过程的名字并给出参数（如果该存储过程带有参数））来执行存储过程。1.存储过程的概念（一）存储过程概述11可以重复调用。提高执行速度。减少网络流量。提供安全机制。2.使用存储过程的优点（一）存储过程概述12系统存储过程（存储在master数据库中）前缀：sp_例如sp_rename、sp_help等用户定义存储过程T-SQL存储过程：指保存的T-SQL程序，可以接受和返回用户提供的参数。存储过程也可能从数据库向客户端应用程序返回数据。CLR存储过程：指对Microsoft.NETFramework公共语言运行时方法的引用，可以接受和返回用户提供的参数，它们在.NETFramework程序集中是作为类的公共静态方法实现的。3.存储过程的分类（一）存储过程概述13扩展存储过程扩展存储过程是以在SQLServer环境之外执行的动态链接库（DynamicLinkLibraries，DLL）来实现的，通常以前缀xp_开头。扩展存储过程用与存储过程相似的方式来执行。3.存储过程的分类（二）创建存储过程14CREATEPROC[EDURE]过程名 --创建存储过程

[[@形参数据类型] --输入参数

|[@形参数据类型=默认值] --默认值参数

|[@变参数据类型OUTPUT] --输出参数（返回值）

][,...n]AS[BEGIN]T-SQL语句 --过程体（T-SQL程序，返回结果集或值）[END]使用T-SQL创建存储过程的基本语法（二）创建存储过程15【例10-1】为数据库“Library”创建一个多表查询的存储过程，查询出读者为“程鹏”的借阅信息。代码USELibraryGOCREATEPROCEDUREborrowed_book1ASBEGINSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDWHERERname='程鹏'END1.无参存储过程（二）创建存储过程16执行结果在数据库“Library”中创建了存储过程“borrowed_book1”调用borrowed_book1或EXECborrowed_book1返回结果1.无参存储过程（二）创建存储过程17【例10-2】为数据库“Library”创建一个查询某读者（姓名在执行存储过程时给出）借阅情况的存储过程。代码USELibraryGO--输入形式参数@nameCREATEPROCEDUREborrowed_book2@namechar(8)ASBEGINSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDWHERERname=@nameEND2.带输入参数的存储过程（二）创建存储过程18调用（常量传值）EXECborrowed_book2'杨淑华'--实参表'杨淑华'或DECLARE@temp1char(8)SET@temp1='杨淑华'EXECborrowed_book2@temp1--实参表@temp1返回结果2.带输入参数的存储过程（二）创建存储过程19【例10-3】为数据库“Library”创建使用默认值参数查询某读者（姓名在执行存储过程时给出）借阅情况的存储过程。代码CREATEPROCEDUREborrowed_book3@namechar(8)=NULL--默认参数ASBEGINIF@nameISNULL--@name为默认值NULL，IF条件成立SELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDELSESELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDWHERERname=@nameEND3.带默认值参数的存储过程（二）创建存储过程20调用EXECborrowed_book3返回结果2.使用T-SQL创建存储过程（二）创建存储过程21【例10-4】为数据库“Library”创建一个查询某出版社图书总价值和平均价值的存储过程。代码USELibraryGOCREATEPROCEDUREbook_price--创建存储过程@Publishervarchar(30),--输入形式参数@SUMPricedecimal(9,2)OUTPUT,--输出形式参数1@AVGPricedecimal(9,2)OUTPUT--输出形式参数2AS4．带输出参数的存储过程（二）创建存储过程22【例10-4】为数据库“Library”创建一个查询某出版社图书总价值和平均价值的存储过程。代码续BEGIN

--输出参数1得到图书总价值，赋值语句SELECT@SUMPrice=SUM(price)FROMBookWHEREPublisher=@Publisher

--输出参数2得到图书平均价值，赋值语句SELECT@AVGPrice=AVG(price)FROMBookWHEREPublisher=@PublisherEND（二）创建存储过程23调用DECLARE@chvarchar(30),@ou1decimal(9,2),@ou2decimal(9,2)SET@ch='人民邮电出版社‘GOEXECbook_price--调用存储过程@ch,@ou1Output,@ou2Output--输入实参，输出实参1，2GOSELECT@chAS书名,@ou1AS总价值,@ou2AS平均价值

--输出变量（实参）值返回结果4．带输出参数的存储过程（三）管理存储过程24修改ALTERPROC[EDURE]过程名 --修改存储过程

[[@形参数据类型] --输入参数

|[@形参数据类型=默认值] --默认值参数

|[@变参数据类型OUTPUT] --输出参数

][,...n]AS[BEGIN]T-SQL语句 --过程主体[END]1．修改存储过程（三）管理存储过程25基本语法DROPPROC[EDURE]存储过程名[,...n]例：删除数据库“Library”的存储过程“borrowed”。代码：USELibraryDROPPROCborrowed2．删除存储过程单元2.8T-SQL程序设计26创建与管理存储过程一创建与管理触发器二创建与管理用户定义函数三案例2-10-2图书管理触发器的创建与管理根据图书管理系统的功能需求和完整性需求：创建与管理服务器、数据库“Library”及其表或视图的触发器，实现数据操作以及完整性控制。工作任务二、创建与管理触发器27二、创建与管理触发器28触发器概述1创建DML触发器2创建DDL触发器3管理触发器4（一）触发器概述29特殊的存储过程，是基于表/视图/服务器/数据库创建的。触发器里也包含一系列的T-SQL语句，但它的执行不是用EXEC主动调用的，而是在满足一定条件下自动执行的。当触发器所保护的数据库中的数据经过操作发生变化或者当服务器、数据库中发生数据定义事件时，系统将自动运行触发器中的程序以保证数据库的完整性、正确性和安全性。通俗地说，触发器是基于一个基表/视图/服务器/数据库，并通过一个事件被调用的存储过程。1.触发器的概念（一）触发器概述30DML触发器基于表或视图设计的T-SQL程序DML（INSERT、UPDATE、DELETE语句）触发事件调用自动创建临时表“inserted”“deleted”作用可以实现回滚（撤销）触发事件。实现较为复杂的数据完整性控制。2.触发器的分类（一）触发器概述31DDL触发器DDL（CREATE、ALTER、DROP语句）触发事件调用作用用于管理任务，例如审核和控制数据库操作。2.触发器的分类（一）触发器概述32登录触发器LOGON事件触发作用用于控制数据库服务器的安全，例如拒绝某登录名启动的SQLServer登录尝试。2.触发器的分类（二）创建DML触发器33语法CREATETRIGGER<触发器名>ON<表名|视图名>{AFTER--DML语句完成后调用触发器|INSTEADOF}--DML语句执行时被触发器所替代[UPDATE][,][INSERT][,][DELETE]--DML触发事件AS[BEGIN]T-SQL语句--过程体（T-SQL程序）[END]使用T-SQL创建DML触发器（二）创建DML触发器34参数说明AFTER：仅在触发T-SQL语句中指定的

INSERT/UPDATE/DELETE语句操作都成功执行之后才被执行。所有的引用级联操作和约束检查也必须在执行此触发器之前成功完成。不能对视图定义AFTER触发器。INSTEADOF：为表和视图指定的DML触发器用于“替代”引起触发器执行的T-SQL语句，因此其优先级高于触发语句的操作。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEADOF触发器。使用T-SQL创建DML触发器（二）创建DML触发器35临时表inserted和deleted说明DML触发器执行时自动创建两个临时表inserted表和deleted表，触发器工作完成后即被删除。inserted表用于临时保存被插入或被更新后的数据行副本。在执行INSERT或UPDATE语句时，新的数据行被插入到触发器表中，同时也被插入到inserted表中。可以从inserted表中读取所插入的数据，进一步进行对其他表的操作。也可以判断所插入的数据是否满足完整性规则，如不满足则可以回滚（撤消）此操作。使用T-SQL创建DML触发器（二）创建DML触发器36deleted表用于临时保存被删除或被更新后的数据行副本。在执行DELETE或UPDATE语句时，从触发器表中删除数据行并传输到deleted表中。可以从deleted表中检查所删除的数据行是否满足删除条件，如不满足则可以回滚（撤消）此操作。执行UPDATE语句更新数据时，类似于在删除之后执行插入；首先被删除的数据行被传输到deleted表中，然后新的数据行被插入到inserted表中。使用T-SQL创建DML触发器（二）创建DML触发器37INSERT事件的AFTER触发器是对指定的表执行插入数据行语句INSERTINTO...VALUES事件之后被激发执行的一段程序代码。1.创建AFTERINSERT触发器（二）创建DML触发器38【例10-5】读者借书时，对数据库“Library”完成以下处理。（1）使用INSERT语句完成借阅表“Borrow”添加借书信息的操作。添加读者编号和图书编号。借期“LendDate”为当前系统日期（定义表时已经设置为默认值）。（2）调用触发器T-SQL程序，判断所借图书是否已经借出。尚未借出，则：①计算该书的应还日期“SReturnDate”为借期加限借天数；②将读者表“Reader”该读者的借阅数量“Lendnum”增加1本；③将图书表“Book”该书是否借出“LentOut”置为真。已经借出，则：①撤销所添加的读者借书数据；②提示该书已借出。1.创建AFTERINSERT触发器（二）创建DML触发器39CREATETRIGGERT_Borrow--创建触发器ONBorrow--基于表“Borrow”AFTERINSERTAS--INSERT操作之后激发BEGINDECLARE@dzbhchar(10),@tsbhchar(15),@dzlxint,@xjtsint--从添加行副本inserted中查询出RID并赋值给变量@dzbh--从添加行副本inserted中查询出BID并赋值给变量@tsbh--从表“Reader”中查询出添加行读者的TypeID赋值给变量@dzlxSET@dzbh=(SELECTRIDFROMinserted)SET@tsbh=(SELECTBIDFROMinserted)SET@dzlx=(SELECTTypeIDFROMReaderWHERERID=@dzbh)1.创建AFTERINSERT触发器（二）创建DML触发器40--判断所借图书是否已经借出IF

EXISTS(SELECT*FROMBookWHEREBID=@tsbhANDLentOut=0)BEGIN--①应还日期为借期加限借天数UPDATEBorrowSETSReturnDate=DATEADD(dd,(SELECTLimitDaysFROMReaderTypeWHERETypeID=@dzlx),LendDate)WHERERID=@dzbhANDBID=@tsbhANDReturnDateISNULL1.创建AFTERINSERT触发器（二）创建DML触发器41--②将读者表Reader中该读者的借阅数量Lendnum加1UPDATEReaderSETLendnum=Lendnum+1WHERERID=@dzbh--③将图书表Book中该书是否借出LentOut置为1UPDATEBookSETLentOut=1WHEREBID=@tsbhENDELSE--该图书已借出BEGINROLLBACK--回滚数据行添加操作PRINT‘该书已借出’ENDEND1.创建AFTERINSERT触发器（二）创建DML触发器42假设1：执行以下INSERT语句完成图书借阅的操作INSERTINTOBorrow(RID,BID)VALUES(‘2000186010’,‘TP312/429’)INSERT语句对借阅表“Borrow”的操作如下读者编号列“RID”得到“2000186010”图书编号列“BID”得到“TP312/429”借期列“LendDate”得到定义表时设置的系统默认值GETDATE()，假设系统日期为“2023-11-30”1.创建AFTERINSERT触发器（二）创建DML触发器43调用触发器“T_Borrow”，判断结果为所借书尚未借出借阅表“Borrow”，应还日期列“SReturnDate”得到’2022-02-28'在触发器中从表“Reader”中查询出该读者的读者类型（教师），再从表“ReaderType”中查询出该读者类型的限借天数90，由借阅日期’2023-11-30'加90天得到应还日期’2024-02-28'1.创建AFTERINSERT触发器（二）创建DML触发器44读者表“Reader”，读者编号为'2000186010'的借阅数量“Lendnum”增加到11.创建AFTERINSERT触发器（二）创建DML触发器45图书表“Book”，图书编号为'TP312/429'的图书是否借出列“LendOut”置为1（True）1.创建AFTERINSERT触发器（二）创建DML触发器46假设2：有读者也要借阅图书编号为TP312/429的图书。INSERTINTOBorrow(RID,BID)VALUES(‘2022216117','TP312/429‘)调用触发器“T_Borrow”，判断结果为所借书已经借出对“Borrow”表的INSERT语句回滚，添加信息撤销。返回执行显示结果该书已借出消息3609，级别16，状态1，第1行事务在触发器中结束。批处理已中止。1.创建AFTERINSERT触发器（二）创建DML触发器47【例10-6】读者还书，对数据库“Library”完成以下处理。使用UPDATE语句完成借阅表“Borrow”更新还书信息操作，还期为当前系统日期。使用触发器编程判断还书是否过期及以下处理：①如果过期，计算过期天数；②将读者表“Reader”中该读者的借阅数量“Lendnum”减少1本；③将图书表“Book”该书是否借出“LentOut”置为假。

2．创建AFTERUPDATE触发器（二）创建DML触发器48USELibraryGOCREATETRIGGERT_Return--创建触发器ONBorrow--基于表“Borrow”AFTERUPDATE--在更新操作之后激发执以下语句ASBEGINDECLARE@daysint,@dzbhchar(10),@tsbhchar(9),@hsrqdate

2．创建AFTERUPDATE触发器（二）创建DML触发器49--从更新行副本inserted中查询出RID并赋值给变量@dzbh--从更新行副本inserted中查询出BID并赋值给变量@tsbh--从更新行副本inserted中查询出ReturnDate并赋值给局部变量@hsrqSET@dzbh=(SELECTRIDFROMinserted)--读者编号SET@tsbh=(SELECTBIDFROMinserted) --图书编号SET@hsrq=(SELECTReturnDateFROMinserted)--还书日期2．创建AFTERUPDATE触发器（二）创建DML触发器50/*从借阅表中查询出该读者的本次还书日期和应还日期之差,DATEDIFF函数返回ReturnDate−SreturnDate的值，单位为DAY，并赋值给@days*/SELECT@days=DATEDIFF(day,SReturnDate,ReturnDate)FROMBorrowWHERERID=@dzbhANDBID=@tsbhANDReturnDate=@hsrq2．创建AFTERUPDATE触发器（二）创建DML触发器51/*如果@days小于等于0，即ReturnDate<=SReturnDate，没过期，如果@days大于0，即ReturnDate>SReturnDate，过期@days天*/IF@days<=0PRINT'没有过期！'ELSEPRINT'过期'+convert(char(6),@days)+'天'2．创建AFTERUPDATE触发器（二）创建DML触发器52--将读者表中该读者的借阅数量减1UPDATEReaderSETLendnum=Lendnum-1WHERERID=@dzbh--将图书表“Book”中该书是否借出置为0UPDATEBookSETLentOut=0WHEREBID=@tsbhEND2．创建AFTERUPDATE触发器（二）创建DML触发器53假设：执行UPDATE…SET语句完成读者还书，假设系统日期为2023-11-30，更新借阅表“Borrow”的代码为：UPDATEBorrowSETReturnDate=GETDATE()WHERERID=‘2023216008'ANDBID='F275.3/65'ANDReturnDateISNULLUPDATE…SET执行结果更新了表“Borrow”中还书读者的还书日期“ReturnDate”为’2023-11-30'。2．创建AFTERUPDATE触发器（二）创建DML触发器54调用触发器“T_Return”返回结果过期3天(1行受影响)对于读者表“Reader”，读者编号为2023216008的借阅数量“Lendnum”减1变为0，从而保证了数据的一致性。2．创建AFTERUPDATE触发器（二）创建DML触发器55图书表“Book”，图书编号为‘F275.3/65’的图书是否借出列“LendOut”置为假（0，'False‘）。2．创建AFTERUPDATE触发器（二）创建DML触发器56【例10-7】删除某位读者信息。若要删除一名读者要先检查该读者是否有书没还，若该读者还有书没还则不能被删除。代码：USELibraryGOCREATETRIGGERT_ReaderDEL--创建触发器ONReader --基于表ReaderAFTERDELETE--在删除后触发AS3．创建AFTERDELETE触发器（二）创建DML触发器57BEGINDECLARE@LNumint--从删除的数据行的临时表中获得借阅数量SELECT@LNum=LendnumFROMDELETEDIF@LNum>0 --如果借阅数量大于0BEGINPRINT'该读者不能删除！还有

‘+convert(char(2),@LNum)+’册书未还。'

ROLLBACK --事务回滚撤销所删除的数据行ENDELSE--如果借阅数量不大于0PRINT'该读者已被删除！！！'--显示数据行已删除END3．创建AFTERDELETE触发器（二）创建DML触发器58假设：删除某读者。DELETEReaderWHERERID=‘2004216010’执行结果：该读者不能被删除！还有1册书未还。事务在触发器中结束。批处理已中止。查看读者表：可见该读者未被删除。3．创建AFTERDELETE触发器（二）创建DML触发器59【例10-8】删除图书保护。在图书馆图书处理过程中，不允许随意删除表“Book”中的图书。创建表“Book”的INSTEADOF触发器“T_BookNoDEL”代码：USELibraryGO4．创建INSTEADOF触发器（二）创建DML触发器60CREATETRIGGERT_BookNoDEL--创建INSTEAD触发器ONBook --基于表BookINSTEADOFDELETE --替代触发事件DELETEASBEGINPRINT'图书未被删除！' --显示数据行，图书未被删除END4．创建INSTEADOF触发器（二）创建DML触发器61假设：删除某图书。DELETEBookWHEREBID='TP311.138/235‘执行结果：图书未被删除！(1行受影响)查看表“Book”，可见BID为‘TP311.138/235’的图书未被删除。

（4）创建事件的INSTEADOF触发器（三）创建DDL触发器62基本语法：CREATETRIGGER触发器名ON{ALL

SERVER|DATABASE}[WITHENCRYPTION]{AFTER}<事件类型或事件组>[,...n]AS[BEGIN]T-SQL语句[END]CREATE、ALTER和DROP触发（三）创建DDL触发器63参数说明ALLSERVER：将DDL触发器的作用域应用于当前服务器。指定此参数，则当前服务器中的任何位置上出现事件类型或事件组，就会激发该触发器。DATABASE：将DDL触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现事件类型或事件组，就会激发该触发器。WITHENCRYPTION：对CREATETRIGGER语句的文本进行加密。CREATE、ALTER和DROP触发（三）创建DDL触发器64参数说明事件类型：导致激发DDL触发器的T-SQL语句事件的名称。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE

CREATE_PROCEDURE等操作。事件组：预定义的T-SQL语句事件分组的名称。执行任何属于事件组的T-SQL语句事件之后，都将激发DDL触发器。T-SQL语句：指定触发器所执行的T-SQL语句。CREATE、ALTER和DROP触发（三）创建DDL触发器65触发器对象位置说明在SSMS的【对象资源管理器】窗口中，服务器作用域的DDL触发器显示在“服务器对象”节点中；具有数据库作用域的DDL触发器位于相应数据库节点下的“可编程性”节点下的“数据库触发器”节点下。CREATE、ALTER和DROP触发（三）创建DDL触发器66【例10-9】使用DDL触发器来防止数据库“Library”中的任意一个表被修改或删除。代码:USELibraryGOCREATETRIGGERsafety1ONDATABASEFORDROP_TABLE,ALTER_TABLEASBEGINPRINT'要删除和修改表之前，你必须先禁用触发器safety1!'ROLLBACKENDCREATE、ALTER和DROP触发（三）创建DDL触发器67执行结果：新建触发器“safety1”

。作用：当用户试图使用DROP或ALTER语句删除或修改数据库“Library”中的表时，调用此DDL触发器，此触发器的事务回滚语句ROLLBACK将撤销DROP或ALTER语句的执行。CREATE、ALTER和DROP触发（三）创建DDL触发器68【例10-10】在服务器上创建DDL触发器来防止服务器中的任意一个数据库被修改或删除。代码:CREATETRIGGERsafety2ONALLSERVERFORDROP_DATABASE,ALTER_DATABASEASBEGINPRINT'要删除和修改数据库之前，你必须先禁用触发器safety2!'ROLLBACKENDCREATE、ALTER和DROP触发（三）创建DDL触发器69执行结果：新建触发器“safety2”

。作用：当用户试图使用DROP或ALTER命令删除或修改服务器中的数据库时，调用此DDL触发器，此触发器的事务回滚语句ROLLBACK将撤销DROP或ALTER命令的执行。CREATE、ALTER和DROP触发（四）管理触发器70在【对象资源管理器】窗口中，展开“数据库”→“具体数据库”→“具体表”节点→“触发器”节点，击“触发器”节点，从快捷菜单中选择“修改”命令使用SSMS管理触发器（四）管理触发器71在【查询编辑器】中即可对打开的触发器进行修改使用SSMS管理触发器（四）管理触发器72修改DML触发器。语法如下：ALTERTRIGGER<触发器名>ON<表名|视图名>｛AFTER|INSTEADOF｝[UPDATE][,][INSERT][,][DELETE]AST-SQL语句1.使用T-SQL修改DML触发器（四）管理触发器73修改DDL触发器。语法如下：ALTERTRIGGER<触发器名>ON｛ALLSERVER|DATABASE｝[WITHENCRYPTION]｛AFTER｝<事件类型或事件组>[,…n]AST-SQL语句2.使用T-SQL修改DDL触发器（四）管理触发器74删除触发器。语法如下：DROPTRIGGER<触发器名>3.使用T-SQL删除触发器（四）管理触发器75禁用触发器。语法如下：DISABLETRIGGER触发器名ON对象名|DATABASE|ALLServer启用触发器。语法如下：ENABLETRIGGER触发器名ON对象名|DATABASE|ALLServer4.使用T-SQL禁用与启用触发器（四）管理触发器76【例10-11】禁用DDL触发器“safety1”和“safety2”，以便进行表的修改和删除。代码如下：DISABLETRIGGERsafety1ONDATABASEGODISABLETRIGGERsafety2ONALLSERVER2.使用T-SQL管理触发器单元2.8T-SQL程序设计77创建与管理存储过程一创建与管理触发器二创建与管理用户定义函数三三、创建与管理用户定义函数78案例2-10-3图书管理用户定义函数的创建与管理根据图书管理系统的功能需求，在数据库“Library”中创建用户定义函数。工作任务（一）用户定义函数概述79用户定义函数一组编译好的、存储在数据库服务器上的和完成特定功能的T-SQL程序，是某数据库的对象。可以将一个或多个T-SQL语句的子程序定义成函数，从而实现代码的封装和重用。用户定义函数（UserDefinedFunction，UDF）可以有多个输入参数并返回标量（常量）或表值，不支持输出参数。1.用户定义函数的概念（一）用户定义函数概述80用户定义函数的优点允许模块化程序设计。能够实现较快的执行速度。能够减少网络流量。1.用户定义函数的概念（一）用户定义函数概述81用户定义函数与存储过程的比较2.用户定义函数的优点项

目用户定义函数存储过程参数允许多个输入参数，不允许输出参数允许多个输入/输出参数返回值有且只有一个返回值，可以返回标量或表值可以没有返回值，不能返回表值调用在表达式中引用，可以嵌入在查询语句的表达式中调用必须单独调用（一）用户定义函数概述82标量值函数返回的是在RETURNS子句中定义类型的标量表达式的值（单个数据值）。3.用户定义函数的分类（一）用户定义函数概述83表值函数：返回的是在RETURNS子句中指定的“TABLE”类型的数据行集（表值）。内联表值函数：没有函数体，RETURN子句在括号中含有一条单独的SELECT查询语句。多语句表值函数：在BEGIN…END语句块中定义的函数体包含一系列T-SQL语句。2.用户定义函数的分类（二）创建用户定义函数84基本语法CREATEFUNCTION函数名 --创建标量函数([@形参数据类型][,...n]) --括号内输入参数RETURNS返回数据类型

--定义返回标量值的数据类型ASBEGINT-SQL语句

--函数体RETURN标量表达式

--返回RETURNS子句中定义的数据类型的单个数据值END1.创建标量值函数（二）创建用户定义函数85【例10-12】创建标量值函数“fn_price”，价格高于50元的书认为是较贵的图书，否则认为是便宜的图书，实现对图书价格的高与低的评价。代码:USELibraryGOCREATEFUNCTIONfn_price(@priceinputmoney)RETURNSnvarchar(5) --函数返回nvarchar(5)类型标量AS1.创建标量值函数（二）创建用户定义函数86代码:BEGINDECLARE@returnstrnvarchar(5)IF@priceinput>50 --如果输入参数的值大于50SET@returnstr=‘较贵的图书’--赋值‘较贵的图书’ELSESET@returnstr=‘便宜的图书’--否则赋值'便宜的图书'RETURN@returnstr--返回字符串标量值END1.创建标量值函数（二）创建用户定义函数87执行结果：新建用户定义函数“fn_price”

。函数功能：当函数被调用时，参数如果大于50，则输出较贵的图书，否则输出较便宜的图书。1.创建标量值函数（二）创建用户定义函数88调用举例：在表达式中使用函数:SELECTBID,Bname,Author,Price,dbo.fn_price(Price)FROMBook--调用自定义函数执行结果：1.创建标量值函数（二）创建用户定义函数89基本语法CREATEFUNCTION函数名([@形参名数据类型][,...n]) --括号内输入参数RETURNSTABLE --定义返回值为表ASBEGINRETURN(SELECT查询语句)--返回查询结果的数据行集END2.创建内联表值函数（二）创建用户定义函数90【例10-13】创建用户定义内联表值函数“fn_Publisher”，根据指定的出版社查询该出版社出版的图书，返回结果数据行集。代码:USELibraryGOCREATEFUNCTIONfn_Publisher(@Publishervarchar(30))RETURNSTABLE--函数返回值为表值ASRETURN(SELECTBID,Bname,Author,Publisher