SQL SERVER 用戶自定義函數（UDF）深入解析

本文內容概要： UDF 概念、原理、優缺點、UDF 的分類詳細講述3種 UDF 的創建、調用方法以及註意事項基本原理： UDF：user-defined functions，用戶自定義函數的簡稱。 UDF 是一個常式，它接受參數、執行操作並返回該操作的結果。根據定義，結果可以是標量值（單個）或表 ...

本文內容概要：

UDF 概念、原理、優缺點、UDF 的分類
詳細講述3種 UDF 的創建、調用方法以及註意事項
UDF 的實踐建議

基本原理：

UDF：user-defined functions，用戶自定義函數的簡稱。

UDF 是一個常式，它接受參數、執行操作並返回該操作的結果。根據定義，結果可以是標量值（單個）或表。

UDF 的優點：

UDF 可以把複雜的邏輯嵌入到查詢中。UDF 可以為複雜的表達式創建新函數。
UDF 可以運用在一個表達式或 SELECT 語句的 FROM 子句中，並且還可以綁定到架構。此外，UDF 還可以接受參數。UDF 有助於實施一致性和可重用性。

UDF 的缺點：

該函數一旦誤用會產生潛在的性能問題。必須針對WHERE子句的每一行執行的任何函數，不管是用戶定義的函數還是系統函數，都將減慢執行速度。

UDF 的類型：

UDF 主要有 3 種類型（SQL Server Management Studio 把內聯表值函數與多語句表值函數放到了一個組中）：

標量函數
內聯表值函數
多語句表值函數

一、標量函數

標量函數是返回一個具體值的函數。函數可以接收多個參數、執行計算然後返回一個值。返回值通過RETURN命令返回。用戶定義的函數中的每個可能代碼路徑都以RETURN命令結尾。

標量函數可以運用於 SQL Server 中的任何表達式，甚至在 CHECK 約束的表達式中也可以使用（但不推薦這種用法）。

函數限制

標量函數必須是確定性的，也就是說標量函數必須反覆地為相同的輸入參數返回相同的值。因此，如newid()函數和rand()函數不允許出現在標量函數中。不允許用戶定義標量函數更新資料庫、調用存儲過程或調用DBCC命令，唯一的例外是可以更新表變數。用戶定義函數不能返回BLOB（二進位大型對象）數據，如text、next、timestamp和image數據類型變數。也不能返回表變數可cursor數據類型。對於錯誤處理，UDF 也不包含 TRY...CATCH 或 RAISERROR。

UDF 可以調用嵌套深度為 32 層以內的其他用戶定義函數，或者遞歸調用自己到 32 層的深度。當然，這隻是理論限制，嵌套函數會嚴重影響性能，應儘可能避免使用嵌套函數。

創建方法

1 CREATE FUNCTION FunctionName (InputParameters) 
2 RETURNS DataType 
3 AS
4 BEGIN
5   Code;
6   RETURN Expression;
7 END;

InputParameters 輸入參數包含數據類型定義。參數可以設置預設值（Parameter = default )，需要註意的是在 UDF 中有預設值的參數並不能成為可選參數，為在調用函數時請求到預設值，需要把關鍵字 DEFAULT 傳遞到函數的預設值參數位置。

示例1：下麵的 UDF 執行一個簡單的數學計算，其中第二個參數帶有預設值。

CREATE FUNCTION dbo.ufnCalculate
(@Numer_a numeric(5,2),
 @Numer_b numeric(5,2) = 1.0)
RETURNS numeric(5,2)
AS
BEGIN
  RETURN @Numer_a / @Numer_b ;
END;
GO

select dbo.ufnCalculate(15.3 , 6.54),
       dbo.ufnCalculate(9.0 , DEFAULT);

結果：
------  ------
2.38      9.00

示例2：計算並返回某個時間所在月份的天數。

CREATE FUNCTION [dbo].[GetMonthDay](@date datetime)
RETURNS int
AS 
BEGIN
  DECLARE @date1 datetime
  SELECT @date1 =Dateadd(MM,1,@date)
  RETURN day(Dateadd(DD,-day(@date1),@date1))
END;

調用方法

在接受單值的表達式中，標量函數可用於任何地方。用戶定義的標量函數必須通過一個最少有兩部分的名稱（所有者.函數名）來調用。

下麵的腳本演示了在資料庫的訂單表中調用示例2中的函數及其返回值。

SELECT S.BIL_DD,dbo.GetMonthDay(BIL_DD) as DAYS_M 
FROM Orders S

結果
BIL_DD        DAYS_M
------        ------
2019-01-31     31
2019-02-15     28

二、內聯表值函數

與視圖相似，內聯表值函數也是為一個存儲的SELECT語句封裝。內聯表值函數保留了視圖的優點，還添加了一些參數。

創建方法

內聯表值用戶定義函數沒有BEGIN / END主體。SELECT語句是作為一個虛擬數據表返回的：

CREATE FUNCTION FunctionName （InputParameters）
RETURNS Table 
AS 
RETURN (Select Statement)；

示例：下麵的示例返回某個客戶所訂購產品的彙總情況。

CREATE FUNCTION dbo.ufnGetProductTotalByCust (@custNo varchar (10))
RETURNS Table 
AS
RETURN(
SELECT H.CUS_NO,B.PRD_NO,SUM(B.QTY) as TOTAL_PRD 
FROM TF_POS AS B       --訂單貨品明細表
LEFT JOIN MF_POS  AS H --訂單客戶信息表
       ON H.OS_NO=B.OS_NO 
WHERE H.CUS_NO=@custNo  
GROUP BY H.CUS_NO,B.PRD_NO );
GO

調用方法

通過dbo.ufnGetProductTotalByCust查詢客戶代號為"CT060228" 的產品彙總數據，函數出現在SELECT語句的FROM部分：

SELECT PRD_NO,TOTAL_PRD FROM 
dbo.ufnGetProductTotalByCust('CT060228') 
ORDER BY PRD_NO DESC

返回結果（部分）：

PRD_NO           TOTAL_PRD
------------   ------------------
10910030006      5792.00000000
10910040003      10776.00000000
10912060014      11442.00000000
10913040009      9276.00000000
11410030028      900.00000000
......

與視圖的關係

與視圖相比，內聯表值函數的優勢在於其可以使用參數。而視圖不包含參數，而且在運行時想要限制結果需要把 WHERE 子句添加到調用視圖的 SELECT 語句中來實現。

示圖的調用示例，假設已經存在視圖 dbo.vwProductTotalByCust，調用視圖時，在 SELECT 語句中添加了一個 WHERE 子句限制：

SELECT * FROM dbo.vwProductTotalByCust WHERE cus_no='CT060228'

關聯方法

表值用戶定義函數的關聯可以使用 APPLY 命令，從而使 UDF 針對由主查詢處理的每一行接受一個不同的參數值。

APPLY 命令具有兩種形式。最普通的一種形式是 CROSS APPLY，它運行起來更像一個內聯接。CROSS APPLY 命令聯接主查詢的數據與來自用戶自定義函數的任意表值數據集。如果未從UDF 返回數據，那麼主查詢的行也不能返回，如下圖的例子所示：

SELECT T.PRD_NO,P.NAME,T.TOTAL_PRD 
FROM PRDT P --產品資料表
CROSS APPLY dbo.ufnGetProductTotalByCust('CT060228')  T
ORDER BY T.PRD_NO DESC 

結果:

PRD_NO         NAME               TOTAL_PRD
------------  ------------        ------------------------
10910030006   3pcs storage jar    5792.00000000
10910040003   2pcs storage jar    10776.00000000
10912060014   4pcs spice jar      11442.00000000
10913040009   6pcs spice jar      9276.00000000
11410030028   salad dressing      900.00000000
......

CROSS APPLY 的第2種形式是 OUTER APPLY 命令，操作上與左聯接相似。這種形式下，主查詢的行將包含在結果集中，而不管 UDF 返回的虛擬表是否為空。

標量函數和內聯表值函數可生成完成相同的結果集，那麼這兩者的區別是什麼呢？

標量函數針對每一行運行一次，而內聯表值函數由查詢優化器處理，非常類似於視圖。因為內聯表值函數會由查詢優化器進行處理，所以建議儘可能優先使用內聯表值函數，而非標量函數。

架構綁定

架構綁定阻止更改或刪除函數所依賴的任何對象。如果架構綁定函數引用了某個表A，那麼表A不可更改或刪除，但可以將列添加到表A。

架構綁定的方法：在函數創建語句的 RETURNS 之後和 AS 之前添加選項 WITH SCHEMA BINDING，如下所示：

1 CREATE FUNCTION FunctionName (InputParameters) 
2 RETURNS DataType 
3 WITH SCHEMA BINDING 
4 AS
5 BEGIN
6   Code;
7   RETURN Expression;
8 END;

可以使用ALTER修改函數，使其不再包含架構綁定，以便可以修改引用對象。

三、多語句表值函數

將標量函數與內聯表值函數的功能結合起來就構成了複雜的多語句表值函數。

特征：這種類型的函數創建了一個表變數，將它置於代碼中，然後從函數返回，以便能在SELECT語句中使用。

優點：可以代碼內生成複雜結果集，以便在SELECT語句中使用，在查詢中構建複雜邏輯，並解決那些沒有游標就很難解決的問題。

創建方法

創建多語句表值函數的語法與創建標量函數的語法相似：

CREATE FUNCTION FunctionName （InputParamenters）
RETURNS @TableName TABLE (columns)
AS
BEGIN; 
    Code to populate table variable
    RETURN;
END;

示例：下麵的過程構建了一個返回基本結果集的多語句表值用戶定義的函數，函數首先在 CREATE FUNCTION 頭中創建了一個名為 @PruductList 的表變數，在函數體中，兩個 INSERT 語句置於@ProductList 表變更中，如果函數執行完畢，表變更 @ProductList 將作為函數的輸出傳回。

ufnGetProductsAndOrderTotals函數返回Product表中的每個產品和每個產品的訂單總數。

CREATE FUNCTION ufnGetProductsAndOrderTotals() 
RETURNS @ProductList TABLE 
            (ProductID int,
             ProductName nvarchar(100),
             TotalOrders int)
AS
BEGIN;
        INSERT @ProductList（ProductID，ProductName） 
        SELECT ProductID，Name 
        FROM Product;

        UPDATE p1 
        SET TotalOrders = 
            (SELECT sum(sod.OrderQty) 
             FROM @ProductList ip1 
               JOIN SalesOrderDetail sod 
               ON ip1.ProductID = sod.ProductID
             WHERE ip1.ProductID = p1.ProductID) 
        FROM @ProductList p1 ;

        RETURN;
END;

調用方法

只需要在SELECT語句的FROM部分引用該函數，即可查詢到函數的執行結果。下麵的代碼檢索ufnGetProductsAndOrderTotals函數的結果：

SELECT ProductID，ProductName，TotalOrders 
FROM ufnGetProductsAndOrderTotals（） 
ORDER BY TotalOrders DESC

結果集如下：

ProductID        ProductName                TotalOrders
------------       -------------------          --------------
715                 4 PCS Storage Jar           8311
780                 6 PCS Spice Jar             6800
......

四、UDF 的實踐建議

無疑 UDF 為我們的 T-SQL 選項添加了靈活性，但如果這些函數運用不當，帶來的性能缺陷也是很嚴重的。UDF 並不能成為子查詢、視圖或存儲過程的替代物。

從上面的示例，我們不難看出，三種類型函數可以產生基本相同的結果集，實踐中可以將自己的函數定義為其種任意一種。

建議一：性能最優化

如果選擇 UDF 來封裝查詢邏輯，則建議遵循下麵的這些基本原則：

相對於多語句表值函數，儘可能優先選擇內聯表值函數；
儘量避免使用標量函數，儘可能使用內聯表值函數取代它；
如果需要使用多語句表值函數，則對比一下存儲過程是不是更合適的解決文案。雖然需要花更多的時間，但考慮長期的性能影響，還是值得的。

建議二：命名一致性

為方便我們的T-SQL更易於閱讀更容易排除故障，我們應該確保為所有的 UDF 創建某種統一類型的命名約束。最常用的方法是採用名稱首碼，更進一步，可以讓首碼表明 UDF 是標量函數、內聯表值函數還是多語句表值函數。例如，返回每個產品類別的月平均銷售額的內聯表值函數，可以將其命名為 udfAvgMonSalesPerCategory 或 ifn_AvgMonSalesPerCategory。

SQL SERVER 用戶自定義函數（UDF）深入解析

本文內容概要：

基本原理：

UDF 的優點：

UDF 的缺點：

UDF 的類型：

一、標量函數

函數限制

創建方法

調用方法

二、內聯表值函數

創建方法

調用方法

與視圖的關係

關聯方法

架構綁定

三、多語句表值函數

創建方法

四、UDF 的實踐建議

建議一：性能最優化

建議二：命名一致性