使用者定義函數的基本概念
MicrosoftSQL Server 使用者定義函數與程式設計語言函數類似,是接受參數、執行動作 (如複雜計算) 以及傳回該動作所得值的常式。傳回的值可以是單一純量值或結果集。
使用者定義函數的好處
使用 SQL Server 中使用者定義函數的好處如下:
可進行模組化的程式撰寫。
只需建立函數一次,將它儲存在資料庫中,就可以在程式中無限次地隨意呼叫。使用者定義函數不需透過原始程式碼即可修改。
可加快執行速度。
如同預存程序,Transact-SQL 使用者定義函數可藉由針對重複執行來快取以及重複使用計畫,來降低 Transact-SQL 程式碼的編譯成本。這表示,每次使用時,使用者定義函數不需要重新剖析和最佳化,所以執行時間可以更快。
與 Transact-SQL 函數相比,CLR 函數在計算工作、字串處理和商務邏輯等方面提供更顯著的效能優勢。Transact-SQL 函數更適用於經常需要進行資料存取的作業。
可降低網路傳輸量。
對於無法以單一純量運算式表示的作業 (例如,根據某些複雜條件約束來篩選資料),可以利用函數來表示。接著,您可以在 WHERE 子句中叫用函數,減少傳送到用戶端的資料列數。
使用者定義函數的元件
您可以使用 Transact-SQL 或任何 .NET 程式語言來撰寫使用者定義函數。如需使用 .NET 程式語言的詳細資訊,請參閱<CLR 使用者定義函數>。
所有使用者定義函數都有兩個相同的結構:標頭和主體。函數會使用 0 或多個輸入參數,並傳回純量值或資料表。
標頭可定義:
具有選擇性結構描述/擁有者名稱的函數名稱
輸入參數名稱和資料類型
適用輸入參數的選項
傳回參數資料類型和選擇性名稱
適用傳回參數的選項
主體會定義函數要執行的動作或邏輯。它包含:
一或多個執行函數邏輯的 Transact-SQL 陳述式
.NET 組件的參考
以下範例顯示簡單的 Transact-SQL 使用者定義函數,並識別函數的主要元件。此函數會計算提供的日期,且傳回的值會指明該日期在一星期中為哪一天。
IF OBJECT_ID(N'dbo.GetWeekDay', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetWeekDay;
GO
CREATE FUNCTION dbo.GetWeekDay -- function name
(@Date datetime) -- input parameter name and data type
RETURNS int -- return parameter data type
AS
BEGIN -- begin body definition
RETURN DATEPART (weekday, @Date) -- action performed
END;
GO
以下範例顯示 Transact-SQL 陳述式中使用的函數。
SELECT dbo.GetWeekDay(CONVERT(DATETIME,'20020201',101)) AS DayOfWeek;
GO
以下為結果集:
DayOfWeek
---------
6
(1 row(s) affected)