第十章用戶自定義函數(shù)

1什么是函數(shù)？

是一組編譯好的Transact-SQL語句，他們可以帶一個或一組數(shù)值做參數(shù)，也可以不帶參數(shù)，它返回一個數(shù)值、數(shù)值集合，或執(zhí)行一些操作。函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫代碼。2函數(shù)類型：

SQLServer2005支持用戶定義函數(shù)和內(nèi)置函數(shù)。內(nèi)置函數(shù)

是一組預(yù)定義的函數(shù)，是Transact-SQL語言的一部分，按Transact-SQL參考中定義的方式運行且不能修改。310.1用戶自定義函數(shù)概述用戶定義函數(shù)是用戶定義的Transact-SQL函數(shù)，它可以擁有零個、一個或多個參數(shù)，必須返回單一的返回值。返回值可以是單個數(shù)值，也可以是一個表。4用戶定義函數(shù)種類返回可更新數(shù)據(jù)表的函數(shù)返回不可更新數(shù)據(jù)表的函數(shù)返回標(biāo)量值的函數(shù)若函數(shù)含單個SELECT語句且可更新，則返回的數(shù)據(jù)表可更新若函數(shù)含多個SELECT語句或一個不可更新的SELECT語句，則返回的數(shù)據(jù)表不可更新510.2創(chuàng)建用戶自定義函數(shù)10.2.1 創(chuàng)建用戶自定義函數(shù)CREATE

FUNCTION[ower-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])參數(shù)名，局部變量參數(shù)數(shù)據(jù)類型6RETURNSscalar_return_datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND用戶自定義函數(shù)的返回值函數(shù)中返回值的表達式7SCHEMABINING選項可將函數(shù)綁定到它所引用的數(shù)據(jù)庫對象function-optionENCRYPTION選項可以實現(xiàn)SQLServer加密包含CREATE

FUNCTION語句文本的系統(tǒng)表列8例：在Northwind庫上創(chuàng)建自定義函數(shù)my_function1，該函數(shù)實現(xiàn)輸入代表商品年銷售額的money類型參數(shù)的值后返回字符串，如果年銷售額大于10000，返回“熱銷商品”，否則返回“非熱銷商品”9USENorthwindgoCREATE

FUNCTION

my_function1

(@moneyinputmoney)RETURNSnvarchar(5)10BEGINDECLARE@returnstringnvarchar(5)IF@moneyinput<10000SET@returnstring='非熱銷商品'ELSESET@returnstring='熱銷商品'RETURN@returnstringEND11在Northwind庫上有一個記錄各種產(chǎn)品在1997年銷售額的視圖，通過它來引用新建的my_function1，查看哪些商品屬于熱銷商品USENorthwindgoSELECT

ProductName,ProductSales,dbo.my_function1(ProductSales)AS

DiscriptionFROM[Productsalesfor1997]WHERECategoryName=’DairyProducts’121310.2.2 查看用戶自定義函數(shù)自定義函數(shù)的名稱保存在sysobjects系統(tǒng)表中創(chuàng)建自定義函數(shù)的源代碼保存在syscomments系統(tǒng)表中141.使用系統(tǒng)存儲過程查看EXEC

sp_help(sp_helptext)<function-name>例：用系統(tǒng)存儲過程sp_helptext

查看用戶自定義函數(shù)my_funciton1的定義文本信息USENorthwindgoEXECsp_helptextmy_function1go15162.使用系統(tǒng)表或系統(tǒng)信息架構(gòu)視圖在SQL

Server中存在三個信息架構(gòu)視圖報告有關(guān)用戶自定義函數(shù)的信息ROUTINESPARAMETERSROUTINE_COLUMNS這些信息架構(gòu)視圖也是基于系統(tǒng)表sysobjects和syscomments實現(xiàn)的17例：使用系統(tǒng)表sysobjects查看數(shù)據(jù)庫Northwind上存在的所有用戶自定義函數(shù)的相關(guān)信息。

USENorthwindSELECT*fromsysobjectsWHEREtype='FN'go1810.3用戶自定義函數(shù)類型自定義函數(shù)有三種類型標(biāo)量函數(shù)內(nèi)嵌表值函數(shù)多語句表值函數(shù)1910.3.1標(biāo)量函數(shù)標(biāo)量函數(shù)返回在RETURNS子句中定義的數(shù)據(jù)類型的單個數(shù)據(jù)值標(biāo)量函數(shù)可重復(fù)調(diào)用20例：創(chuàng)建標(biāo)量函數(shù)，要求將當(dāng)前系統(tǒng)日期轉(zhuǎn)化為年月日格式的字符串并返回，且默認的分隔符為‘

::’，并允許用戶自行定義分隔符21CREATE

FUNCTIONmy_function2(@DATEdatetime,

@separatornvarchar(2)='::')RETURNSnvarchar(20)BEGINDECLARE

@returnstringnvarchar(20)22SET@returnstring='今天是'+CONVERT(nvarchar(5),

datepart(year,@date))+‘年’

+@separator+CONVERT(nvarchar(5),

datepart(month,@date))+‘月’

+@separator+CONVERT(nvarchar(5),

datepart(day,@date))+'日'RETURN@returnstringEND23如何調(diào)用新建立的標(biāo)量函數(shù)呢？可以運行如下命令：

SELECT

dbo.my_function2(GETDATE(),’--‘)返回當(dāng)天的年、月和日2410.3.2內(nèi)嵌表值函數(shù)在內(nèi)嵌表值函數(shù)中，RETURN子句中包含有一條單獨的SELECT語句，該語句的結(jié)果構(gòu)成了內(nèi)嵌表值函數(shù)所返回的表可替代視圖，可用在T-SQL查詢中允許表或視圖表達式的地方25視圖：受限于單個SELECT

語句，不允許包含用戶自己提供的參數(shù)內(nèi)嵌表值函數(shù)：可包含附加的語句，使函數(shù)所包含的邏輯比視圖的邏輯更強返回表的內(nèi)嵌表值函數(shù)還可替換返回單個結(jié)果集的存儲過程26CREATEFUNCTION[ower-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])27RETURNSTABLE[WITH<function-option>[,…n]][AS]RETURNSELECT_STATEMENT定義內(nèi)嵌表值函數(shù)返回值返回值的單個select語句

指定表值函數(shù)的返回值為表28例:

在Northwind數(shù)據(jù)庫上建立一個可以根據(jù)輸入的城市名返回所有該城市的客戶和供應(yīng)商的信息的內(nèi)嵌表值函數(shù)my_function329USENorthwindGoCREATE

FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableAS30RETURN(SELECTCity,CompanyName,ContactName,

'Customers'AS

Relationship

FROMCustomersWHERECity=@RegionParameterUNIONSELECTCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=@RegionParameter)31下面示例使用新建立的內(nèi)嵌表值函數(shù)my_function3來獲取“巴黎”市的所有客戶和供應(yīng)商的信息

SELECT*FROMdbo.my_function3('Paris')go

32如果用戶想通過視圖“CustomerandSuppliersbyCity”來實現(xiàn)同樣的目的，則使用如下查詢命令：

SELECT*FROM[CustomerandSuppliersbyCity]WHERECity='Paris'go3310.3.3多語句表值函數(shù)多語句表值函數(shù)的主體中允許使用的語句賦值控制流DECLARESELECT游標(biāo)操作INSERTUPDATEDELETEEXECUTE34CREATEFUNCTION[ower-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])35RETURNS@return_variableTABLE<table_type_definition>[WITH<function-option>[,…n]][AS]BEGIN

function_bodyRETURNEND函數(shù)體，由sql語句組成，返回表變量是TABLE變量，用于存儲作為函數(shù)值返回的行36TABLE<table_type_definition>::=({column_definition|table_constraint}[,…])表的列聲明表約束37例:

在Northwind庫上利用視圖Customer

andSuppliersbyCity，建立一個可以根據(jù)輸入的城市名和用戶要求返回所有該城市的客戶或供應(yīng)商的信息的多語句表值函數(shù)my_function4，并將其與上例建立的內(nèi)嵌表值函數(shù)my_function3對比38USENorthwindGOCREATEFUNCTIONmy_function4(@RegionParameternvarchar(25),@SelectParameterchar(9))RETURNS@my_tableTABLE(Citynvarchar(15)NULL,

CompanyNamenvarchar(40)NOT

NULL,

ContactNamenvarchar(30)NULL)39ASBEGININSERT@my_tableSELECTCity,

CompanyName,ContactName

FROM[Customer

andSuppliersbyCity]WHERECity=@RegionParameter

AND

Relationship=@SelectParameterRETURNEND40下面舉例說明使用多語句表值函數(shù)my_function4分別獲取來自Paris的所有客戶和供應(yīng)商的信息。命令如下：SELECT*FROMdbo.my_function4('Paris','Customers')goSELECT*FROMdbo.my_function4('Paris','Suppliers')go4110.4修改和刪除用戶自定義函數(shù)10.4.1修改用戶自定義函數(shù)ALTER

FUNCTION[ower-name,]function-name([{@parameter-namescalar-parameter-datetype[=default]}[,…n]])RETURNSscalar-return-datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND42例：修改在Northwind庫上創(chuàng)建的自定義函數(shù)my_function1，將區(qū)分商品是否熱銷的年銷售額由當(dāng)初的10000提高到20000ALTER

FUNCTIONmy_function1(@moneyinputmone