函數(shù)的創(chuàng)建與管理

函數(shù)的創(chuàng)建與管理第一頁，共二十七頁，2022年，8月28日函數(shù)是接受參數(shù)、執(zhí)行操作（例如復(fù)雜計算）并將操作結(jié)果以值的形式返回的例程。返回值可以是單個標(biāo)量值或結(jié)果集。SQLServer2005中有多種函數(shù)，根據(jù)返回值的類型和是否由系統(tǒng)提供，分為標(biāo)量函數(shù)、表值函數(shù)和內(nèi)置函數(shù)。SQLServer2005支持3種用戶定義函數(shù)：標(biāo)量函數(shù)、表值函數(shù)和聚合函數(shù)。本章介紹用戶定義函數(shù)的創(chuàng)建、修改及刪除。第二頁，共二十七頁，2022年，8月28日14.1用戶自定義函數(shù)簡介SQLServer不但提供了系統(tǒng)內(nèi)置函數(shù)，而且還允許用戶根據(jù)實際需要創(chuàng)建用戶自定義函數(shù)。用戶自定義函數(shù)是由一條或多條T-SQL語句組成的子程序，保存在數(shù)據(jù)庫內(nèi)。它可以具有多個輸入?yún)?shù)，并返回一個標(biāo)量值（單個數(shù)據(jù)值）或一個表。

第三頁，共二十七頁，2022年，8月28日用戶自定義函數(shù)有以下3種類型：返回單值的標(biāo)量函數(shù)；類似于視圖的可更新內(nèi)嵌表值函數(shù)；使用代碼創(chuàng)建結(jié)果集的多語句表值函數(shù)。第四頁，共二十七頁，2022年，8月28日14.2.1標(biāo)量函數(shù)14.2.2內(nèi)嵌表值函數(shù)14.2.3多語句表值函數(shù)14.2創(chuàng)建用戶自定義函數(shù)第五頁，共二十七頁，2022年，8月28日標(biāo)量函數(shù)類似于系統(tǒng)內(nèi)置函數(shù)。函數(shù)的輸入?yún)?shù)可以是所有標(biāo)量數(shù)據(jù)類型，輸出參數(shù)的類型可以是除了text、nText、image、cursor、timestamp以外的任何數(shù)據(jù)類型，函數(shù)主體在BEGIN-END塊中定義。14.2.1標(biāo)量函數(shù)第六頁，共二十七頁，2022年，8月28日標(biāo)量函數(shù)標(biāo)量函數(shù)是返回單個值的函數(shù)。標(biāo)量函數(shù)可以接受多個參數(shù)進行計算，并且返回單個值。標(biāo)量函數(shù)一經(jīng)定義后，就可以在SQLServer的表達式（如表的計算列）中使用該函數(shù)。創(chuàng)建標(biāo)量函數(shù)的語法：CREATEFUNCTIONFunctionName(InputParameters)RETURNSDatatypeASBEGINSqlstatementRETURNExpressionEND第七頁，共二十七頁，2022年，8月28日創(chuàng)建一個用戶定義標(biāo)量函數(shù)fsum，其功能為求兩個整數(shù)的和。CREATEFUNCTIONdbo.fsum(@num1INT,@num2INT=6)--參數(shù)@num2默認(rèn)值為6RETURNSINT--函數(shù)返回值為整數(shù)類型ASBEGINRETURN@num1+@num2--返回值表達式ENDGOSELECTdbo.fsum(2,8)SELECTdbo.fsum(7,default)執(zhí)行結(jié)果如下：---------------------------1013第八頁，共二十七頁，2022年，8月28日下面的用戶定義標(biāo)量函數(shù)可以根據(jù)學(xué)生的學(xué)號查詢學(xué)生的出生日期，由此計算出并返回學(xué)生的年齡值。--參數(shù)@sid為學(xué)生學(xué)號，@year為當(dāng)前年份CREATEFUNCTIONdbo.fage(@sidchar(10),@yearINT=2004)RETURNSINT--函數(shù)返回值為整數(shù)類型ASBEGINDECLARE@birdateDATETIMESELECT@birdate=stu_birthdateFROMStudentWHEREstu_id=@sidRETURN@year-YEAR(@birdate)--返回值表達式ENDGOSELECTdbo.fage('0901001',2004)SELECTdbo.fage('0901002',default)執(zhí)行結(jié)果如下：---------------------------2624第九頁，共二十七頁，2022年，8月28日查看定義：select*fromsys.sql_modules

或selectdefinitionfromsys.sql_modules

whereobject_id=OBJECT_ID(‘fsum’)第十頁，共二十七頁，2022年，8月28日例14-1useBlueSkyDBgocreatefunctionFunBookSale(@bookIDint)returnintasbegindeclare@quantityintselect@quantity=sum(quantity)fromOrdreItemswherebookID=@bookIDif(@quantityisnull) set@quantity=0return@quantityendgo第十一頁，共二十七頁，2022年，8月28日useBlueSkyDBgoselect*,dbo.FunBookSale(bookID)asSaleQuantityfromBooksgo第十二頁，共二十七頁，2022年，8月28日調(diào)用標(biāo)量函數(shù)變量函數(shù)注意事項需要說明的是，標(biāo)量函數(shù)必須是確定性的，這意味著，如果使用同樣的輸入?yún)?shù)反復(fù)調(diào)用它，它每次都應(yīng)當(dāng)返回同樣的結(jié)果值。不能在標(biāo)量函數(shù)中使用返回可變數(shù)據(jù)的函數(shù)和全局變量，如@@connections、getgate()、newid()等。在表達式中任何可以使用單個值（數(shù)據(jù)類型相同）的地方都可以使用標(biāo)量函數(shù)。調(diào)用用戶定義標(biāo)量函數(shù)時，必須始終使用兩個部分構(gòu)成的名字（owner.functionname）。第十三頁，共二十七頁，2022年，8月28日14.2.2內(nèi)嵌表值函數(shù)第二種用戶定義函數(shù)是內(nèi)嵌表值函數(shù)。內(nèi)嵌表值函數(shù)和視圖類似，都包含有一條存儲的SELECT語句。內(nèi)嵌表值函數(shù)可以使用參數(shù)，也可以不使用參數(shù)。第十四頁，共二十七頁，2022年，8月28日創(chuàng)建內(nèi)嵌表值函數(shù)創(chuàng)建用戶定義內(nèi)嵌表值函數(shù)的語法格式：CREATEFUNCTIONFunctionName(InputParameters)RETURNSTableASRETURN(selectstatement)例如：CREATEFUNCTIONfstu1()RETURNSTableASRETURN(SELECTstudent.Stu_id,student.Stu_name,student.Stu_addr,class.class_id,class.class_nameFROMStudentINNERJOINClassONStudent.stu_classid=Class.class_id)第十五頁，共二十七頁，2022年，8月28日調(diào)用內(nèi)嵌表值函數(shù)用戶定義內(nèi)嵌表值函數(shù)創(chuàng)建以后，可以在SELECT語句的FROM子句中調(diào)用它。例如：在SELECT語句的FROM子句中調(diào)用內(nèi)嵌表值函數(shù)。SELECTStu_id,Stu_nameFROMfstu1()執(zhí)行結(jié)果如下：Stu_idStu_name----------------------------0901001張三0901002李四 第一次調(diào)用內(nèi)嵌表值函數(shù)時，系統(tǒng)性能會有明顯的下降，因為系統(tǒng)需要編譯函數(shù)的代碼，并將編譯的結(jié)果存放于內(nèi)存中。一旦編譯完成后，此后對函數(shù)的調(diào)用執(zhí)行速度都將很快。第十六頁，共二十七頁，2022年，8月28日使用參數(shù)與視圖相比，內(nèi)嵌表值函數(shù)的一個優(yōu)點是可以在預(yù)編譯的SELECT語句中使用參數(shù)，而視圖則不能使用參數(shù)，它只能在調(diào)用視圖的SELECT語句中使用WHERE子句來實現(xiàn)。以下舉例說明視圖和函數(shù)在運行時限制結(jié)果集的不同方法。例如：CREATEVIEWV_stuASSELECTStu_id,Stu_name,Stu_classidFROMStudent第十七頁，共二十七頁，2022年，8月28日要查詢某個班級的學(xué)生信息，需要在下面調(diào)用視圖的SELECT語句的WHERE子句的條件中實現(xiàn)：SELECT*FROMV_stuWHEREStu_classid='09計一'執(zhí)行結(jié)果如下：Stu_idStu_nameStu_classid--------------------------------------------0901001張三 09計一 第十八頁，共二十七頁，2022年，8月28日與視圖相比，在函數(shù)中可以通過為預(yù)編譯的SQLSELECT語句傳遞補貼的參數(shù)對返回的結(jié)果集進行限制，如下:創(chuàng)建一個自定義函數(shù)。CREATEFUNCTIONdbo.fstu2(@classidchar(10)=null)RETURNSTableASRETURN(SELECTStu_id,Stu_name,Stu_classidFROMStudentWHEREstu_classid=@classidOR@classidISNULL)如果使用default關(guān)鍵字來調(diào)用該函數(shù)，就會返回對應(yīng)班級的學(xué)生信息：SELECT*FROMdbo.fstu2(DEFAULT)執(zhí)行結(jié)果如下：Stu_idStu_nameStu_classid--------------------------------------------0901001張三 09計一0901002李四 09計二第十九頁，共二十七頁，2022年，8月28日如果通過輸入?yún)?shù)傳遞了班級代碼，函數(shù)內(nèi)預(yù)編譯的SELECT語句就會返回對應(yīng)班級的學(xué)生信息。SELECT*FROMdbo.fstu2('09計一')執(zhí)行結(jié)果如下：Stu_idStu_nameStu_classid--------------------------------------------0901001張三 09計一第二十頁，共二十七頁，2022年，8月28日例14.4useBlueSkyDBgocreatefunctionfunbookselect(@catecorynamenvarchar(50))returnstablewithencryptionasreturn(selectb.*fromBooksbjoinCategoriesconb.categoryCode=c.categoryCodewherec.categoryName=b.categoryName)go第二十一頁，共二十七頁，2022年，8月28日多語句表值函數(shù)

多語句表值用戶定義函數(shù)既可以像標(biāo)量函數(shù)那樣包含復(fù)雜的代碼，也可以像內(nèi)嵌表值函數(shù)那樣返回一個結(jié)果集。多語句表值函數(shù)會創(chuàng)建一個表變量，并使用代碼對它進行填充，然后返回這個表變量，以便在SELECT語句中使用它。第二十二頁，共二十七頁，2022年，8月28日創(chuàng)建多語句表值函數(shù)創(chuàng)建多語句表值函數(shù)的語法格式：CREATEFUNCTIONFunctionName(InputParameters)RETURNS@TableNameTable(Columns)ASBEGINInsertsqlstatementRETURNEND第二十三頁，共二十七頁，2022年，8月28日創(chuàng)建一個多語句表值用戶定義函數(shù)，其作用為將屬于特定系部的學(xué)生信息放入表變量中作為函數(shù)的輸出。CREATEFUNCTIONfstu3(@deptidchar(10))RETURNS@stuTable(stu_idchar(7),stu_namevarchar(10),dept_idchar(10))ASBEGININSERT@stuSELECTStudent.stu_id,Student.stu_name,Dept.idFROMStudent,Class,DeptWHEREStudent.stu_classid=Class.idANDClass.deptid=Dept.idANDDept.id=@deptidRETURNEND第二十四頁，共二十七頁，2022年，8月28日調(diào)用函數(shù)要執(zhí)行多語句表值函數(shù)，可以在SELECT語句的FROM子句中使用它。例如：SELECT*FROMdbo.fstu3('計算機系')執(zhí)行結(jié)果如下：stu_idstu_namedept_id-----------------------------------------0301001張三 計算機系 0301002李四 計算機系第二十五頁，共二十七頁，2022年，8月28日例14.7useBlueSkyDBgocreatefunctionfunorderofcustomer(