CREATE FUNCTION sqlserver用戶定義函數(shù)-2023修改整理

千里之行，始于足下讓知識帶有溫度。第第2頁/共2頁精品文檔推薦CREATEFUNCTIONsqlserver用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)，它是返回值的已保存的Transact-SQL例程。用戶定義函數(shù)不能用于執(zhí)行一組修改全局數(shù)據(jù)庫狀態(tài)的操作。與系統(tǒng)函數(shù)一樣，用戶定義函數(shù)可以從查詢中喚醒調(diào)用。也可以像存儲過程一樣，通過EXECUTE語句執(zhí)行

創(chuàng)建用戶定義函數(shù)，它是返回值的已保存的Transact-SQL例程。用戶定義函數(shù)不能用于執(zhí)行一組修改全局數(shù)據(jù)庫狀態(tài)的操作。與系統(tǒng)函數(shù)一樣，用戶定義函數(shù)可以從查詢中喚醒調(diào)用。也可以像存儲過程一樣，通過EXECUTE語句執(zhí)行。

用戶定義函數(shù)用ALTERFUNCTION修改，用DROPFUNCTION除去。

語法

標量函數(shù)

CREATEFUNCTION[owner_name.]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNSscalar_return_data_type

[WITH[[,]...n]]

[AS]

BEGIN

function_body

RETURNscalar_expression

END

內(nèi)嵌表值函數(shù)

CREATEFUNCTION[owner_name.]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNSTABLE

[WITH[[,]...n]]

[AS]

RETURN[(]select-stmt[)]

多語句表值函數(shù)

CREATEFUNCTION[owner_name.]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNS@return_variableTABLE

[WITH[[,]...n]]

[AS]

BEGIN

function_body

RETURN

END

::=

{ENCRYPTION|SCHEMABINDING}

::=

({column_definition|table_constraint}[,...n])

參數(shù)

owner_name

擁有該用戶定義函數(shù)的用戶ID的名稱。owner_name必需是現(xiàn)有的用戶ID。function_name

用戶定義函數(shù)的名稱。函數(shù)名稱必需符合標識符的規(guī)章，對其全部者來說，該名稱在數(shù)據(jù)庫中必需是唯一的。

@parameter_name

用戶定義函數(shù)的參數(shù)。CREATEFUNCTION語句中可以聲明一個或多個參數(shù)。函數(shù)最多可以有1,024個參數(shù)。函數(shù)執(zhí)行時每個已聲明參數(shù)的值必需由用戶指定，除非該參數(shù)的默認值已經(jīng)定義。假如函數(shù)的參數(shù)有默認值，在調(diào)用該函數(shù)時必需指定"default"關(guān)鍵字才干獲得默認值。這種行為不同于存儲過程中有默認值的參數(shù)，在存儲過程中省略參數(shù)也意味著使用默認值。

使用@符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必需符合標識符的規(guī)章。每個函數(shù)的參數(shù)僅用于該函數(shù)本身；相同的參數(shù)名稱可以用在其它函數(shù)中。參數(shù)只能代替常量；而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。

scalar_parameter_data_type

參數(shù)的數(shù)據(jù)類型。全部標量數(shù)據(jù)類型（包括bigint和sql_variant）都可用作用戶定義函數(shù)的參數(shù)。不支持timestamp數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型。不能指定非標量類型（例如cursor和table）。

scalar_return_data_type

是標量用戶定義函數(shù)的返回值。scalar_return_data_type可以是SQLServer支持的任何標量數(shù)據(jù)類型（text、ntext、image和timestamp除外）。

scalar_expression

指定標量函數(shù)返回的標量值。

TABLE

指定表值函數(shù)的返回值為表。

在內(nèi)嵌表值函數(shù)中，通過單個SELECT語句定義TABLE返回值。內(nèi)嵌函數(shù)沒有相關(guān)聯(lián)的返回變量。

在多語句表值函數(shù)中，@return_variable是TABLE變量，用于存儲和累積應(yīng)作為函數(shù)值返回的行。

function_body

指定一系列Transact-SQL語句定義函數(shù)的值，這些語句合在一起不會產(chǎn)生副作用。function_body只用于標量函數(shù)和多語句表值函數(shù)。

在標量函數(shù)中，function_body是一系列合起來求得標量值的Transact-SQL語句。

在多語句表值函數(shù)中，function_body是一系列填充表返回變量的

Transact-SQL語句。

select-stmt

是定義內(nèi)嵌表值函數(shù)返回值的單個SELECT語句。

ENCRYPTION

指出SQLServer加密包含CREATEFUNCTION語句文本的系統(tǒng)表列。使用ENCRYPTION可以避開將函數(shù)作為SQLServer復(fù)制的一部分發(fā)布。SCHEMABINDING

指定將函數(shù)綁定到它所引用的數(shù)據(jù)庫對象。假如函數(shù)是用SCHEMABINDING選項創(chuàng)建的，則不能更改（使用ALTER語句）或除去（使用DROP語句）該函數(shù)引用的數(shù)據(jù)庫對象。

函數(shù)與其所引用對象的綁定關(guān)系惟獨在發(fā)生以下兩種狀況之一時才被解除：

除去了函數(shù)。

在未指定SCHEMABINDING選項的狀況下更改了函數(shù)（使用ALTER語句）。

惟獨在滿足以下條件時，函數(shù)才干綁定到架構(gòu)：

該函數(shù)所引用的用戶定義函數(shù)和視圖也已綁定到架構(gòu)。

該函數(shù)所引用的對象不是用兩部分名稱引用的。

該函數(shù)及其引用的對象屬于同一數(shù)據(jù)庫。

執(zhí)行CREATEFUNCTION語句的用戶對全部該函數(shù)所引用的數(shù)據(jù)庫對象都具有REFERENCES權(quán)限。

假如不符合以上條件，則指定了SCHEMABINDING選項的CREATEFUNCTION語句將失敗。

解釋

用戶定義函數(shù)為標量值函數(shù)或表值函數(shù)。假如RETURNS子句指定一種標量數(shù)據(jù)類型，則函數(shù)為標量值函數(shù)。可以使用多條Transact-SQL語句定義標量值函數(shù)。假如RETURNS子句指定TABLE，則函數(shù)為表值函數(shù)。按照函數(shù)主體的定義方式，表值函數(shù)可分為行內(nèi)函數(shù)或多語句函數(shù)。

假如RETURNS子句指定的TABLE不附帶列的列表，則該函數(shù)為行內(nèi)函數(shù)。行內(nèi)函數(shù)是使用單個SELECT語句定義的表值函數(shù)，該語句組成了函數(shù)的主體。該函數(shù)返回的表的列（包括數(shù)據(jù)類型）來自定義該函數(shù)的SELECT語句的SELECT列表。

假如RETURNS子句指定的TABLE類型帶有列及其數(shù)據(jù)類型，則該函數(shù)是多語句表值函數(shù)。

多語句函數(shù)的主體中允許使用以下語句。未在下面的列表中列出的語句不能用在函數(shù)主體中。

賦值語句。

控制流語句。

DECLARE語句，該語句定義函數(shù)局部的數(shù)據(jù)變量和游標。

SELECT語句，該語句包含帶有表達式的挑選列表，其中的表達式將值給予函數(shù)的局部變量。

游標操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標。只允許使用以INTO子句向局部變量賦值的FETCH語句；不允許使用將數(shù)據(jù)返回到客戶端的FETCH語句。

INSERT、UPDATE和DELETE語句，這些語句修改函數(shù)的局部table變量。

EXECUTE語句調(diào)用擴展存儲過程。

函數(shù)確實定性和副作用

函數(shù)可以是確定的或不確定的。假如任何時候用一組特定的輸入值調(diào)用函數(shù)時返回的結(jié)果總是相同的，則這些函數(shù)為確定的。假如每次調(diào)用函數(shù)時即使用的是相同的一組特定輸入值，返回的結(jié)果總是不同的，則這些函數(shù)為不確定的。

不確定的函數(shù)會產(chǎn)生副作用。副作用是更改數(shù)據(jù)庫的某些全局狀態(tài)，比如更新數(shù)據(jù)庫表或某些外部資源，如文件或網(wǎng)絡(luò)等（例如，修改文件或發(fā)送電子郵件消息）。

存儲過程時仍會產(chǎn)生副作用。

因為擴展存儲過程會對數(shù)據(jù)庫產(chǎn)生副作用，因此調(diào)用擴展存儲過程的函數(shù)是不確定的。當(dāng)用戶定義函數(shù)調(diào)用會對數(shù)據(jù)庫產(chǎn)生副作用的擴展存儲過程時，不要指望結(jié)果集保持全都或執(zhí)行函數(shù)。

從函數(shù)中調(diào)用擴展存儲過程

從函數(shù)內(nèi)部調(diào)用時擴展存儲過程無法向客戶端返回結(jié)果集。任何向客戶端返回結(jié)果集的ODSAPI都將返回FAIL。擴展存儲過程可以銜接回Microsoft?SQLServer?；但是，它不應(yīng)嘗試聯(lián)接與喚醒調(diào)用擴展存儲過程的函數(shù)相同的事務(wù)。

與從批處理或存儲過程中喚醒調(diào)用相像，擴展存儲過程在運行SQLServer的Windows?平安帳戶的上下文中執(zhí)行。存儲過程的全部者在授予用戶EXECUTE特權(quán)時應(yīng)考慮這一點。

函數(shù)調(diào)用

在可使用標量表達式的位置可喚醒調(diào)用標量值函數(shù)，包括計算列和CHECK約束

定義。當(dāng)喚醒調(diào)用標量值函數(shù)時，至少應(yīng)使用函數(shù)的兩部分名稱。

[database_name.]owner_name.function_name([argument_expr][,...])

假如用戶定義函數(shù)用于定義計算列，則該函數(shù)確實定性同樣打算了是否可在該計算列上創(chuàng)建索引。惟獨當(dāng)函數(shù)具有確定性時，才可以在使用該函數(shù)的計算列上創(chuàng)建索引。假如在輸入相同的狀況下函數(shù)始終返回相同的值，則該函數(shù)具有確定性。

可以使用由一部分組成的名稱喚醒調(diào)用表值函數(shù)。

[database_name.][owner_name.]function_name([argument_expr][,...])

對于Microsoft?SQLServer?2000中包含的系統(tǒng)表函數(shù)，喚醒調(diào)用時需在函數(shù)名的前面加上前綴"::"。

SELECT*

FROM::fn_helpcollations()

對于導(dǎo)致語句停止執(zhí)行然后從存儲過程中的下一語句繼續(xù)執(zhí)行的Transact-SQL錯誤，在函數(shù)中的處理方式不同。在函數(shù)中，這類錯誤解導(dǎo)致函數(shù)停止執(zhí)行。這反過來使喚醒調(diào)用該函數(shù)的語句停止執(zhí)行。

權(quán)限

用戶應(yīng)具有執(zhí)行CREATEFUNCTION語句的CREATEFUNCTION權(quán)限。

CREATEFUNCTION的權(quán)限默認地授予sysadmin固定服務(wù)器角色和db_owner

和db_ddladmin固定數(shù)據(jù)庫角色的成員。sysadmin和db_owner的成員可用GRANT語句將CREATEFUNCTION權(quán)限授予其它登錄。

函數(shù)的全部者對其函數(shù)具有EXECUTE權(quán)限。其他用戶不具有EXECUTE權(quán)限，除非給他們授予了特定函數(shù)上的EXECUTE權(quán)限。

若要創(chuàng)建或更改在CONSTRAINT、DEFAULT子句或計算列定義中引用了用戶定義函數(shù)的表，用戶還必需對這些函數(shù)有REFERENCES權(quán)限。

示例

A.計算ISO周的標量值用戶定義函數(shù)

下例中，用戶定義函數(shù)ISOweek取日期參數(shù)并計算ISO周數(shù)。為了正確計算該函數(shù)，必需在調(diào)用該函數(shù)前喚醒調(diào)用SETDATEFIRST1。

復(fù)制代碼代碼如下:

CREATEFUNCTIONISOweek(@DATEdatetime)

RETURNSint

AS

BEGIN

DECLARE@ISOweekint

SET@ISOweek=DATEPART(wk,@DATE)+1

-DATEPART(wk,CAST(DATEPART(yy,@DATE)asCHAR(4))+'0104')

--Specialcases:Jan1-3maybelongtothepreviousyear

IF(@ISOweek=0)

SET@ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1

ASCHAR(4))+'12'+CAST(24+DATEPART(DAY,@DATE)ASCHAR(2)))+1--Specialcase:Dec29-31maybelongtothenextyear

IF((DATEPART(mm,@DATE)=12)AND

((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>=28))

SET@ISOweek=1

RETURN(@ISOweek)

END

下面是函數(shù)調(diào)用。注重DATEFIRST設(shè)置為1。

SETDATEFIRST1

SELECTmaster.dbo.ISOweek('12/26/1999')AS'ISOWeek'

下面是結(jié)果集。

ISOWeek

52

B.內(nèi)嵌表值函數(shù)

下例返回內(nèi)嵌表值函數(shù)。

復(fù)制代碼代碼如下:

USEpubs

GO

CREATEFUNCTIONSalesByStore(@storeidvarchar(30))RETURNSTABLE

AS

RETURN(SELECTtitle,qty

FROMsaless,titlest

WHEREs.stor_id=@storeidand

t.title_id=s.title_id)

C.多語句表值函數(shù)

假設(shè)有一個表代表如下的層次關(guān)系：

復(fù)制代碼代碼如下:

CREATETABLEemployees(empidnchar(5)PRIMARYKEY,

empnamenvarchar(50),

mgridnchar(5)REFERENCESemployees(empid),

titlenvarchar(30)

)

表值函數(shù)fn_FindReports(InEmpID)有一個給定的職員ID，它返回與全部直接或間接向給定職員報告的職員相對應(yīng)的表。該規(guī)律無法在單個查詢中表現(xiàn)出來，不過可以實現(xiàn)為用戶定義函數(shù)。

復(fù)制代碼代碼如下:

CREATEFUNCTIONfn_FindReports(@InEmpIdnchar(5))

RETURNS@retFindReportsTABLE(empidnchar(5)primarykey,

empnamenvarchar(50)NOTNULL,

mgridnchar(5),

titlenvarchar(30))

/*Returnsaresultsetthatlistsalltheemployeeswhoreporttogivenemployeedirectlyorindirectly.*/

AS

BEGIN

DECLARE@RowsAddedint

--tablevariabletoholdaccumulatedresults

DECLARE@reportsTABLE(empidnchar(5)primarykey,

empnamenvarchar(50)NOTNULL,

mgridnchar(5),

titlenvarchar(30),

processedtinyintdefault0)

--initialize@ReportswithdirectreportsofthegivenemployeeINSERT@reports

SELECTempid,empname,mgrid,title,0

FROMemployees

WHEREempid=@InEmpId

SET@RowsAdded=@@rowcount

--Whilenewemployeeswereaddedinthepreviousiteration

WHILE@RowsAdded>0

BEGIN.

/*Markallemployeerecordswhosedirectreportsaregoi