SQL Server 2000 使用者自訂函數完整語法與範例解析

使用者自訂函數概述

SQL Server 2000 引入使用者自訂函數 (UDF)，允許開發人員封裝可重複使用的 T-SQL 邏輯，並在查詢中像系統函數一樣直接呼叫。UDF 分為三種類型：標量函數、內嵌資料表值函數、多語句資料表值函數。

三種類型語法詳解

1. 標量函數

回傳單一資料值（如 int、varchar 等）。

CREATE FUNCTION [ owner_name. ] function_name 
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,] ...n] ]
[ AS ]
BEGIN 
    function_body 
    RETURN scalar_expression
END

2. 內嵌資料表值函數

回傳單一 SELECT 陳述式的結果集，效能較佳，類似參數化檢視表。

CREATE FUNCTION [ owner_name. ] function_name 
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS TABLE 
[ WITH < function_option > [ [,] ...n ] ]
[ AS ] 
RETURN [ ( ] select-stmt [ ) ]

3. 多語句資料表值函數

回傳由多個 T-SQL 陳述式建構的資料表變數，適用於複雜邏輯。

CREATE FUNCTION [ owner_name. ] function_name 
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition > 
[ WITH < function_option > [ [,] ...n ] ]
[ AS ] 
BEGIN 
    function_body 
    RETURN
END

通用選項與資料表定義

< function_option > ::= { ENCRYPTION | SCHEMABINDING }
< table_type_definition > :: = ( { column_definition | table_constraint } [ ,...n ] )

實務範例

範例 1：標量函數 - 取得單一數值

-- 建立函數
CREATE FUNCTION getValue()
RETURNS int
AS 
BEGIN
    RETURN (SELECT CONVERT(int, Value) AS Value FROM Test WHERE [Name] = 'Test')
END
GO

-- 呼叫函數
SELECT * FROM Test2 WHERE Value = dbo.getValue()
GO

範例 2：內嵌資料表值函數 - 多表聯查與參數篩選

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')
GO

最佳實務提醒

• 優先使用內嵌資料表值函數，執行計畫可內聯優化，效能優於多語句版本。
• 標量函數在大量資料列上逐列執行時可能造成效能瓶頸，謹慎使用。
• 善用 SCHEMABINDING 選項防止相依物件被意外修改。