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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器