內嵌使用者定義函數

內嵌使用者定義函數是指傳回 table 資料類型之使用者定義函數的子集。內嵌函數可用來達到參數型檢視的功能。

以下範例會傳回指之定區域的商店名稱與城市。

USE AdventureWorks2008R2;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID
    JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

此檢視若能更廣泛,讓使用者能指定他們想要檢視的區域將會更好。不過,檢視並不支援在 WHERE 子句中指定搜尋條件中的參數。內嵌使用者自訂函數可用來支援在 WHERE 子句中指定搜尋條件中的參數。以下範例會建立內嵌函數,允許使用者在查詢中指定區域:

USE AdventureWorks2008R2;
GO

IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE sp.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington')
ORDER BY City;
GO

內嵌使用者自訂函數的規則

內嵌使用者自訂函數遵循下列規則:

  • RETURNS 子句只包含關鍵字 table。您不需要定義傳回變數的格式,因為 RETURN 子句中的 SELECT 陳述式的結果集格式會設定。

  • 沒有以 BEGIN 及 END 分隔的 function_body。

  • RETURN 子句會將一個 SELECT 陳述式放入括號中。SELECT 陳述式的結果集會組成函數所傳回的資料表。用於內嵌函數的 SELECT 陳述式,與用於檢視的 SELECT 陳述式有著相同的限制。

  • 資料表值函式只接受常數或 **@local_variable** 引數

內嵌函數和索引檢視

內嵌函數也可以用來提高索引檢視的效率。索引檢視本身不得在它的 WHERE 子句搜尋條件中使用參數,來為不同的使用者修改儲存的結果集。不過,您可以定義索引檢視儲存符合檢視的完整資料集,再根據索引檢視定義內嵌函數,索引檢視包含允許使用者修改結果的參數型搜尋條件。如果檢視定義很複雜,只要為檢視建立叢集索引,大部份為建立結果集而執行的作業,都與建立彙總或聯結多個資料表有關。如果您建立一個參考索引檢視的內嵌函數,則該函數可套用使用者的參數化篩選,從索引檢視的具體化結果集傳回特定資料列。例如:

  1. 您可以定義一個 vw_QuarterlySales 檢視將所有的銷售資料彙總到結果集,以報告所有商店每一季的摘要銷售資料。

  2. 您可以為 vw_QuarterlySales 建立一個叢集索引,以具體化包含摘要資料的結果集。

  3. 您可以建立一個內嵌函數,以篩選摘要資料:

    CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
         ( @StoreID int )
    RETURNS table
    AS
    RETURN (
            SELECT *
            FROM SalesDB.dbo.vw_QuarterlySales
            WHERE StoreID = @StoreID
           )
    
  4. 使用者可從內嵌函數中選取,以得到特定商店的資料:

    SELECT *
    FROM fn_QuarterlySalesByStore(14432)
    

要滿足步驟 4 發出之查詢所需的重要工作,就是逐季彙總銷售資料。這個工作在步驟 2 中已執行過一次。在步驟 4 中每個個別的 SELECT 陳述式使用 fn_QuarterlySalesByStore 函數篩選出其商店專屬的彙總資料。