SQL 中的用户定义函数

SQL Server 中的 UDF 或用户定义函数类似于任何其他编程语言中的标量方法，它们接受参数，执行复杂的计算，并返回结果值。

SQL Server 中的函数类型

函数有两种类型

  • 内置系统。
  • 用户定义的。

SQL Server 用户定义函数

SQL Server 允许我们创建自己的方法，称为用户定义函数。例如，如果我们要执行一些复杂的计算，那么我们可以将它们放入一个单独的方法并将其存储在数据库中。每当需要计算时，我们都可以调用它。函数有三种类型

标量：它返回一个单一值。通常，我们必须在 BEGIN … END 块之间定义主体，但对于 SQL Server 用户定义的内联标量函数，您可以省略它们。我们可以使用任何数据类型作为返回类型，除了 text、image、ntext、cursor 和 timestamp。

表值：它是一个返回表的用户定义函数。

内联表值：它根据单个 SELECT 语句返回表数据类型。

SQL Server 内置函数

所有由 Microsoft 支持的内置函数都称为系统函数。我们不必担心它们内部的逻辑，因为它们无法修改。例如，数学、排名和字符串是一些内置函数。

用于查找总和、最小值和平均值的聚合函数主要用于系统方法。查找当前系统日期和时间也是最常用的。

SQL 用户定义函数的优点

  1. 用户定义函数可以避免我们多次编写相同的逻辑。
  2. 在数据库中，您可以创建一次方法并调用 n 次。
  3. 它们通过缓存执行计划并重用它们来减少查询的编译时间。
  4. 此 UDF 可以帮助我们将复杂的计算与常规查询分离，从而更快更好地理解和调试查询。
  5. 由于其缓存计划，它减少了网络流量
  6. 它们也用于 WHERE 子句。通过这种方式，我们可以限制发送到客户端的行数。

UDF 语法

SQL Server 用户定义函数或 UDF 的语法是

CREATE FUNCTION Name(@Parameter_Name Data_type, 
                                 .... @Parameter_Name Data_type
                             )
RETURNS Data_Type
AS
   BEGIN
      -- Function_Body
      
      RETURN Data 
   END
  • Return_Type
    1. 数据类型：请指定返回值的数据类型。例如，VARCHAR、INT、FLOAT 等。
    2. 数据：请指定返回值，它应与数据类型匹配。它可以是单个值或表。
  • 名称：您可以指定您希望赋予的任何名称，但系统保留关键字除外。请尝试使用有意义的名称，以便您轻松识别它们。
  • @Parameter_Name：每个方法接受零个或多个参数；它完全取决于用户的要求。声明参数时，不要忘记适当的数据类型。例如 (@name VARCHAR(50), @number INT)
  • Function_Body：您想在此特定方法中实现的任何查询或任何复杂的数学计算。

让我们看看如何在 SQL Server 中使用示例创建或编写不同类型的用户定义函数。对于此 UDF 演示，我们将使用数据库中的 [MyEmployee 表] 和 [Department] 表。

请参阅更改 UDF 文章，以了解如何重命名、修改或删除 SQL Server UDF。从下图可以看出，[MyEmployee 表] 表有十四条记录

并且 [Department] 表有八条记录。

创建 SQL 用户定义标量函数示例

当您想返回一个单一值作为结果时，这些非常有用。例如，总销售额、总投资、损失、支出等。

创建无参数标量函数示例

在这个简单的示例中，我们将向您展示如何在不带任何参数的情况下创建求和标量函数。

从下面的聚合查询中，您可以看到我们正在对 MyEmployee 表的年收入进行求和。

-- Scalar example
CREATE FUNCTION NoParameters ()
  RETURNS INT
  AS
    BEGIN 
       RETURN (SELECT SUM([YearlyIncome]) FROM [MyEmployees Table])
    END
Messages
-------
Command(s) completed successfully.

让我向您展示，我们新创建的函数在 Management Studio 中是什么样子

SQL Server Scalar Functions 3

让我们看看求和的输出

SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,dbo.NoParameters() AS [Average Income]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
SQL Scalar Function Result 4

创建带参数的 SQL 标量函数示例

在此示例中，我们将向您展示如何创建带参数的标量函数。从下面的查询中，您可以看到我们正在连接名字和姓氏。

注意：我们正在使用 SPACE 在名字和姓氏之间提供空格。

-- Scalar example
CREATE FUNCTION fullName (@firstName VARCHAR(50), @lastName VARCHAR(50))
  RETURNS VARCHAR(200)
  AS
    BEGIN 
       RETURN (SELECT  @firstName + SPACE(2) + @lastName )
    END

让我们使用下面的查询查看输出

SELECT [EmployeeID]
       -- Passing Parameters to fullname Function
      ,dbo.fullname([FirstName], [lastName]) AS [Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
SQL Server Scalar functions with parameters

创建用户定义标量函数 WHERE 子句

以下 SQL 标量函数 WHERE 子句示例将接受 varchar 作为参数。它会找到销售金额的总和，其 Occupation 等于我们传递的参数。

-- Scalar example
CREATE FUNCTION average (@Occupation VARCHAR(50))
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  SUM([Sales]) FROM [MyEmployees Table]
	        WHERE [Occupation] = @Occupation)
    END

让我们看看输出。

SELECT [Occupation]
      ,SUM([YearlyIncome]) as [Total Income]
      ,SUM([Sales]) AS [Total Sale]
      ,dbo.average([Occupation]) AS [Total Sale from Function]
  FROM [MyEmployees Table]
  group by [Occupation]

在 WHERE 子句中使用示例

在此示例中，我们向您展示了如何在 WHERE 子句中使用标量 UDF

CREATE FUNCTION AverageSale ()
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  AVG([Sales]) FROM [MyEmployees Table])
    END

让我们看看输出

SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
  WHERE [Sales] >= dbo.AverageSale()
SQL Server Scalar Function in Where Clause 7

创建 SQL 用户定义内联表值函数示例

内联 UDF 根据单个 SELECT 语句返回表数据类型作为返回值。

无参数内联示例

在这个简单的示例中，我们将向您展示如何在不带任何参数的情况下创建内联表值函数。从下面的查询中，您可以看到我们正在从 MyEmployee 表中选择前 10 条记录。

-- Inline example

CREATE FUNCTION TopTenCustomers ()
  RETURNS TABLE
  AS
       RETURN (
		   SELECT TOP 10 [FirstName]
		  ,[LastName]
		  ,[Education]
		  ,[Occupation]
		  ,[YearlyIncome]
		  ,[Sales]
		  ,[HireDate]
		  FROM [MyEmployees Table]
               )

让我们看看输出。

SELECT * FROM [dbo].[TopTenCustomers] ()
GO
SQL Server Inline Table valued Function Example 8

SQL Server 用户定义内联函数带参数示例

此示例展示了如何创建带参数的内联表值函数。

从下面的查询中，您可以看到我们正在使用 INNER JOIN 从两个表中选择记录，其中 Occupation 等于我们传递的参数。

-- Inline example

CREATE FUNCTION CustomerbyDepartment (@profession VARCHAR(50))
  RETURNS TABLE
  AS
     RETURN (
		SELECT  [FirstName]
                ,[LastName]
		,[Occupation]
		,[Education]
		,dept.DepartmentName AS Department
		,[YearlyIncome] AS Income
		,[Sales]
		FROM [MyEmployees Table]
		INNER JOIN 
		Department AS dept ON
		  Dept.[id] = [MyEmployees Table].DeptID
		WHERE [Occupation] = @profession
		)

让我们看看内联表值的输出。

SELECT * FROM [dbo].[CustomerbyDepartment] ('Management')
GO
SQL Inline Function with Parameters Example 9

SQL Server 用户定义多选表值示例

多选表值函数返回表格结果集。但是，与内联表值函数不同，我们可以在函数体内使用多个 SELECT 语句。

此多选表值函数示例将向您展示如何在单个 UDF 中使用多个语句。

-- Table Valued example

CREATE FUNCTION CustomerDepartment()
  RETURNS @customers TABLE
  (
  	[EmployeeID] [smallint] NOT NULL,
	[FirstName] [nvarchar](30) NULL,
	[LastName] [nvarchar](40)  NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL,
	[HireDate] [date] NULL,
	[DepartmentName] [VARCHAR](50) NULL
  )
  AS BEGIN
   INSERT INTO @customers
   SELECT  [EmployeeID]
	  ,[FirstName]
          ,[LastName]
	  ,[Education]
	  ,[Occupation]
	  ,[YearlyIncome]
	  ,[Sales]
	  ,[HireDate]
	  ,dept.DepartmentName
	FROM [MyEmployees Table]
	INNER JOIN 
	     Department AS dept ON
		Dept.[id] = [MyEmployees Table].DeptID
    
   -- Updating the Records
   UPDATE @customers SET [YearlyIncome] = [YearlyIncome] + 35200
	WHERE [Sales] > (SELECT AVG(Sales) FROM [MyEmployees Table])
  RETURN
 END

在此示例中，首先，我们使用以下语句创建一个名为 @customers 的表变量。

@customers TABLE

接下来，我们将 [MyEmployee 表] 和 [Department] 表中的记录插入到 @customers 表变量中。

INSERT INTO @customers
   SELECT

接下来，我们正在更新 @customers 表变量中所有年收入高于平均销售额的客户的年收入。

UPDATE @customers SET [YearlyIncome] = [YearlyIncome] + 35200
WHERE [Sales] > (SELECT AVG(Sales) FROM [MyEmployees Table])

让我们看看输出。

SELECT * FROM [dbo].[CustomerDepartment] ()
GO
Table Valued Function Example 10

SQL Server 中的多个用户定义函数

如何在单个 SELECT 语句中使用多个 UDF。

SELECT [EmployeeID]
      ,dbo.fullName([FirstName], [LastName]) AS Name --  First UDF
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
  WHERE [Sales] >= dbo.AverageSale() -- Second UDF
Multiple UDFs 11

一个 SQL UDF 嵌套在另一个 UDF 中

在这个简单的 UDF 示例中，我们将向您展示如何嵌套或将一个用户定义函数插入到另一个用户定义函数中。

CREATE FUNCTION CustDepartment (@education VARCHAR(50))
  RETURNS TABLE
  AS
   RETURN (
	SELECT  dbo.fullName([FirstName],[LastName]) AS NAME
	,[Occupation]
	,[Education]
	,dept.DepartmentName AS Department
	,[YearlyIncome] AS Income
	,[Sales]
	FROM [MyEmployees Table]
	INNER JOIN 
	Department AS dept ON
	  Dept.[id] = [MyEmployees Table].DeptID
	WHERE [Education] = @education
)

让我们看看嵌套函数的输出。

SELECT * FROM [dbo].[CustDepartment] ('Bachelors')
One UDF inside another 12

修改 SQL 中的用户定义函数

在上一节中，我们解释了如何创建用户定义函数。现在我们来看看如何修改用户定义函数，例如重命名、更改和删除现有的 UDF。

要查看 SQL Server 中现有的函数，请选择包含 UDF 的数据库。从下面的屏幕截图中，您可以看到我们的数据库包含我们之前创建的所有函数。

要执行现有函数，请选择要执行的函数（AverageSale）。然后右键单击它，选择 Script Function as -> SELECT To -> New Query Window Editor。

选择“新建查询窗口编辑器”选项后，SSMS 会自动返回查询中的 SELECT 语句。

Alter User Defined Functions 3

使用 SSMS 的用户定义函数属性

首先，右键单击所需名称并选择 SQL Server 属性选项。

Properties 4

单击“属性”选项后，将打开一个名为“函数属性”的新窗口。以下是 SQL Server 函数属性列表，它们是

  • 数据库：显示包含指定函数的数据库名称。这里是 AverageSale。
  • 服务器：当前实例的名称。
  • 用户：显示当前用户的名称
  • 创建日期：显示创建日期。
  • 名称：显示当前名称。即 AverageSale
  • 架构：显示我们为其使用的架构。
  • 系统对象：显示布尔值 True 或 False，指示当前对象是否为系统对象。
  • ANSI NULL：显示布尔值 True 或 False，指示对象是否使用 ANSI NULLs 创建。
  • 函数类型：显示函数是标量函数还是表值函数
Function Properties General Tab 5

使用查询的 SQL 用户定义函数属性

如何使用 OBJECT_DEFINITION 查找用户定义函数的定义。

-- It Returns the AverageSale function Definition  
SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.AverageSale')) AS ObjectDefinition;  
GO
Alter User Defined Functions in SQL 6

以下查询返回 SQL UDF 的其余属性。

-- It Returns the AverageSale Name & it's Properties  
SELECT smo.OBJECT_ID,   
   OBJECT_NAME(smo.OBJECT_ID) AS OBJECT_NAME,   
   Obj.TYPE,   
   Obj.TYPE_DESC,  
   obj.create_date,
   smo.uses_ansi_nulls,  
   smo.uses_quoted_identifier,  
   smo.is_schema_bound,  
   smo.execute_as_principal_id  
FROM sys.sql_modules AS smo  
JOIN sys.objects AS Obj ON smo.OBJECT_ID = Obj.OBJECT_ID  
WHERE smo.OBJECT_ID = OBJECT_ID('dbo.AverageSale')  
ORDER BY Obj.TYPE  
GO
Transact Query to return the Functions properties 7

提示：您还可以通过在 Select 语句中添加：smo.DEFINITION 来返回定义。

在 SQL Server 中重命名用户定义函数

要使用 Microsoft Management Studio 重命名用户定义函数，请右键单击要修改的函数（AverageSale），然后选择“重命名”选项。

Choose the Rename Option 8

单击“重命名”选项后，SSMS 允许我们根据需要进行重命名。

Alter User Defined Functions in SQL 9

修改 SQL Server 中的用户定义标量函数

以下示例将帮助您了解如何使用 Management Studio (SSMS) 和查询修改或更改用户定义函数。

使用 SSMS 更改 UDF

要使用 Management Studio 修改 UDF，请右键单击要更改的函数 (CustomerbyDepartmnet)，然后选择“修改”选项。

选择修改选项后，将打开一个新查询窗口，其中包含以下查询。您可以根据需要进行编辑。

Alter User Defined Functions 11

使用查询更改 SQL 用户定义函数

让我使用 ALTER FUNCTION 修改现有函数。出于演示目的，我们正在连接名字和姓氏作为 NAME。

ALTER FUNCTION [dbo].[CustomerbyDepartment] (@profession VARCHAR(50))
  RETURNS TABLE
  AS
     RETURN (
 SELECT  [FirstName] + ' ' + [LastName] AS Name
 ,[Occupation]
 ,[Education]
 ,dept.DepartmentName AS Department
 ,[YearlyIncome] AS Income
 ,[Sales]
   FROM [MyEmployees Table]
 INNER JOIN 
 Department AS dept ON
 Dept.[id] = [MyEmployees Table].DeptID
 WHERE [Occupation] = @profession
 )

让我们看看输出。

SELECT * FROM [dbo].[CustomerbyDepartment] ('Management')
GO

下面的屏幕截图显示它返回的是 NAME 而不是名字和姓氏。

Alter User Defined Functions 12

删除 SQL Server 中的用户定义函数

使用 Microsoft SQL Server Management Studio (SSMS) 和 Transact Query 删除用户定义函数。在深入了解示例之前，请记住以下限制

  • 如果函数有任何引用或 视图 引用它，则不允许删除该函数。
  • 如果计算列、CHECK 或 Default 约束有任何引用，则无法删除。

为了演示删除操作，我们添加了另外两个标量函数。我们的任务是删除 EmployeeSale 和 SaleEmployees。

使用 SQL Server Management Studio 删除 UDF

要使用 SQL Server Management Studio 删除 UDF，请右键单击函数名称，然后单击“删除”选项。这里，我们要删除 SaleEmployees（标量）。

Alter User Defined Functions 14

选择删除选项将打开“删除对象”窗口。单击“显示依赖项”按钮以检查依赖项，然后单击“确定”删除它。

Choose Delete Option

使用查询删除函数

如何使用 SQL Server DROP FUNCTION 删除用户定义的函数。

DROP FUNCTION [dbo].[EmployeesSale]
GO

提示：最好使用 IF OBJECT_ID (N'EmployeeSale', N'IF') IS NOT NULL 检查它是否存在于数据库中。

让我们看看当我们调用已删除的函数时会发生什么。

Alter User Defined Functions 16

从上面的屏幕截图中，我们的对象资源管理器中没有 EmployeeSale，查询它会抛出错误。

SQL Server 中用户定义函数的限制

以下是限制列表，但不限于此。

  1. 我们不能在 SQL Server 中使用简称为 UDF 的函数来修改数据库状态。
  2. UDF 无法返回多个结果集。
  3. UDF 不支持错误处理，例如 TRY..CACHE、RAISEERROR 或 @ERROR
  4. 我们不能从 UDF 调用存储过程，但可以调用扩展存储过程。
  5. 它们不支持临时表，但允许表变量。
  6. UDF 中不允许使用 SET 语句
  7. FOR XML 子句不允许在其中使用。

评论已关闭。