11.2 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参数的产品。
创建表值函数后，可以在Programmability>Functions>table valued Functions下找到它，如下图所示：
上面的函数返回单个SELECT语句的结果集，因此，也称为内联表值函数。
执行表值函数
要执行表值函数，在SELECT语句的FROM子句中使用它：
SELECT
*
FROM
udfProductInYear(2017);
在本例中，我们选择了2017年款的产品。
还可以指定要从表值函数返回的列，如下所示：
SELECT
product_name,
list_price
FROM
udfProductInYear(2018);
修改表值函数
把CREATE关键字改成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();
何时使用表值函数
通常使用表值函数作为参数化视图。与存储过程相比，表值函数更灵活，因为我们可以在任何使用表的地方使用它们。
删除表值函数
请使用DROP FUNCTION语句删除表值函数：
DROP FUNCTION [IF EXISTS] [schema_name.]function_name;
其中
函数存在时，IF EXISTS才允许删除该函数。否则，该语句不执行任何操作。如果尝试删除一个不存在的函数而不指定IF EXISTS，将会报错。
如果要删除的函数被使用WITH SCHEMABINDING选项创建的视图或其他函数引用，则DROP FUNCTION将失败。
要删除多个函数：
DROP FUNCTION [IF EXISTS]
schema_name.function_name1,
schema_name.function_name2,
...;
示例
A)删除普通函数
比如删除udfContacts:
DROP FUNCTION IF EXISTS udfContacts;
B)删除带有SCHEMABINDING的函数
创建使用WITH SCHEMABINDING选项的函数sales.udf_get_discount_amount：
CREATE FUNCTION sales.udf_get_discount_amount (
@quantity INT,
@list_price DEC(10,2),
@discount DEC(4,2)
)
RETURNS DEC(10,2)
WITH SCHEMABINDING
AS
BEGIN
RETURN @quantity * @list_price * @discount
END
然后创建一个视图并且使用这个函数：
CREATE VIEW sales.discounts
WITH SCHEMABINDING
AS
SELECT
order_id,
SUM(sales.udf_get_discount_amount(
quantity,
list_price,
discount
)) AS discount_amount
FROM
sales.order_items i
GROUP BY
order_id;
现在如果想删除这个函数sales.udf_get_discount_amount，将会报错：
DROP FUNCTION sales.udf_get_discount_amount;
报错如下：
Cannot DROP FUNCTION 'sales.udf_get_discount_amount' because it is being referenced by object 'discounts'.
此时如果想删除函数，就要先删除视图sales.discounts:
DROP VIEW sales.discounts;
然后删除函数：
DROP FUNCTION sales.udf_get_discount_amount;
```1