SQL server 标量函数，表值函数与自定义函数

在 SQL Server 中，用户可以根据需要自定义数据库函数（User Defined Function，简称 UDF），用来扩展数据库的功能。与存储过程不同，函数可以返回一个具体的值，可以用来执行简单的数学计算，字符串处理，日期时间处理等功能。函数分为标量函数和表值函数两种。

1. 标量函数

标量函数返回一个单一的值，比如一个数字或者一个字符串。标量函数有三个类型：

• 内联标量函数（Inline Scalar Function）：当SQL Server编译查询中包含的用户定义函数时，系统会将其视为内联表值函数。该类型函数使用和普通单个行标量子查询相似的语法。

• 标量值函数（Scalar Value Function）：标量值函数接受零个或多个输入参数，返回单个标量值。

• 行内函数（Inline Table-valued Function）：行内函数也只能返回一个值，但与标量值函数不同的是，它们的结果可以包含单个行内列。

1. 表值函数

表值函数是返回一张表，而不是一个单独的值。表值函数有两个类型：

• 多行表值函数（Multi-Statement Table-valued Function）：多行表值函数是存储在SQL Server中并返回一整张表。它们类似于存储过程，但是不接受传入参数。需要使用SELECT语句来读取其结果。                                                                                                                       下面是一个返回指定部门员工列表的多行表值函数的示例：

• CREATE FUNCTION dbo.GetEmployeesByDepartment(@deptId INT)
  RETURNS @output TABLE (
      EmployeeId INT,
      Name VARCHAR(50),
      BirthDate DATE
  )
  AS
  BEGIN
      INSERT INTO @output (EmployeeId, Name, BirthDate)
      SELECT EmployeeId, Name, BirthDate
      FROM Employees
      WHERE DepartmentId = @deptId
      RETURN
  END
  

  该函数接受一个部门 ID 参数，返回该部门内员工的 EmployeeId、Name、BirthDate 三个字段。使用该函数的查询语句如下：

  SELECT * FROM dbo.GetEmployeesByDepartment(2)
  

  输出结果为：

  EmployeeId  |  Name          |  BirthDate
  ------------|----------------|-------------
  1           |  John Smith    |  1985-12-10
  2           |  Jane Doe      |  1991-01-05

• 行集函数（Rowset Function）：行集函数返回函数定义中定义的结果集。可用于为应用程序返回表结果的存储过程。

• 单行表值函数（Inline Table-valued Function）返回一个行集合，可以看作是返回一张只有一行的表。使用单行表值函数，可以将一些复杂的查询逻辑封装到函数中，简化使用函数的查询过程。

  下面是一个返回指定日期所在周的开始日期和结束日期的单行表值函数的示例：

CREATE FUNCTION dbo.WeekRange(@date DATE)
RETURNS TABLE
AS
RETURN
(SELECT
    DATEADD(wk, DATEDIFF(wk, 0, @date), 0) AS StartOfWeek,
    DATEADD(wk, DATEDIFF(wk, 0, @date), 6) AS EndOfWeek
)

该函数接受一个日期参数，返回指定日期所在周的开始日期和结束日期。使用该函数的查询语句如下：

SELECT * FROM dbo.WeekRange('2022-01-02')

 输出结果为：

StartOfWeek  |  EndOfWeek
-------------|-------------
2022-01-02   |  2022-01-08

 

自定义函数的好处是可以在数据库层面轻松地处理更多的逻辑，减少编程代码的冗余，使代码更易于维护，同时增加 SQL Server 的灵活性。然而，当使用自定义函数时，需要注意一些影响函数性能的因素，如函数引用过多、数据量大等问题。

假设我们需要自定义一个函数，用于将一个字符串中的每个单词的首字母转换成大写字母。例如，对于字符串 “hello world”，该函数应该返回 “Hello World”。我们可以使用以下代码来实现这个自定义函数：

CREATE FUNCTION [dbo].[InitCap] (@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = LOWER(@str)
    DECLARE @pos INT = PATINDEX('%[^a-z]%', @result)

    WHILE @pos > 0
    BEGIN
        SET @result = STUFF(@result, @pos, 1, UPPER(SUBSTRING(@result, @pos, 1)))
        SET @pos = PATINDEX('%[^a-z]%', @result)
    END

    SET @result = UPPER(LEFT(@result, 1)) + SUBSTRING(@result, 2, LEN(@result))

    RETURN @result
END

上述代码定义了一个名为 InitCap 的函数，该函数接受一个字符串参数 @str，返回一个转换后的字符串。下面是该函数实现的步骤：

1. 首先将输入字符串转换为小写字母，来保证后续处理的一致性。

2. 使用 PATINDEX 函数查找第一个非字母字符的位置。

3. 对于每一个找到的非字母字符，使用 STUFF 函数将对应位置的字符替换为大写字母。

4. 最后将第一个字符转换为大写字母，返回转换后的结果。

例如，使用以下语句调用 InitCap 函数：

SELECT dbo.InitCap('hello world') AS Result

输出结果为 “Hello World”。

除了以上所述的示例，SQL Server 的自定义函数还有许多其他的功能和使用场景。例如，可以编写自定义聚合函数、将 CLR 代码集成到自定义函数中、在自定义函数中连接其他表和写递归函数等等。总的来说，使用自定义函数比较灵活，可以根据业务需求来扩展数据库的功能，提高数据处理的效率和准确性。