在本教程中,将学习如何使用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表值函数,包括内联表值函数和多语句表值函数。