SQL?Server怎么創(chuàng)建用戶定義函數(shù)

本文小編為大家詳細(xì)介紹“SQL Server怎么創(chuàng)建用戶定義函數(shù)”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“SQL Server怎么創(chuàng)建用戶定義函數(shù)”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識吧。

一、UDF的定義

和存儲過程很相似，用戶自定義函數(shù)也是一組有序的T-SQL語句，UDF被預(yù)先優(yōu)化和編譯并且可以作為一個(gè)單元來進(jìn)行調(diào)用。

UDF和存儲過程的主要區(qū)別在于返回結(jié)果的方式：

• 使用UDF時(shí)可傳入?yún)?shù)，但不可傳出參數(shù)。輸出參數(shù)的概念被更為健壯的返回值取代了。

• 和系統(tǒng)函數(shù)一樣，可以返回標(biāo)量值，這個(gè)值的好處是它并不像在存儲過程中那樣只限于整形數(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)和時(shí)間戳以外的任何有效的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

然后試著，運(yùn)用一下：

SELECT * FROM Nx_comment 
　　WHERE dbo.DateOnly(com_posttime) = '2012.04.28'　　--注意前面的dbo是必須的。

其實(shí)以上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來實(shí)現(xiàn)，先定義兩個(gè)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();        --在一個(gè)UDF內(nèi)引用另外一個(gè)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 + '%'
        　　)

然后查詢的時(shí)候可以這樣用：

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

這個(gè)函數(shù)通過傳入一個(gè)十進(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()就是一個(gè)確定性的內(nèi)置函數(shù)。3、5、10的總合永遠(yuǎn)都是18，而GETDATE()的值就是非確定性的，因?yàn)槊看握{(diào)用它的時(shí)候GETDATE()都會改變。

如果視圖或計(jì)算列引用非確定性函數(shù)，則在該視圖或列上將不允許建立任何索引。

如果判定函數(shù)是否是確定性的？除了上面描述的規(guī)則外，這些信息存儲在對象的IsDeterministic屬性中，可以利用OBJECTPROPERTY屬性檢查。

SELECT OBJECTPROPERTY(OBJECT_ID('DateOnly'),'IsDeterministic');　　--只是剛才的那個(gè)自定義函數(shù)

輸出結(jié)果如下：

居然是非確定性的。原因在于之前在定義該函數(shù)的時(shí)候，并沒有加上這個(gè)"WITH SCHEMABINDING"。

ALTER FUNCTION dbo.DateOnly(@Date date)
　　RETURNS date
　　WITH SCHEMABINDING　　--當(dāng)我們加上這一句之后
　　AS
　　BEGIN
　　　　RETURN @Date
　　END

在執(zhí)行查詢，該函數(shù)就是確定性的了。

讀到這里，這篇“SQL Server怎么創(chuàng)建用戶定義函數(shù)”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識點(diǎn)還需要大家自己動手實(shí)踐使用過才能領(lǐng)會，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。