CREATE FUNCTION sqlserver用戶定義函數-2023修改整理

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

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

用戶定義函數用ALTERFUNCTION修改，用DROPFUNCTION除去。

語法

標量函數

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

內嵌表值函數

CREATEFUNCTION[owner_name.]function_name

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

RETURNSTABLE

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

[AS]

RETURN[(]select-stmt[)]

多語句表值函數

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])

參數

owner_name

擁有該用戶定義函數的用戶ID的名稱。owner_name必需是現有的用戶ID。function_name

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

@parameter_name

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

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

scalar_parameter_data_type

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

scalar_return_data_type

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

scalar_expression

指定標量函數返回的標量值。

TABLE

指定表值函數的返回值為表。

在內嵌表值函數中，通過單個SELECT語句定義TABLE返回值。內嵌函數沒有相關聯的返回變量。

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

function_body

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

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

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

Transact-SQL語句。

select-stmt

是定義內嵌表值函數返回值的單個SELECT語句。

ENCRYPTION

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

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

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

除去了函數。

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

惟獨在滿足以下條件時，函數才干綁定到架構：

該函數所引用的用戶定義函數和視圖也已綁定到架構。

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

該函數及其引用的對象屬于同一數據庫。

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

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

解釋

用戶定義函數為標量值函數或表值函數。假如RETURNS子句指定一種標量數據類型，則函數為標量值函數?？梢允褂枚鄺lTransact-SQL語句定義標量值函數。假如RETURNS子句指定TABLE，則函數為表值函數。按照函數主體的定義方式，表值函數可分為行內函數或多語句函數。

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

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

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

賦值語句。

控制流語句。

DECLARE語句，該語句定義函數局部的數據變量和游標。

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

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

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

EXECUTE語句調用擴展存儲過程。

函數確實定性和副作用

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

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

存儲過程時仍會產生副作用。

因為擴展存儲過程會對數據庫產生副作用，因此調用擴展存儲過程的函數是不確定的。當用戶定義函數調用會對數據庫產生副作用的擴展存儲過程時，不要指望結果集保持全都或執(zhí)行函數。

從函數中調用擴展存儲過程

從函數內部調用時擴展存儲過程無法向客戶端返回結果集。任何向客戶端返回結果集的ODSAPI都將返回FAIL。擴展存儲過程可以銜接回Microsoft?SQLServer?；但是，它不應嘗試聯接與喚醒調用擴展存儲過程的函數相同的事務。

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

函數調用

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

定義。當喚醒調用標量值函數時，至少應使用函數的兩部分名稱。

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

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

可以使用由一部分組成的名稱喚醒調用表值函數。

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

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

SELECT*

FROM::fn_helpcollations()

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

權限

用戶應具有執(zhí)行CREATEFUNCTION語句的CREATEFUNCTION權限。

CREATEFUNCTION的權限默認地授予sysadmin固定服務器角色和db_owner

和db_ddladmin固定數據庫角色的成員。sysadmin和db_owner的成員可用GRANT語句將CREATEFUNCTION權限授予其它登錄。

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

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

示例

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

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

復制代碼代碼如下:

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

下面是函數調用。注重DATEFIRST設置為1。

SETDATEFIRST1

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

下面是結果集。

ISOWeek

52

B.內嵌表值函數

下例返回內嵌表值函數。

復制代碼代碼如下:

USEpubs

GO

CREATEFUNCTIONSalesByStore(@storeidvarchar(30))RETURNSTABLE

AS

RETURN(SELECTtitle,qty

FROMsaless,titlest

WHEREs.stor_id=@storeidand

t.title_id=s.title_id)

C.多語句表值函數

假設有一個表代表如下的層次關系：

復制代碼代碼如下:

CREATETABLEemployees(empidnchar(5)PRIMARYKEY,

empnamenvarchar(50),

mgridnchar(5)REFERENCESemployees(empid),

titlenvarchar(30)

)

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

復制代碼代碼如下:

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