• 發行項

執行使用者定義函數 (Database Engine)

使用者定義函數可在查詢或其他陳述式和運算式中叫用，例如計算資料行或字串運算式。純量值函數可使用 EXECUTE 陳述式來執行。

叫用傳回純量值的使用者定義函數

您可以在 Transact-SQL 陳述式中允許相同資料類型之純量運算式的任何位置，叫用一個會傳回純量值的使用者定義函數。叫用純量值函數必須至少使用函數的兩部分名稱。如需有關多部分名稱的詳細資訊，請參閱＜Transact-SQL 語法慣例 (Transact-SQL)＞。

查詢

下列位置允許傳回純量值的使用者自訂函數：

  • 作為 SELECT 陳述式的 select_list 中之 expression：

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO

  • 作為 WHERE 或 HAVING 子句述詞中的 expression 或 string_expression：

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO

  • 作為 GROUP BY 子句中的 group_by_expression。

  • 作為 ORDER BY 子句中的 order_by_expression。

  • 作為 UPDATE 陳述式中 SET 子句中的 expression：

    USE AdventureWorks2008R2;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO

  • 作為 INSERT 陳述式的 VALUES 子句中之 expression：

    在這些位置所參考的使用者自訂函數，會按照邏輯每個資料列執行一次。

CHECK 條件約束

如果將引數值傳遞給只參考資料表中的資料行或常數的函數，則可在 CHECK 條件約束中叫用傳回純量值的使用者自訂函數。每當查詢處理器檢查條件約束時，查詢處理器都會利用與目前接受檢查的資料列關聯之引數值來呼叫函數。資料表的擁有者，必須也是利用資料表的 CHECK 條件約束叫用的使用者自訂函數之擁有者。

DEFAULT 定義

如果將引數值傳遞給只包含常數的函數，則使用者自訂函數可以被當作 DEFAULT 定義的 constant_expression 來叫用。資料表的擁有者，必須也是利用資料表的 DEFAULT 定義叫用的使用者自訂函數之擁有者。

計算資料行

如果傳遞給函數的引數值只參考資料表中的資料行或條件約束，則可利用計算資料行來叫用函數。資料表的擁有者，必須也是利用資料表中的計算資料行叫用的使用者自訂函數之擁有者。

指派運算子

設定運算子 (left_operand = right_operand) 可以叫用被指定作為右邊運算元的運算式中，傳回純量值的使用者自訂函數。

流程控制陳述式

傳回純量值的使用者自訂函數，可以由流程控制陳述式的布林運算式叫用。

CASE 運算式

傳回純量值的使用者自訂函數，可由 CASE 運算式的任一運算式叫用。

傳回字元字串的使用者自訂函數，可以被當作 PRINT 陳述式的 string_expr 運算式來叫用。

函數與預存程序

  • 函數引數也可以當作傳回純量值的使用者自訂函數之參考。

  • 預存程序中的 RETURN integer_expression 陳述式，可以叫用傳回整數作為 integer_expression 的使用者自訂函數。

  • 假設叫用的使用者定義函數所傳回的值可以隱含地轉換為叫用函數的傳回資料類型，則使用者定義函數中的 RETURN return_type_spec 陳述式可以叫用傳回如 return_type_spec 純量資料類型的使用者定義函數。

執行傳回純量值的使用者定義函數

您可以利用與預存程序相同的方式，來執行傳回純量值的使用者定義函數。執行傳回純量值的使用者自訂函數時，指定參數的方式必須與指定參數給預存程序相同：

  • 引數值不加括號。

  • 可指定參數名稱。

  • 如果指定參數名稱，引數值的順序不必和參數的順序相同。

下列範例建立一個可傳回小數純量值的使用者自訂函數。

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

下列範例執行 dbo.ufn_CubicVolume 函數。使用 Transact-SQL EXECUTE 陳述式時，識別引數的順序與函數定義中的參數之順序不同：

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

下列範例執行 dbo.ufn_CubicVolume 函數且不指定參數名稱：

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

您也可以使用 ODBC CALL 語法，從 OLE DB 或 ODBC 應用程式執行 dbo.ufn_CubicVolume 函數：

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

叫用傳回 Table 資料類型的使用者定義函數

您可以叫用傳回 table 的使用者定義函數，其中資料表運算式可用於 SELECT、INSERT、UPDATE 或 DELETE 陳述式的 FROM 子句中。傳回資料表的使用者自訂函數之叫用，可以在後面加上選擇性的資料表別名。下例說明在 SELECT 陳述式的 FROM 子句中呼叫資料表值函式 dbo.ufnGetContactInformation。

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

在子查詢的 FROM 子句中叫用傳回資料表的使用者自訂函數時，函數引數無法參考來自外部查詢的任何資料行。

靜態的唯讀資料指標是唯一可在 SELECT 陳述式開啟的資料指標類型，它的 FROM 子句會參考傳回資料表的使用者自訂函數。

參考傳回 table 的使用者定義函數之 SELECT 陳述式，會叫用函數一次。

叫用內建的資料表值函式

有許多傳回資料表值的內建資料表值函式。這些內建的使用者自訂函數的叫用可以是未限定的，也可以使用 sys 結構描述限定詞。您應該對內建資料表值函式使用 sys 結構描述限定詞，因為它可防止與相同名稱的使用者自訂函數發生衝突。下列範例顯示如何叫用系統內建函數 fn_helpcollations。

SELECT *
FROM sys.fn_helpcollations();
GO

在資料表值函式中使用提示

當您建立使用者定義函數時，可在形成函數定義的任何查詢中套用資料表提示。套用到參考 Transact-SQL 資料表值函式的檢視之提示，它也可以套用到函數。這些函數可能與函數定義中的提示衝突。如需詳細資訊，請參閱＜檢視解析＞。

您不能在參考 CLR 資料表值函式的檢視中套用提示。

[!附註]

未來的 SQL Server 版本將移除 Database Engine 可套用檢視中的提示到多陳述式資料表值函式 (它們是檢視定義的一部分) 的能力。

您不能將資料表提示套用到查詢的 FROM 子句中任何資料表值函式的結果。