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_yearINT类型参数。它返回型号年份等于@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
posted @   平元兄  阅读(1367)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示