11.1 SQL Server 标量函数

SQL Server (标量函数)

SQL Server (标量函数)

简介

SQL Server标量函数接受一个或多个参数并返回单个值。

标量函数可以简化代码。比如，可能有一个复杂的计算出现在许多查询中。您可以创建一个标量函数来封装公式并在每个查询中使用它，而不是在每个查询都包含公式。

创建标量函数

使用CREATE FUNCTION语句创建标量函数：

	CREATE FUNCTION [schema_name.]function_name (parameter_list)
	RETURNS data_type AS
	BEGIN
	statements
	RETURN value
	END

其中：

首先在CREATE FUNCTION关键字后面指定函数名，schema_name(架构名)是可选的，如果不明确指定，SQLServer默认使用dbo。
其次，指定一个参数列表，在函数名后面用括号括起来。
第三，在RETURNS语句中指定返回值的数据类型。
最后，包含一个RETURN语句来返回函数体中的值。

以下示例创建了一个基于数量、标价和折扣计算净销售额的函数：

	CREATE FUNCTION sales.udfNetSale(
	@quantity INT,
	@list_price DEC(10,2),
	@discount DEC(4,2)
	)
	RETURNS DEC(10,2)
	AS
	BEGIN
	RETURN @quantity * @list_price * (1 - @discount);
	END;

稍后，就可以使用这个函数来计算order_items(订单项)中任何销售订单的净销售额

创建标量函数后，可以在Programmability>Functions>scalar valued Functions(可编程性>函数>标量函数)下找到它，如下图所示：

调用标量函数

跟调用内置函数一样。如下，以下语句调用udfNetSale函数：

	SELECT
	sales.udfNetSale(10,100,0.1) net_sale;

使用sales.udfNetSale函数获取order_items表中销售订单的净销售额：

	SELECT
	order_id,
	SUM(sales.udfNetSale(quantity, list_price, discount)) net_amount
	FROM
	sales.order_items
	GROUP BY
	order_id
	ORDER BY
	net_amount DESC;

部分输出：

修改标量函数

把CREATE关键字改成ALTER关键字就行了。其余语句保持不变：

	ALTER FUNCTION [schema_name.]function_name (parameter_list)
	RETURN data_type AS
	BEGIN
	statements
	RETURN value
	END

注意，如果不知道函数存不存在，可以使用CREATE OR ALTER语句创建或者修改现有的标量函数：

	CREATE OR ALTER FUNCTION [schema_name.]function_name (parameter_list)
	RETURN data_type AS
	BEGIN
	statements
	RETURN value
	END

删除标量函数

请使用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)删除普通函数

比如删除sales.udfNetSale:

DROP FUNCTION IF EXISTS sales.udfNetSale;

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;