SQL Server 2000 引入使用者自訂函數 (User-Defined Functions, UDF),可在 T-SQL 中像系統函數一樣直接呼叫,極大提升程式碼重用與封裝能力。UDF 分三類:
回傳單一資料值。
CREATE FUNCTION [owner.]function_name
( [@parameter_name [AS] scalar_type [= default]] [,...n] )
RETURNS scalar_return_type
[WITH [,...n] ]
[AS]
BEGIN
function_body
RETURN scalar_expression
END
範例:
CREATE FUNCTION getValue()
RETURNS INT
AS
BEGIN
RETURN (SELECT CONVERT(INT, Value) FROM Test WHERE [Name] = 'Test');
END;
GO
SELECT * FROM Test2 WHERE Value = dbo.getValue();
回傳單一 SELECT 陳述式的結果集,類似參數化檢視表。
CREATE FUNCTION [owner.]function_name
( [@parameter_name [AS] scalar_type [= default]] [,...n] )
RETURNS TABLE
[WITH [,...n] ]
[AS]
RETURN [ ( ] select_stmt [ ) ];
範例:
CREATE FUNCTION getTabletest(@t1 DATETIME, @t2 DATETIME)
RETURNS TABLE
AS
RETURN (
SELECT t1.Field1, t2.Field1, t2.Qty, t3.Field1, t3.Field1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
INNER JOIN Table3 t3 ON t2.JID = t3.JID
WHERE t1.Date >= @t1 AND t1.Date < @t2
);
GO
SELECT * FROM dbo.getTabletest('1999/1/1', '2001/2/1');
以 T-SQL 陳述式群組建構資料表變數並回傳,支援複雜邏輯。
CREATE FUNCTION [owner.]function_name
( [@parameter_name [AS] scalar_type [= default]] [,...n] )
RETURNS @return_variable TABLE
[WITH [,...n] ]
[AS]
BEGIN
function_body
RETURN
END
定義回傳資料表結構 (欄位定義、約束)。
ENCRYPTION:加密函數定義。SCHEMABINDING:綁定結構描述,防止依賴物件被修改/刪除。所有 UDF 呼叫皆需加上擁有者前置詞 (如 dbo.):SELECT dbo.myFunc()。