11.2 SQL Server 表值函数

 

SQL Server表值函数

 

目录

• SQL Server表值函数
  • 简介
  • 创建表值函数
  • 执行表值函数
  • 修改表值函数
  • 多语句表值函数（MSTVF）
  • 何时使用表值函数
  • 删除表值函数
    • 示例
      • A)删除普通函数
      • B)删除带有SCHEMABINDING的函数

 

简介

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

创建表值函数

下面的创建了一个表值函数，函数返回产品列表，包括产品名称、年款和特定年款的标价：

	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

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库