CREATE FUNCTION sqlserver用戶定義函數(shù)
創(chuàng)建用戶定義函數(shù),它是返回值的已保存的 Transact-SQL 例程。用戶定義函數(shù)不能用于執(zhí)行一組修改全局?jǐn)?shù)據(jù)庫狀態(tài)的操作。與系統(tǒng)函數(shù)一樣,用戶定義函數(shù)可以從查詢中喚醒調(diào)用。也可以像存儲(chǔ)過程一樣,通過 EXECUTE 語句執(zhí)行。
用戶定義函數(shù)用 ALTER FUNCTION 修改,用 DROP FUNCTION 除去。
語法
標(biāo)量函數(shù)
CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option> [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END
內(nèi)嵌表值函數(shù)
CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]
多語句表值函數(shù)
CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END < function_option > ::= { ENCRYPTION | SCHEMABINDING } < table_type_definition > ::= ( { column_definition | table_constraint } [ ,...n ] )
參數(shù)
owner_name
擁有該用戶定義函數(shù)的用戶 ID 的名稱。owner_name 必須是現(xiàn)有的用戶 ID。
function_name
用戶定義函數(shù)的名稱。函數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則,對(duì)其所有者來說,該名稱在數(shù)據(jù)庫中必須是唯一的。
@parameter_name
用戶定義函數(shù)的參數(shù)。CREATE FUNCTION 語句中可以聲明一個(gè)或多個(gè)參數(shù)。函數(shù)最多可以有 1,024 個(gè)參數(shù)。函數(shù)執(zhí)行時(shí)每個(gè)已聲明參數(shù)的值必須由用戶指定,除非該參數(shù)的默認(rèn)值已經(jīng)定義。 如果函數(shù)的參數(shù)有默認(rèn)值,在調(diào)用該函數(shù)時(shí)必須指定"default"關(guān)鍵字才能獲得默認(rèn)值。這種行為不同于存儲(chǔ)過程中有默認(rèn)值的參數(shù),在存儲(chǔ)過程中省略參數(shù)也意味著使用默認(rèn)值。
使用 @ 符號(hào)作為第一個(gè)字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則。每個(gè)函數(shù)的參數(shù)僅用于該函數(shù)本身;相同的參數(shù)名稱可以用在其它函數(shù)中。參數(shù)只能代替常量;而不能用于代替表名、列名或其它數(shù)據(jù)庫對(duì)象的名稱。
scalar_parameter_data_type
參數(shù)的數(shù)據(jù)類型。所有標(biāo)量數(shù)據(jù)類型(包括 bigint 和 sql_variant)都可用作用戶定義函數(shù)的參數(shù)。不支持 timestamp 數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型。不能指定非標(biāo)量類型(例如 cursor 和 table)。
scalar_return_data_type
是標(biāo)量用戶定義函數(shù)的返回值。scalar_return_data_type 可以是 SQL Server 支持的任何標(biāo)量數(shù)據(jù)類型(text、ntext、image 和 timestamp 除外)。
scalar_expression
指定標(biāo)量函數(shù)返回的標(biāo)量值。
TABLE
指定表值函數(shù)的返回值為表。
在內(nèi)嵌表值函數(shù)中,通過單個(gè) SELECT 語句定義 TABLE 返回值。內(nèi)嵌函數(shù)沒有相關(guān)聯(lián)的返回變量。
在多語句表值函數(shù)中,@return_variable 是 TABLE 變量,用于存儲(chǔ)和累積應(yīng)作為函數(shù)值返回的行。
function_body
指定一系列 Transact-SQL 語句定義函數(shù)的值,這些語句合在一起不會(huì)產(chǎn)生副作用。function_body 只用于標(biāo)量函數(shù)和多語句表值函數(shù)。
在標(biāo)量函數(shù)中,function_body 是一系列合起來求得標(biāo)量值的 Transact-SQL 語句。
在多語句表值函數(shù)中,function_body 是一系列填充表返回變量的 Transact-SQL 語句。
select-stmt
是定義內(nèi)嵌表值函數(shù)返回值的單個(gè) SELECT 語句。
ENCRYPTION
指出 SQL Server 加密包含 CREATE FUNCTION 語句文本的系統(tǒng)表列。使用 ENCRYPTION 可以避免將函數(shù)作為 SQL Server 復(fù)制的一部分發(fā)布。
SCHEMABINDING
指定將函數(shù)綁定到它所引用的數(shù)據(jù)庫對(duì)象。如果函數(shù)是用 SCHEMABINDING 選項(xiàng)創(chuàng)建的,則不能更改(使用 ALTER 語句)或除去(使用 DROP 語句)該函數(shù)引用的數(shù)據(jù)庫對(duì)象。
函數(shù)與其所引用對(duì)象的綁定關(guān)系只有在發(fā)生以下兩種情況之一時(shí)才被解除:
除去了函數(shù)。
在未指定 SCHEMABINDING 選項(xiàng)的情況下更改了函數(shù)(使用 ALTER 語句)。
只有在滿足以下條件時(shí),函數(shù)才能綁定到架構(gòu):
該函數(shù)所引用的用戶定義函數(shù)和視圖也已綁定到架構(gòu)。
該函數(shù)所引用的對(duì)象不是用兩部分名稱引用的。
該函數(shù)及其引用的對(duì)象屬于同一數(shù)據(jù)庫。
執(zhí)行 CREATE FUNCTION 語句的用戶對(duì)所有該函數(shù)所引用的數(shù)據(jù)庫對(duì)象都具有 REFERENCES 權(quán)限。
如果不符合以上條件,則指定了 SCHEMABINDING 選項(xiàng)的 CREATE FUNCTION 語句將失敗。
注釋
用戶定義函數(shù)為標(biāo)量值函數(shù)或表值函數(shù)。如果 RETURNS 子句指定一種標(biāo)量數(shù)據(jù)類型,則函數(shù)為標(biāo)量值函數(shù)。可以使用多條 Transact-SQL 語句定義標(biāo)量值函數(shù)。
如果 RETURNS 子句指定 TABLE,則函數(shù)為表值函數(shù)。根據(jù)函數(shù)主體的定義方式,表值函數(shù)可分為行內(nèi)函數(shù)或多語句函數(shù)。
如果 RETURNS 子句指定的 TABLE 不附帶列的列表,則該函數(shù)為行內(nèi)函數(shù)。行內(nèi)函數(shù)是使用單個(gè) SELECT 語句定義的表值函數(shù),該語句組成了函數(shù)的主體。該函數(shù)返回的表的列(包括數(shù)據(jù)類型)來自定義該函數(shù)的 SELECT 語句的 SELECT 列表。
如果 RETURNS 子句指定的 TABLE 類型帶有列及其數(shù)據(jù)類型,則該函數(shù)是多語句表值函數(shù)。
多語句函數(shù)的主體中允許使用以下語句。未在下面的列表中列出的語句不能用在函數(shù)主體中。
賦值語句。
控制流語句。
DECLARE 語句,該語句定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。
SELECT 語句,該語句包含帶有表達(dá)式的選擇列表,其中的表達(dá)式將值賦予函數(shù)的局部變量。
游標(biāo)操作,該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。只允許使用以 INTO 子句向局部變量賦值的 FETCH 語句;不允許使用將數(shù)據(jù)返回到客戶端的 FETCH 語句。
INSERT、UPDATE 和 DELETE 語句,這些語句修改函數(shù)的局部 table 變量。
EXECUTE 語句調(diào)用擴(kuò)展存儲(chǔ)過程。
函數(shù)的確定性和副作用
函數(shù)可以是確定的或不確定的。如果任何時(shí)候用一組特定的輸入值調(diào)用函數(shù)時(shí)返回的結(jié)果總是相同的,則這些函數(shù)為確定的。如果每次調(diào)用函數(shù)時(shí)即使用的是相同的一組特定輸入值,返回的結(jié)果總是不同的,則這些函數(shù)為不確定的。
不確定的函數(shù)會(huì)產(chǎn)生副作用。副作用是更改數(shù)據(jù)庫的某些全局狀態(tài),比如更新數(shù)據(jù)庫表或某些外部資源,如文件或網(wǎng)絡(luò)等(例如,修改文件或發(fā)送電子郵件消息)。
不允許在用戶定義函數(shù)主體中內(nèi)置不確定函數(shù);這些不確定函數(shù)如下:
@@CONNECTIONS | @@TOTAL_ERRORS |
@@CPU_BUSY | @@TOTAL_READ |
@@IDLE | @@TOTAL_WRITE |
@@IO_BUSY | GETDATE |
@@MAX_CONNECTIONS | GETUTCDATE |
@@PACK_RECEIVED | NEWID |
@@PACK_SENT | RAND |
@@PACKET_ERRORS | TEXTPTR |
@@TIMETICKS |
盡管在用戶定義函數(shù)主體中不允許有不確定函數(shù),這些用戶定義函數(shù)在調(diào)用擴(kuò)展存儲(chǔ)過程時(shí)仍會(huì)產(chǎn)生副作用。
由于擴(kuò)展存儲(chǔ)過程會(huì)對(duì)數(shù)據(jù)庫產(chǎn)生副作用,因此調(diào)用擴(kuò)展存儲(chǔ)過程的函數(shù)是不確定的。當(dāng)用戶定義函數(shù)調(diào)用會(huì)對(duì)數(shù)據(jù)庫產(chǎn)生副作用的擴(kuò)展存儲(chǔ)過程時(shí),不要指望結(jié)果集保持一致或執(zhí)行函數(shù)。
從函數(shù)中調(diào)用擴(kuò)展存儲(chǔ)過程
從函數(shù)內(nèi)部調(diào)用時(shí)擴(kuò)展存儲(chǔ)過程無法向客戶端返回結(jié)果集。任何向客戶端返回結(jié)果集的 ODS API 都將返回 FAIL。擴(kuò)展存儲(chǔ)過程可以連接回 Microsoft® SQL Server™;但是,它不應(yīng)嘗試聯(lián)接與喚醒調(diào)用擴(kuò)展存儲(chǔ)過程的函數(shù)相同的事務(wù)。
與從批處理或存儲(chǔ)過程中喚醒調(diào)用相似,擴(kuò)展存儲(chǔ)過程在運(yùn)行 SQL Server 的 Windows® 安全帳戶的上下文中執(zhí)行。存儲(chǔ)過程的所有者在授予用戶 EXECUTE 特權(quán)時(shí)應(yīng)考慮這一點(diǎn)。
函數(shù)調(diào)用
在可使用標(biāo)量表達(dá)式的位置可喚醒調(diào)用標(biāo)量值函數(shù),包括計(jì)算列和 CHECK 約束定義。當(dāng)喚醒調(diào)用標(biāo)量值函數(shù)時(shí),至少應(yīng)使用函數(shù)的兩部分名稱。
[database_name.]owner_name.function_name ([argument_expr][,...])
如果用戶定義函數(shù)用于定義計(jì)算列,則該函數(shù)的確定性同樣決定了是否可在該計(jì)算列上創(chuàng)建索引。只有當(dāng)函數(shù)具有確定性時(shí),才可以在使用該函數(shù)的計(jì)算列上創(chuàng)建索引。如果在輸入相同的情況下函數(shù)始終返回相同的值,則該函數(shù)具有確定性。
可以使用由一部分組成的名稱喚醒調(diào)用表值函數(shù)。
[database_name.][owner_name.]function_name ([argument_expr][,...])
對(duì)于 Microsoft® SQL Server™ 2000 中包含的系統(tǒng)表函數(shù),喚醒調(diào)用時(shí)需在函數(shù)名的前面加上前綴"::"。
SELECT *
FROM ::fn_helpcollations()
對(duì)于導(dǎo)致語句停止執(zhí)行然后從存儲(chǔ)過程中的下一語句繼續(xù)執(zhí)行的 Transact-SQL 錯(cuò)誤,在函數(shù)中的處理方式不同。在函數(shù)中,這類錯(cuò)誤會(huì)導(dǎo)致函數(shù)停止執(zhí)行。這反過來使喚醒調(diào)用該函數(shù)的語句停止執(zhí)行。
權(quán)限
用戶應(yīng)具有執(zhí)行 CREATE FUNCTION 語句的 CREATE FUNCTION 權(quán)限。
CREATE FUNCTION 的權(quán)限默認(rèn)地授予 sysadmin 固定服務(wù)器角色和 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色的成員。sysadmin 和 db_owner 的成員可用 GRANT 語句將 CREATE FUNCTION 權(quán)限授予其它登錄。
函數(shù)的所有者對(duì)其函數(shù)具有 EXECUTE 權(quán)限。其他用戶不具有 EXECUTE 權(quán)限,除非給他們授予了特定函數(shù)上的 EXECUTE 權(quán)限。
若要?jiǎng)?chuàng)建或更改在 CONSTRAINT、DEFAULT 子句或計(jì)算列定義中引用了用戶定義函數(shù)的表,用戶還必須對(duì)這些函數(shù)有 REFERENCES 權(quán)限。
示例
A. 計(jì)算 ISO 周的標(biāo)量值用戶定義函數(shù)
下例中,用戶定義函數(shù) ISOweek 取日期參數(shù)并計(jì)算 ISO 周數(shù)。為了正確計(jì)算該函數(shù),必須在調(diào)用該函數(shù)前喚醒調(diào)用 SET DATEFIRST 1。
CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 --Special case: Dec 29-31 may belong to the next year 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。
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是結(jié)果集。
ISO Week
----------------
52
B. 內(nèi)嵌表值函數(shù)
下例返回內(nèi)嵌表值函數(shù)。
USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)
C. 多語句表值函數(shù)
假設(shè)有一個(gè)表代表如下的層次關(guān)系:
CREATE TABLE employees (empid nchar(5) PRIMARY KEY, empname nvarchar(50), mgrid nchar(5) REFERENCES employees(empid), title nvarchar(30) )
表值函數(shù) fn_FindReports(InEmpID) 有一個(gè)給定的職員ID,它返回與所有直接或間接向給定職員報(bào)告的職員相對(duì)應(yīng)的表。該邏輯無法在單個(gè)查詢中表現(xiàn)出來,不過可以實(shí)現(xiàn)為用戶定義函數(shù)。
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int -- table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END -- copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO -- Example invocation SELECT * FROM fn_FindReports('11234') GO
相關(guān)文章
sql ntext數(shù)據(jù)類型字符替換實(shí)現(xiàn)代碼
sql ntext數(shù)據(jù)類型字符替換實(shí)現(xiàn)代碼,需要的朋友可以參考下。2011-09-09非常不錯(cuò)的SQL語句學(xué)習(xí)手冊(cè)實(shí)例版
非常不錯(cuò)的SQL語句學(xué)習(xí)手冊(cè)實(shí)例版...2007-03-03sql server自動(dòng)生成拼音首字母的函數(shù)
建立一個(gè)查詢,執(zhí)行語句生成函數(shù)fn_GetPy,下面是具體的實(shí)現(xiàn),需要的朋友可以參考下2014-01-01SQLServer 2012中設(shè)置AlwaysOn解決網(wǎng)絡(luò)抖動(dòng)導(dǎo)致的提交延遲問題
這篇文章主要介紹了SQLServer 2012中設(shè)置AlwaysOn解決網(wǎng)絡(luò)抖動(dòng)導(dǎo)致的提交延遲問題,需要的朋友可以參考下2015-02-02SQL Server 事務(wù),異常和游標(biāo)詳解
這篇文章主要為大家介紹了SQLServer事務(wù),異常和游標(biāo),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下,希望能夠給你帶來幫助2022-01-01VS2015安裝之后加裝Sql server2014詳細(xì)步驟
這篇文章主要介紹了VS2015安裝之后加裝SQL SERVER2014詳細(xì)步驟,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2016-12-12SQLServer2016 sa登錄失敗(錯(cuò)誤代碼18456)
18456錯(cuò)誤是因密碼或用戶名錯(cuò)誤而使身份驗(yàn)證失敗并導(dǎo)致連接嘗試被拒或者賬戶被鎖定無法sa登錄,本文就來介紹一下解決方法,感興趣的可以了解一下2023-09-09SQL Server創(chuàng)建鏈接服務(wù)器的存儲(chǔ)過程示例分享
創(chuàng)建鏈接服務(wù)器。鏈接服務(wù)器讓用戶可以對(duì) OLE DB 數(shù)據(jù)源進(jìn)行分布式異類查詢。在使用 sp_addlinkedserver 創(chuàng)建鏈接 服務(wù)器后,可對(duì)該服務(wù)器運(yùn)行分布式查詢。2014-07-07