SQL?Server創(chuàng)建用戶定義函數(shù)
一、UDF的定義
和存儲過程很相似，用戶自定義函數(shù)也是一組有序的T-SQL語句，UDF被預(yù)先優(yōu)化和編譯并且可以作為一個單元來進(jìn)行調(diào)用。
UDF和存儲過程的主要區(qū)別在于返回結(jié)果的方式：
- 使用UDF時可傳入?yún)?shù)，但不可傳出參數(shù)。輸出參數(shù)的概念被更為健壯的返回值取代了。
- 和系統(tǒng)函數(shù)一樣，可以返回標(biāo)量值，這個值的好處是它并不像在存儲過程中那樣只限于整形數(shù)據(jù)類型，而是可以返回大多數(shù)SQL Server數(shù)據(jù)類型。
UDF有以下兩種類型:
- 返回標(biāo)量值的UDF。
- 返回表的UDF。
創(chuàng)建語法：
CREATE FUNCTION [<schema name>.]<function name>
(
[ <@parameter name> [AS] [<schema name>.]<data type> [= <default value> [READONLY]] [,...n] ]
)
RETURNS { <scalar type> | TABLE [(<table definition>)] }
[ WITH [ENCRYPTION] | [SCHEMABINDING] | [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] |
[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]
[AS] { EXTERNAL NAME <externam method> |
BEGIN
[<function statements>]
{RETURN <type as defined in RETURNS clause | RETURN (<SELECT statement>)}
END}[;]二、標(biāo)量值函數(shù)：
這種類型的UDF和大多數(shù)SQL Server內(nèi)置函數(shù)一樣，會向調(diào)用腳本或存儲過程返回標(biāo)量值，像GETDATE()或USER()函數(shù)就會返回標(biāo)量值。
UDF的返回值并不限于整數(shù)，而是可以返回除了BLOB、游標(biāo)(cursor)和時間戳以外的任何有效的SQL Server數(shù)據(jù)類型(包括用戶自定義類型)。
與存儲過程不同，用戶自定義函數(shù)返回值的目的是提供有意義的數(shù)據(jù)（而對于存儲過程來說，返回值只能說明成功或失敗，如果失敗，則會提供一些關(guān)于失敗性質(zhì)的特定信息。）
可在查詢中內(nèi)聯(lián)執(zhí)行函數(shù)(如作為SELECT語句的一部分)，而用存儲過程則不行。
例1：應(yīng)用在where語句中
CREATE FUNCTION DateOnly(@Date DateTime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12),@Date,102)
END然后試著，運用一下：
SELECT * FROM Nx_comment WHERE dbo.DateOnly(com_posttime) = '2012.04.28' --注意前面的dbo是必須的。
其實以上SQL語句相當(dāng)于：
SELECT * FROM Nx_comment WHERE CONVERT(varchar(12),com_posttime,102) = '2012.04.28'
例2：應(yīng)用在select語句中
SELECT Name,Age,
(SELECT AVG(Age) FROM Person) AS AvgAge,
Age - (SELECT AVG(Age) FROM Person) AS Difference
FROM Person這里要說明一下，列的意思分別是，姓名，年齡，平均年齡以及與平均年齡的差值。
下面我們用UDF來實現(xiàn)，先定義兩個UDF如下：
CREATE FUNCTION dbo.AvgAge()
RETURNS int
AS
BEGIN
RETURN (SELECT AVG(Age) FROM Person)
END
GO
CREATE FUNCTION dbo.AgeDifference(@Age int)
RETURNS int
AS
BEGIN
RETURN @Age - dbo.AvgAge(); --在一個UDF內(nèi)引用另外一個UDF，好華麗的說
END然后執(zhí)行查詢：
SELECT Name,Age,dbo.AvgAge() AS AvgAge,dbo.AgeDifference(Age) as Difference FROM Person
三、內(nèi)聯(lián)表值函數(shù)
SQL Server中的用戶自定義函數(shù)并不只限于返回標(biāo)量值，也可以返回表。返回的表在很大程度上和其他表是一樣的。
可以對返回 表的UDF執(zhí)行JOIN，甚至對結(jié)果應(yīng)用WHERE條件。
改為用表作為返回值并不難，對于UDF來說，表就像任何其他SQL Server數(shù)據(jù)類型一樣。
例1：像表一樣地用UDF
CREATE FUNCTION dbo.fnContactName()
RETURNS TABLE
AS
RETURN (
SELECT Id,LastName + ',' + FirstName AS Name FROM Man
)然后我們就可以像表一樣地用UDF了。
SELECT * FROM dbo.fnContactName()
例2：帶參數(shù)返回表
CREATE FUNCTION dbo.fnNameLike(@LName varchar(20))
RETURNS TABLE
AS
RETURN (
SELECT Id,LastName + ',' + FirstName AS Name FROM Man WHERE LastName Like @LName + '%'
)然后查詢的時候可以這樣用：
SELECT * FROM dbo.fnNameLike('劉')沒有WHERE子句，沒有過濾SELECT列表，就可以反復(fù)使用該函數(shù)，而不需要進(jìn)行"剪切和粘貼"。
四、多語句表值函數(shù)
語法:
CREATE FUNCTION Funtion_name
(
--這里定義傳入?yún)?shù)及類型
)
RETURNS
@table_name TABLE
(
--這里定義@table_name的列名
)
AS
BEGIN
--這里寫sql語句并且將最終需要返回的結(jié)果集塞到@table_name 這張表里面
RETURN
END
GO這個函數(shù)通過傳入一個十進(jìn)制的數(shù)字，分別返回對應(yīng)的二進(jìn)制、八進(jìn)制、十六進(jìn)制。
Create FUNCTION F_TConversion
(
@NUM INT
)
RETURNS
@t_table TABLE
(
[Binary] varchar(64),
Octal varchar(16),
Hexadecimal varchar(8)
)
AS
BEGIN
DECLARE @RESULT2 VARCHAR(500)='',@RESULT8 VARCHAR(500)='',@RESULT16 VARCHAR(500)='';
WITH CTE AS(
SELECT @NUM/2 D2,@NUM%2 S2,@NUM/8 D8,@NUM%8 S8,@NUM/16 D16,@NUM%16 S16,1 [INDEX]
UNION ALL
SELECT D2/2 , D2%2,D8/8 , D8%8,D16/16 , D16%16,[INDEX]+1 FROM CTE WHERE D2>0
)
SELECT @RESULT2+=CAST(S2 AS VARCHAR(1))
,@RESULT8+=CASE WHEN D8=0 AND S8=0 THEN '' ELSE CAST(S8 AS VARCHAR(1)) END
,@RESULT16+=CASE WHEN D16=0 AND S16=0 THEN ''
ELSE CASE CAST(S16 AS VARCHAR(5))
WHEN '10' THEN 'A'
WHEN '11' THEN 'B'
WHEN '12' THEN 'C'
WHEN '13' THEN 'D'
WHEN '14' THEN 'E'
WHEN '15' THEN 'F'
ELSE CAST(S16 AS VARCHAR(5))
END
END
FROM CTE ORDER BY [INDEX] DESC
INSERT INTO @t_table
SELECT @RESULT2,@RESULT8,@RESULT16
RETURN
END
GO
五、理解確定性
用戶自定義函數(shù)可以是確定性的也可以是非確定性的。如果給定了一組特定的有效輸入，每次函數(shù)就都能返回相同的結(jié)果，那么就說該函數(shù)是確定性的。
SUM()就是一個確定性的內(nèi)置函數(shù)。3、5、10的總合永遠(yuǎn)都是18，而GETDATE()的值就是非確定性的，因為每次調(diào)用它的時候GETDATE()都會改變。
如果視圖或計算列引用非確定性函數(shù)，則在該視圖或列上將不允許建立任何索引。
如果判定函數(shù)是否是確定性的？除了上面描述的規(guī)則外，這些信息存儲在對象的IsDeterministic屬性中，可以利用OBJECTPROPERTY屬性檢查。
SELECT OBJECTPROPERTY(OBJECT_ID('DateOnly'),'IsDeterministic'); --只是剛才的那個自定義函數(shù)輸出結(jié)果如下：
居然是非確定性的。原因在于之前在定義該函數(shù)的時候，并沒有加上這個"WITH SCHEMABINDING"。
ALTER FUNCTION dbo.DateOnly(@Date date) RETURNS date WITH SCHEMABINDING --當(dāng)我們加上這一句之后 AS BEGIN RETURN @Date END
在執(zhí)行查詢，該函數(shù)就是確定性的了。
到此這篇關(guān)于SQL Server用戶自定義函數(shù)的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
- MySQL創(chuàng)建用戶與授權(quán)方法
- MySQL創(chuàng)建用戶與授權(quán)及撤銷用戶權(quán)限方法
- MySQL創(chuàng)建用戶和權(quán)限管理的方法
- mysql創(chuàng)建用戶并賦予用戶權(quán)限詳細(xì)操作教程
- Sqlserver創(chuàng)建用戶并授權(quán)的實現(xiàn)步驟
- Mysql8創(chuàng)建用戶及賦權(quán)操作實戰(zhàn)記錄
- mysql創(chuàng)建用戶授權(quán)步驟詳解
- MySQL創(chuàng)建用戶的三種方法
- SqlServer數(shù)據(jù)庫創(chuàng)建用戶只賦予某個視圖權(quán)限
- SQLSERVER2022創(chuàng)建用戶的圖文教程
相關(guān)文章
這篇文章主要介紹了SQL?Server?還原?完整備份和差異備份的詳細(xì)操作，本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-09-09
在SQLServer中,定義表的主鍵非常容易,本文主要介紹了sqlserver設(shè)置主鍵的實現(xiàn)步驟,具有一定的參考價值,感興趣的可以了解一下2023-10-10
SQLServer中用T—SQL命令查詢一個數(shù)據(jù)庫中有哪些表的sql語句
SQLServer如何用T—SQL命令查詢一個數(shù)據(jù)庫中有哪些表，方便進(jìn)行表操作，需要的朋友可以參考下2012-06-06
在Sql Server數(shù)據(jù)庫的存儲過程中如何使用事務(wù)來完成數(shù)據(jù)的批量操作呢？下面小編就詳細(xì)的為大家介紹一下。需要的朋友可以過來參考參考2013-08-08
下面小編就為大家分享一篇基于SSIS 事件的向上傳遞(詳解)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-01-01
SQLserver 實現(xiàn)分組統(tǒng)計查詢（按月、小時分組）
首先創(chuàng)建數(shù)據(jù)表IP地址，訪問時間和訪問次數(shù)。如果每訪問一次就插入一條記錄，那么AccessCount可以不要，查詢時使用count就可以了，這樣當(dāng)訪問量很大的時候會對數(shù)據(jù)庫造成很大壓力。2009-06-06
公網(wǎng)遠(yuǎn)程訪問局域網(wǎng)SQL Server數(shù)據(jù)庫
數(shù)據(jù)庫的重要性相信大家都有所了解，在某些場景下，數(shù)據(jù)庫已經(jīng)成為企業(yè)正常運行必不可少的條件之一。與企業(yè)的其他工作一樣，數(shù)據(jù)庫也需要進(jìn)行必要的維護(hù)，想詳細(xì)了解的同學(xué)可以參考這篇文章2023-04-04
本文主要介紹了使用SQL實現(xiàn)車流量的計算的示例代碼，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
本文主要介紹了SQLServer OUTPUT子句的具體使用,OUTPUT 子句允許你捕獲由 INSERT、UPDATE 或 DELETE 語句影響的行,并將這些行作為結(jié)果集返回,感興趣可以了解一下2024-08-08
SQL Server格式轉(zhuǎn)換函數(shù)Cast、Convert介紹
這篇文章介紹了SQL Server中的格式轉(zhuǎn)換函數(shù)Cast、Convert，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-05-05