11.1 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;
SQL Server标量函数注意点
- 标量函数几乎可以在T-SQL语句的任何地方使用。
- 标量函数接受一个或多个参数，但只返回一个值，因此，它们必须包含
RETURN语句。 - 标量函数可以使用诸如
IF块或WHILE循环之类的逻辑。 - 标量函数不能更新数据。可以访问数据，但不建议这样干。
- 标量函数可以调用其他函数。
