SQL Server表值函数

在本教程中，将学习如何使用SQL Server表值函数，包括内联表值函数和多语句值函数。

SQL Server中的表值函数简介

表值函数是用户定义的函数，它返回表类型的数据。表值函数的返回类型是一个表，因此，可以像使用表一样使用表值函数。

创建表值函数

以下语句示例创建一个表值函数，该函数返回产品列表，包括产品名称，型号年份和价格：

CREATE FUNCTION udfProductInYear (
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

语法类似于创建用户定义函数的语法。

RETURNS TABLE指定该函数将返回一个表。如上所见，没有BEGIN ... END语句。 该语句只是查询production.products表中的数据。

udfProductInYear函数接受一个名为@model_year，类型为INT的参数。 它返回模型年份等于@model_year参数的值的产品。

当创建了表值函数，就可以在：可编程性 -> 函数 -> 表值函数 下找到它，如下图所示：

上面的函数返回单个SELECT语句的结果集，因此，它也称为内联表值函数。

执行表值函数

要执行表值函数，请在SELECT语句的FROM子句中使用它：

SELECT 
    * 
FROM 
    udfProductInYear(2017);

执行上面查询语句，得到以下结果：

在此示例中，选择了型号年份为2017年的产品。
还可以指定从表值函数返回的列，如下所示：

SELECT 
    product_name,
    list_price
FROM 
    udfProductInYear(2018);

执行上面查询语句，得到以下结果：

修改表值函数

要修改表值函数，请使用ALTER关键字。脚本的其余部分是相同的。

例如，以下语句通过更改现有参数并添加一个参数来修改udfProductInYear表值函数：

ALTER FUNCTION udfProductInYear (
    @start_year INT,
    @end_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year BETWEEN @start_year AND @end_year

udfProductInYear函数现在返回模型年份在起始年份和结束年份之间的产品。

以下语句调用udfProductInYear函数来获取模型年份介于2017年和2018年之间的产品：

SELECT 
    product_name,
    model_year,
    list_price
FROM 
    udfProductInYear(2017,2018)
ORDER BY
    product_name;

执行上面查询语句，得到以下结果：

多语句表值函数(MSTVF)

多语句表值函数或MSTVF是一个表值函数，它返回多个语句的结果。

多语句表值函数非常有用，因为可以在函数内执行多个查询并将结果聚合到返回的表中。

要定义多语句表值函数，可以使用表变量作为返回值。 在函数内部，执行一个或多个查询并将数据插入此表变量。

以下udfContacts()函数将员工和客户合并到一个联系人列表中：

CREATE FUNCTION udfContacts()
    RETURNS @contacts TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(255),
        phone VARCHAR(25),
        contact_type VARCHAR(20)
    )
AS
BEGIN
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Staff'
    FROM
        sales.staffs;

    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Customer'
    FROM
        sales.customers;
    RETURN;
END;

以下语句说明了如何执行多语句表值函数udfContacts：

SELECT 
    * 
FROM
    udfContacts();

执行上面查询语句，得到以下结果：

何时使用表值函数

通常使用表值函数作为参数化视图。与存储过程相比，表值函数更灵活，因为可以在使用表的任何地方使用它们。

在本教程中，了解了SQL Server表值函数，包括内联表值函数和多语句表值函数。