创建表值函数

5 分钟

使用表值函数可将复杂的查询逻辑封装到返回结果集的可重用组件中。可以直接在查询中调用这些函数，就像表或视图一样，使代码更具模块化和可维护性。

生成数据库应用程序时，通常需要根据输入参数检索筛选或计算的数据集。表值函数通过将查询逻辑打包到接受参数和返回表的函数中来解决此问题。与存储过程不同，可以在子句和JOIN语句中使用SELECT表值函数，从而灵活地将函数结果视为数据源。

了解表值函数类型

SQL Server 提供两种类型的表值函数，每个函数都适合不同的方案。

内联表值函数

包含单个 SELECT 语句并直接返回结果。使用内联函数时，不定义表结构 - SQL Server 会从 SELECT 语句推断它。查询优化器将内联表值函数视为具有参数的视图，通常生成更好的执行计划。

多语句表值函数

使用BEGIN...END块，显式声明返回表的结构。当需要执行多个语句、执行复杂计算或以迭代方式生成结果集时，此类型可提供更多控制。但是，这种灵活性伴随着性能权衡，因为优化器以不同的方式对待这些函数。

这些函数之间的选择取决于你的特定要求。对于具有参数的简单查询，内联函数可提供更好的性能。如果需要过程逻辑或多个步骤来生成结果集，则需要使用多语句函数。

创建内联表值函数

内联表值函数提供了一种简洁的方式来参数化查询。使用单个 RETURN 语句来定义它们，后面跟着一个 SELECT 查询。

以下示例演示检索特定客户的订单的内联函数：

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

现在可以像表一样在查询中使用此函数：

SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';

该函数接受客户 ID 参数，并仅返回客户的订单。可以根据需要进一步筛选JOIN或聚合结果。此方法在封装客户筛选逻辑时保持主查询干净。

创建多语句表值函数

当需要执行多个操作以构建结果集时，多语句表值函数可提供更大的灵活性。

请考虑使用多个聚合计算产品销售摘要的函数：

CREATE FUNCTION dbo.GetProductSalesSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @SalesSummary TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantity INT,
    TotalRevenue DECIMAL(18,2),
    AveragePrice DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @SalesSummary
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        AVG(od.UnitPrice) AS AveragePrice
    FROM Production.Products p
    INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

请注意，如何使用特定列和数据类型显式声明表变量 @SalesSummary 。函数正文将数据插入此表变量，然后返回它。此结构允许根据需要添加其他处理逻辑、错误处理或条件语句。

在查询中使用表值函数

表值函数可无缝集成到查询中，实现强大的数据检索模式。

可以将函数结果与其他表联接：

SELECT 
    c.CustomerName,
    s.ProductName,
    s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;

运算符 CROSS APPLY 调用 Customers 表中每一行的函数，但在此示例中，函数参数是常量。将列值作为参数传递时， CROSS APPLY 将变得特别有用：

SELECT 
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';

此查询检索每个客户的所有已完成订单，演示表值函数如何在查询中启用逐行处理。该函数充当关联的子查询，但具有更好的可读性和可重用性。

对于不需要逐行计算的内联表值函数，也可以使用 INNER JOIN 语法：

SELECT 
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;

使用这些技术，可以从更简单、经过测试的函数组件生成复杂的查询，从而提高代码可维护性和开发效率。

反馈

此页面是否有帮助？