创建标量函数

6 分钟

标量函数是 SQL Server 中的基本工具，可用于封装可重用逻辑并返回单个值。可以直接在语句、SELECT子句和其他 WHERE 表达式中使用它们，使查询更易于维护，代码更模块化。

了解标量函数基础知识

标量函数接受零个或多个参数，并返回指定数据类型的单个值。与存储过程不同，无论使用列或变量，标量函数都可以直接嵌入到 SQL 表达式中。

标量函数的主要特征包括它们接受输入参数、执行计算或转换以及返回一个值的能力。在函数定义中显式定义返回数据类型，SQL Server 在创建时对其进行验证。

创建标量函数时，将创建其他开发人员可以在整个数据库中调用的可重用逻辑片段。这将促进代码重用，并帮助保持应用程序之间的一致性。

定义标量函数语法

若要创建标量函数，请使用 CREATE FUNCTION 具有特定语法组件的语句。基本结构包括函数名称、参数、返回类型和函数正文。

下面是基本语法模式：

CREATE FUNCTION schema_name.function_name 
(
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    -- Function logic here
    RETURN @result
END

该 RETURNS 子句指定函数返回的单个值的数据类型。在BEGIN...END块中，编写您的 T-SQL 逻辑，并使用RETURN语句发送回结果。

例如，可以创建一个计算销售税的简单函数：

CREATE FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    SET @TaxAmount = @Amount * @TaxRate
    RETURN @TaxAmount
END

此函数接受两个参数并返回计算的税额。可以在任何 SELECT 语句中使用此函数。

使用业务逻辑实现标量函数

标量函数擅长封装那些需要在整个数据库中一致应用的业务规则和计算。使用标量函数，可以将那些可能在多个查询或应用程序代码中重复的逻辑集中起来。

假设你需要在几年内计算员工任期。创建一个标量函数，该函数接受雇用日期并返回完整年份数：

CREATE FUNCTION dbo.GetEmployeeTenure
(
    @HireDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Tenure INT
    SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
    RETURN @Tenure
END

可以在查询中使用此函数来显示任期信息：

SELECT 
    EmployeeName,
    HireDate,
    dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5

此方法可确保在整个数据库中进行一致的任期计算。如果业务规则发生更改，则修改函数一次，而不是更新多个查询。

注释

此函数使用 GETDATE()，这使得它不可确定性。不可确定性函数不能用于索引视图或计算列上的索引。对于需要确定性的方案，请改为将当前日期作为参数传递。

对标量函数应用最佳做法

创建标量函数时，几个最佳做法有助于确保最佳性能和可维护性。了解这些做法有助于避免常见的陷阱，并创建高效的可靠函数。

首先，尽可能保留标量函数确定性。确定性函数始终返回相同的结果，给定相同的输入参数。引用系统日期/时间函数或表的函数是不确定的，并且可能会阻止某些查询优化。

此外，请避免在函数中产生副作用。标量函数不应修改数据库状态或依赖于外部资源。此限制存在是因为 SQL Server 可能多次执行函数，或以不同于您预期的顺序执行。

最后，请注意性能影响。在包含大型表的 WHERE 子句或 SELECT 列表中使用标量函数时，SQL Server 可能会对每一行执行该函数。这可能会影响查询性能。对于此类方案，请考虑使用内联表值函数作为替代方法。

下面是遵循以下做法的精心设计的标量函数的示例：

CREATE FUNCTION dbo.FormatPhoneNumber
(
    @PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
    DECLARE @FormattedNumber VARCHAR(14)
    
    IF LEN(@PhoneNumber) = 10
        SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
                               SUBSTRING(@PhoneNumber, 4, 3) + '-' +
                               SUBSTRING(@PhoneNumber, 7, 4)
    ELSE
        SET @FormattedNumber = @PhoneNumber
    
    RETURN @FormattedNumber
END

此函数是确定性的，没有副作用，并执行简单的转换。当电话号码与预期格式不匹配时，它通过返回原始值来正常处理无效输入。

修改和管理标量函数

创建标量函数后，可以使用该语句修改其定义 ALTER FUNCTION 。 ALTER FUNCTION 语法类似于 CREATE FUNCTION ，但允许你更改函数而无需删除并重新创建它，从而保留权限和依赖项。

ALTER FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    -- Updated logic with rounding
    SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
    RETURN @TaxAmount
END

若要删除标量函数，请使用 DROP FUNCTION 语句：

DROP FUNCTION IF EXISTS dbo.CalculateSalesTax

如果函数不存在，则子 IF EXISTS 句会阻止错误，这在部署脚本中很有用。在删除函数之前，请通过检查系统视图（例如 sys.sql_expression_dependencies）来验证任何其他数据库对象是否依赖于它。

反馈

此页面是否有帮助？