SQL Server建立使用者定義函數

2022-05-20 13:13:19

一、UDF的定義

和儲存過程很相似，使用者自定義函數也是一組有序的T-SQL語句，UDF被預先優化和編譯並且可以作為一個單元來進行呼叫。

UDF和儲存過程的主要區別在於返回結果的方式：

使用UDF時可傳入引數，但不可傳出引數。輸出引數的概念被更為健壯的返回值取代了。
和系統函數一樣，可以返回標量值，這個值的好處是它並不像在儲存過程中那樣只限於整形資料型別，而是可以返回大多數SQL Server資料型別。

UDF有以下兩種型別:

返回標量值的UDF。
返回表的UDF。

建立語法：

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}[;]

二、標量值函數：

這種型別的UDF和大多數SQL Server內建函數一樣，會向呼叫指令碼或儲存過程返回標量值，像GETDATE()或USER()函數就會返回標量值。

UDF的返回值並不限於整數，而是可以返回除了BLOB、遊標(cursor)和時間戳以外的任何有效的SQL Server資料型別(包括使用者自定義型別)。

與儲存過程不同，使用者自定義函數返回值的目的是提供有意義的資料（而對於儲存過程來說，返回值只能說明成功或失敗，如果失敗，則會提供一些關於失敗性質的特定資訊。）

可在查詢中內聯執行函數(如作為SELECT語句的一部分)，而用儲存過程則不行。

例1：應用在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語句相當於：

SELECT * FROM Nx_comment 
　　WHERE CONVERT(varchar(12),com_posttime,102) = '2012.04.28'

例2：應用在select語句中

SELECT Name,Age,
    　　(SELECT AVG(Age) FROM Person) AS AvgAge,
     　　Age - (SELECT AVG(Age) FROM Person) AS Difference 
　　FROM Person

這裡要說明一下，列的意思分別是，姓名，年齡，平均年齡以及與平均年齡的差值。

下面我們用UDF來實現，先定義兩個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內參照另外一個UDF，好華麗的說
　　END

然後執行查詢：

SELECT Name,Age,dbo.AvgAge() AS AvgAge,dbo.AgeDifference(Age) as Difference 
　　FROM Person

三、內聯表值函數

SQL Server中的使用者自定義函數並不只限於返回標量值，也可以返回表。返回的表在很大程度上和其他表是一樣的。

可以對返回表的UDF執行JOIN，甚至對結果應用WHERE條件。

改為用表作為返回值並不難，對於UDF來說，表就像任何其他SQL Server資料型別一樣。

例1：像表一樣地用UDF

CREATE FUNCTION dbo.fnContactName()
　　RETURNS TABLE
AS
　　RETURN (
        　　SELECT Id,LastName + ',' + FirstName AS Name 　FROM Man
        　 )

然後我們就可以像表一樣地用UDF了。

SELECT * FROM dbo.fnContactName()

例2：帶引數返回表

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列表，就可以反覆使用該函數，而不需要進行"剪下和貼上"。

四、多語句表值函數

語法:

CREATE FUNCTION Funtion_name
(
    --這裡定義傳入引數及型別
)
RETURNS
@table_name TABLE
(
    --這裡定義@table_name的列名
)
AS
BEGIN
    --這裡寫sql語句並且將最終需要返回的結果集塞到@table_name 這張表裡面
    RETURN 
END
GO

這個函數通過傳入一個十進位制的數位，分別返回對應的二進位制、八進位制、十六進位制。

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

五、理解確定性

使用者自定義函數可以是確定性的也可以是非確定性的。如果給定了一組特定的有效輸入，每次函數就都能返回相同的結果，那麼就說該函數是確定性的。

SUM()就是一個確定性的內建函數。3、5、10的總合永遠都是18，而GETDATE()的值就是非確定性的，因為每次呼叫它的時候GETDATE()都會改變。

如果檢視或計算列參照非確定性函數，則在該檢視或列上將不允許建立任何索引。

如果判定函數是否是確定性的？除了上面描述的規則外，這些資訊儲存在物件的IsDeterministic屬性中，可以利用OBJECTPROPERTY屬性檢查。

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

輸出結果如下：

居然是非確定性的。原因在於之前在定義該函數的時候，並沒有加上這個"WITH SCHEMABINDING"。

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

在執行查詢，該函數就是確定性的了。

到此這篇關於SQL Server使用者自定義函數的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。