探索MSSQL：如何使用用户定义语句

1.背景介绍

用户定义函数是SQL Server用来为开发人员提供自定义函数的一种方式。用户定义函数有3种类型，分别为标量值函数、表值函数和聚合函数。用户定义语句可以让开发人员自定义他们的函数需求。此篇文章，将会介绍如何使用用户定义语句。

2.如何使用用户定义语句

2.1 创建标量值函数

标量值函数返回一个单一的值，比如一个数值或者字符串。下面的例子中，我们将会创建一个函数，用来将一个字符串转换成小写，并返回该值。

CREATE FUNCTION udf_ToLower
(
  @inputString nvarchar(4000)
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @result nvarchar(4000)
SELECT @result = lower(@inputString)
RETURN (@result)
END

这个函数可以被调用，使用下面的SELECT语句：

SELECT dbo.udf_ToLower('HELLO WORLD')

这个函数将会返回：hello world。

2.2 创建表值函数

在SQL Server中，表值函数返回的是一个表格，而不是像标量值函数那样只返回单一的值。下面的例子中，我们将会创建一个函数，用来返回年龄大于特定年纪的员工集合。

CREATE FUNCTION udf_GetEmployeesByAge
(
  @minAge int
)
RETURNS TABLE
AS
  RETURN (SELECT *
          FROM Employees
          WHERE Age > @minAge)

这样我们就可以使用下面的SELECT语句来调用该函数，并返回所有年龄大于30岁的员工集合：

SELECT *
FROM dbo.udf_GetEmployeesByAge(30)

这个函数将会返回，所有年龄大于30岁的员工集合。

2.3 创建聚合函数

聚合函数返回一个单一的结果，由对一个集合执行操作的统计信息而得到。在下面的例子中，我们将会创建一个函数，用来返回一个特定列表中数字的中位数。

CREATE FUNCTION udf_Median
(
  @stringWithNumbers NVARCHAR(MAX)
)
RETURNS FLOAT
AS
BEGIN
  DECLARE @median FLOAT
  DECLARE @split TABLE (Element INT)
  DECLARE @pos INT
  DECLARE @len INT
  SET @pos = 0
  SET @len = 0
  SET @stringWithNumbers = @stringWithNumbers + ','
  WHILE CHARINDEX(',', @stringWithNumbers, @pos + 1) <> 0
  BEGIN
    SET @len = CHARINDEX(',', @stringWithNumbers, @pos + 1) - @pos
    INSERT INTO @split SELECT CAST(SUBSTRING(@stringWithNumbers, @pos, @len) AS INT)
    SET @pos = CHARINDEX(',', @stringWithNumbers, @pos + @len) + 1
  END
  SELECT TOP 1 @median = Element FROM (
  SELECT Element, ROW_NUMBER() OVER (ORDER BY Element) AS RowNumber
  FROM @split
  ) AS Subquery
  WHERE RowNumber IN (CEILING((SELECT COUNT(*) FROM @split) / 2.0), FLOOR((SELECT COUNT(*) FROM @split) / 2.0) + 1)
  ORDER BY RowNumber ASC
  RETURN @median
END

这个函数可以被调用，使用下面的SELECT语句：

SELECT dbo.udf_Median('1,2,3,4,5,6,7,8')

这个函数将会返回：4.5。

3.总结

所以，用户定义函数是SQL Server提供给开发人员的一种自定义函数的方式。其中，用户定义语句可以让开发人员自定义他们的函数需求。