• 项目

内联用户定义函数

内联用户定义函数是返回 table 数据类型的用户定义函数的子集。内联函数可用于获得参数化视图的功能。

以下示例将返回指定区域的商店名称和城市：

USE AdventureWorks;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

如果此视图更通用，并且允许用户在查看时指定所感兴趣的区域，那么则会更好。然而，视图不支持在 WHERE 子句中指定的搜索条件的参数。内联用户定义函数可用于支持在 WHERE 子句中指定的搜索条件的参数。以下示例将创建一个允许用户在查询中指定区域的内联函数：

USE AdventureWorks;
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
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince 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');
GO

内联用户定义函数规则

内联用户定义函数遵从以下规则：

  • RETURNS 子句只包含关键字 table。不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置。

  • function_body 不用 BEGIN 和 END 分隔。

  • 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 筛选出特定商店的聚集数据。