SQL Server用户定义的函数

  • 一、背景知识
    • 1.1、用户定义函数的优点
    • 1.2、函数类型
    • 1.3、指引
    • 1.4、函数中的有效语句
    • 1.5、架构绑定函数
    • 1.6、指定参数
  • 二、创建用户定义函数
    • 2.1、限制和权限
    • 2.2、标量函数示例（标量 UDF）
    • 2.3、表值函数示例
      • 2.3.1、内联表值函数 （TVF）
      • 2.3.2、多语句表值函数 （MSTVF）
  • 三、修改用户定义的函数
  • 四、删除用户定义的函数
  • 五、执行用户定义的函数
  • 六、重命名用户定义函数
  • 七、查看用户定义的函数
    • 7.1、获取函数的定义和属性
    • 7.2、获取函数的依赖项

一、背景知识

与编程语言中的函数一样，SQL Server 用户定义函数是接受参数、执行操作（如复杂计算）并将该操作的结果作为值返回的例程。返回值可以是单个标量值，也可以是结果集。

1.1、用户定义函数的优点

  • 模块化编程。可以创建一次函数，将其存储在数据库中，并在程序中调用它任意次数。可以独立于程序源代码修改用户定义的函数。

  • 执行速度更快。与存储过程类似，Transact-SQL 用户定义函数通过缓存计划并重用它们进行重复执行来降低 Transact-SQL 代码的编译成本。这意味着用户定义的函数不需要在每次使用时重新解析和重新优化，从而缩短执行时间。

  • 与 Transact-SQL 函数相比，CLR 函数在计算任务、字符串操作和业务逻辑方面具有显著的性能优势。事务处理 SQL 函数更适合数据访问密集型逻辑。

  • 减少网络流量。基于某些无法在单个标量表达式中表示的复杂约束筛选数据的操作可以表示为函数。然后可以在 WHERE 子句中调用该函数，以减少发送到客户端的行数。

查询中的事务处理 SQL UDF 只能在单个线程（串行执行计划）上执行。因此，使用 UDF 会抑制并行查询处理。

1.2、函数类型

  • 标量函数。用户定义的标量函数返回 RETURNS 子句中定义的类型的单个数据值。对于内联标量函数，返回的标量值是单个语句的结果。对于多语句标量函数，函数体可以包含一系列返回单个值的 Transact-SQL 语句。返回类型可以是除文本、ntext、图像、光标和时间戳之外的任何数据类型。

  • 表值函数。用户定义的表值函数 （TVF） 返回表数据类型。对于内联表值函数，没有函数体;该表是单个 SELECT 语句的结果集。有关示例，请参阅创建用户定义函数（数据库引擎）。

  • 系统功能。SQL Server 提供了许多可用于执行各种操作的系统函数；它们无法修改。

1.3、指引

导致语句被取消并继续执行模块中的下一条语句（如触发器或存储过程）的 Transact-SQL 错误在函数中处理方式不同。在函数中，此类错误会导致函数的执行停止。这反过来会导致调用函数的语句被取消。

块中的语句不能有任何副作用。函数副作用是对具有函数范围之外的资源状态的任何永久更改，例如对数据库表的修改。函数中的语句可以进行的唯一更改是对函数局部对象的更改，例如局部游标或变量。对数据库表的修改、对非函数本地游标的操作、发送电子邮件、尝试修改目录以及生成返回给用户的结果集是无法在函数中执行的操作的示例。

如果语句对发出该语句时不存在的资源产生副作用，则 SQL Server 将执行该语句。但是，SQL Server 在调用函数时不会执行该函数。

查询中指定的函数的执行次数可能因优化程序构建的执行计划而异。例如，子句中的子查询调用的函数。子查询及其函数的执行次数可能因优化程序选择的不同访问路径而异。

确定性函数必须是架构绑定的。在创建确定性函数时使用SCHEMABINDING子句。

1.4、函数中的有效语句

在函数中有效的语句类型包括：

  • DECLARE语句可用于定义函数的本地数据变量和游标。

  • 将值赋值到函数的本地对象，例如用于将值赋值给标量变量和表局部变量。

  • 引用在函数中声明、打开、关闭和释放的本地游标的游标操作。 不允许使用将数据返回到客户端的语句。只允许使用该子句将值赋值给局部变量的 FETCH 语句。

  • 流控制语句（语句除外）。

  • SELECT包含选择列表的语句，这些表达式将值分配给函数的局部变量。

  • UPDATE语句修改函数的局部表变量。

  • EXECUTE调用扩展存储过程的语句。

内置系统功能：
（1）以下非确定性内置函数可用于事务处理 SQL 用户定义函数。

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

（2）以下非确定性内置函数不能在 Transact-SQL 用户定义函数中使用。

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

1.5、架构绑定函数

CREATE FUNCTION支持将函数绑定到它引用的任何对象（如表、视图和其他用户定义函数）的架构的子句。尝试更改或删除架构绑定函数引用的任何对象失败。

必须先满足以下条件，然后才能在创建函数中指定：

  • 函数引用的所有视图和用户定义函数都必须是架构绑定的。

  • 函数引用的所有对象必须与函数位于同一数据库中。必须使用由一部分或两部分组成的名称来引用对象。

  • 必须对函数中引用的所有对象（表、视图和用户定义函数）具有权限。

可以使用 ALTER FUNCTION、ALTER FUNCTIONWITH SCHEMABINDING 删除架构绑定。该语句应重新定义函数而不指定 。

1.6、指定参数

用户定义的函数采用零个或多个输入参数，并返回标量值或表。一个函数最多可以有 1024 个输入参数。当函数的参数具有默认值时，调用函数时必须指定关键字 DEFAULT 才能获取默认值。此行为不同于用户定义存储过程中具有默认值的参数，在用户定义存储过程中，省略参数也意味着默认值。用户定义的函数不支持输出参数。

二、创建用户定义函数

2.1、限制和权限

限制：

  • 用户定义的函数不能用于执行修改数据库状态的操作。

  • 用户定义的函数不能包含以表为目标的子句。

  • 用户定义的函数不能返回多个结果集。如果需要返回多个结果集，请使用存储过程。

  • 错误处理在用户定义的函数中受到限制。UDF 不支持TRY…CATCH@ERRORRAISERROR。

  • 用户定义函数不能调用存储过程，但可以调用扩展存储过程。

  • 用户定义的函数不能使用动态 SQL 或临时表。允许使用表变量。

  • SET语句不允许在用户定义的函数中使用。

  • 不允许使用FOR XML子句。

  • 用户定义的函数可以嵌套;也就是说，一个用户定义的函数可以调用另一个用户定义的函数。嵌套级别在被调用函数开始执行时递增，在被调用函数完成执行时递减。用户定义的函数最多可嵌套 32 个级别。超过最大嵌套级别会导致整个调用函数链失败。对来自 Transact-SQL 用户定义函数的托管代码的任何引用都计为 32 级嵌套限制中的一个级别。从托管代码中调用的方法不计入此限制。

  • 以下服务代理语句不能包含在 Transact-SQL 用户定义函数的定义中：BEGIN DIALOG CONVERSATION、END CONVERSATION、GET CONVERSATION GROUP、MOVE CONVERSATION、RECEIVE、SEND。

权限：
需要数据库中的权限以及对在其中创建函数的架构的权限。如果函数指定用户定义类型，则需要CREATE FUNCTIONALTEREXECUTE该类型的权限。

2.2、标量函数示例（标量 UDF）

创建一个多语句标量函数（标量 UDF）。该函数采用一个输入值 a 并返回单个数据值，即库存中指定产品的聚合数量。

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULLDROP FUNCTION ufnGetInventoryStock;GOCREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)RETURNS intAS-- Returns the stock level for the product.BEGINDECLARE @ret int;SELECT @ret = SUM(p.Quantity)FROM Production.ProductInventory pWHERE p.ProductID = @ProductIDAND p.LocationID = '6'; IF (@ret IS NULL)SET @ret = 0;RETURN @ret;END;

使用该函数返回介于 75 和 80 之间的产品的当前库存数量。

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupplyFROM Production.ProductWHERE ProductModelID BETWEEN 75 and 80;

2.3、表值函数示例

2.3.1、内联表值函数 （TVF）

创建一个内联表值函数 （TVF）。该函数采用一个输入参数、一个客户（商店）ID，并返回列 、 和销售到商店的每个产品的年初至今销售额的汇总。

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULLDROP FUNCTION Sales.ufn_SalesByStore;GOCREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)RETURNS TABLEASRETURN(SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'FROM Production.Product AS PJOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductIDJOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderIDJOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerIDWHERE C.StoreID = @storeidGROUP BY P.ProductID, P.Name);

调用该函数并指定客户 ID 602。

SELECT * FROM Sales.ufn_SalesByStore (602);

2.3.2、多语句表值函数 （MSTVF）

创建一个多语句表值函数 （MSTVF）。该函数采用单个输入参数 an，并返回直接或间接向指定员工报告的所有员工的列表。然后调用该函数，指定员工 ID 109。

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULLDROP FUNCTION dbo.ufn_FindReports;GOCREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)RETURNS @retFindReports TABLE(EmployeeID int primary key NOT NULL,FirstName nvarchar(255) NOT NULL,LastName nvarchar(255) NOT NULL,JobTitle nvarchar(50) NOT NULL,RecursionLevel int NOT NULL)--Returns a result set that lists all the employees who report to the--specific employee directly or indirectly.*/ASBEGINWITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columnsAS (SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager nFROM HumanResources.Employee eINNER JOIN Person.Person pON p.BusinessEntityID = e.BusinessEntityIDWHERE e.BusinessEntityID = @InEmpIDUNION ALLSELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchorFROM HumanResources.Employee eINNER JOIN EMP_cteON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNodeINNER JOIN Person.Person pON p.BusinessEntityID = e.BusinessEntityID)-- copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM EMP_cte RETURNEND;GO

调用该函数并指定员工 ID 1。

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevelFROM dbo.ufn_FindReports(1);

三、修改用户定义的函数

修改用户定义函数（如下所述）不会更改函数的权限，也不会影响任何依赖函数、存储过程或触发器。
ALTER 函数不能用于执行以下任何操作：

  • 将标量值函数更改为表值函数，反之亦然。

  • 将内联函数更改为多语句函数，反之亦然。

  • 将事务处理 SQL 函数更改为 CLR 函数，反之亦然。

权限：需要对函数或架构的 ALTER 权限。如果函数指定用户定义类型，则需要对该类型具有 EXECUTE 权限。

（1）更改标量值函数。

-- Scalar-Valued FunctionUSE [AdventureWorks2012]GOALTER FUNCTION [dbo].[ufnGetAccountingEndDate]()RETURNS [datetime]ASBEGINRETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));END;

（2）更改表值函数。

-- Table-Valued FunctionUSE [AdventureWorks2012]GOALTER FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)RETURNS @retContactInformation TABLE(-- Columns returned by the function[PersonID] int NOT NULL,[FirstName] [nvarchar](50) NULL,[LastName] [nvarchar](50) NULL,[JobTitle] [nvarchar](50) NULL,[BusinessEntityType] [nvarchar](50) NULL)AS-- Returns the first name, last name, job title and business entity type for the specified contact.-- Since a contact can serve multiple roles, more than one row may be returned.BEGINIF @PersonID IS NOT NULLBEGIN IF EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[BusinessEntityID] = @PersonID) INSERT INTO @retContactInformationSELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'FROM [HumanResources].[Employee] AS eINNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]WHERE e.[BusinessEntityID] = @PersonID; IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = v.[BusinessEntityID] WHERE bec.[PersonID] = @PersonID) INSERT INTO @retContactInformationSELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact'FROM [Purchasing].[Vendor] AS vINNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = v.[BusinessEntityID]INNER JOIN [Person].ContactType ct ON ct.[ContactTypeID] = bec.[ContactTypeID]INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID]WHERE bec.[PersonID] = @PersonID; IF EXISTS(SELECT * FROM [Sales].[Store] AS s INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID] WHERE bec.[PersonID] = @PersonID) INSERT INTO @retContactInformationSELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact'FROM [Sales].[Store] AS sINNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID]INNER JOIN [Person].ContactType ct ON ct.[ContactTypeID] = bec.[ContactTypeID]INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID]WHERE bec.[PersonID] = @PersonID; IF EXISTS(SELECT * FROM [Person].[Person] AS p INNER JOIN [Sales].[Customer] AS c ON c.[PersonID] = p.[BusinessEntityID] WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) INSERT INTO @retContactInformationSELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer'FROM [Person].[Person] AS pINNER JOIN [Sales].[Customer] AS c ON c.[PersonID] = p.[BusinessEntityID]WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; ENDRETURN;END;

四、删除用户定义的函数

限制：

  • 如果数据库中存在引用此函数且是使用 SCHEMABINDING 创建的 Transact-SQL 函数或视图，或者存在引用该函数的计算列、CHECK 约束或 DEFAULT 约束，则无法删除该函数。

  • 如果存在引用此函数并已编制索引的计算列，则无法删除该函数。

权限：需要对函数所属架构的 ALTER 权限，或对函数具有 CONTROL 权限。

（1）创建一个用户定义的函数：

-- creates function called "Sales.ufn_SalesByStore"USE AdventureWorks2012;GOCREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)RETURNS TABLEASRETURN(SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'FROM Production.Product AS PJOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductIDJOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderIDJOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerIDWHERE C.StoreID = @storeidGROUP BY P.ProductID, P.Name);GO

（2）删除在上面示例中创建的用户定义函数：

USE AdventureWorks2012;GO-- determines if function exists in databaseIF OBJECT_ID (N'Sales.fn_SalesByStore', N'IF') IS NOT NULL-- deletes functionDROP FUNCTION Sales.fn_SalesByStore;GO

五、执行用户定义的函数

限制：
在 Transact-SQL 中，可以使用值或使用 @parameter_name=value 来提供参数。参数不是事务的一部分;因此，如果在稍后回滚的事务中更改了参数，则该参数的值不会还原为其以前的值。返回给调用方的值始终是模块返回时的值。

权限：
运行 EXECUTE 语句不需要权限。但是，需要对 EXECUTE 字符串中引用的安全对象具有权限。例如，如果字符串包含 INSERT 语句，则 EXECUTE 语句的调用方必须对目标表具有 INSERT 权限。在遇到 EXECUTE 语句时检查权限，即使 EXECUTE 语句包含在模块中也是如此。

示例;
此示例使用大多数版本的 中可用的标量值函数。该函数的目的是从给定整数返回销售状态的文本值。通过将整数 1 到 7 传递给参数来改变示例。

USE [AdventureWorks2016CTP3]GO-- Declare a variable to return the results of the function.DECLARE @ret nvarchar(15);-- Execute the function while passing a value to the @status parameterEXEC @ret = dbo.ufnGetSalesOrderStatusText @Status = 5;-- View the returned value.The Execute and Select statements must be executed at the same time.SELECT N'Order Status: ' + @ret;-- Result:-- Order Status: Shipped

六、重命名用户定义函数

限制：

  • 函数名称必须符合标识符规则。

  • 重命名用户定义函数不会更改 sys.sql_modules 目录视图的定义列中相应对象名称的名称。因此，建议不要重命名此对象类型。而是删除存储过程，然后使用其新名称重新创建存储过程。

  • 更改用户定义函数的名称或定义可能会导致依赖对象在对象未更新以反映对函数所做的更改时失败。

权限：
删除函数需要对函数所属架构具有 ALTER 权限，或者需要对函数具有 CONTROL 权限。若要重新创建函数，需要数据库中的 CREATE FUNCTION 权限和要在其中创建函数的架构的 ALTER 权限。

注意：若要使用 Transact-SQL 重命名用户定义函数，必须先删除现有函数，然后使用新名称重新创建它。确保使用函数旧名称的所有代码和应用程序现在都使用新名称。

七、查看用户定义的函数

获取有关 SQL Server 中用户定义函数的定义或属性的信息。可能需要查看函数的定义，以了解其数据是如何从源表派生的，或者查看函数定义的数据。

如果更改函数引用的对象的名称，则必须修改该函数，使其文本反映新名称。因此，在重命名对象之前，请先显示对象的依赖项，以确定是否有任何函数受到建议更改的影响。

权限：
用于查找函数上的所有依赖项需要对数据库具有 VIEW DEFINITION 权限，对数据库具有 SELECT 权限。系统对象定义（如 OBJECT_DEFINITION 中返回的那些定义）是公开可见的。

7.1、获取函数的定义和属性

（1）获取函数的定义和属性。

USE AdventureWorks2012;GO-- Get the function name, definition, and relevant propertiesSELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition, sm.uses_ansi_nulls, sm.uses_quoted_identifier, sm.is_schema_bound, sm.execute_as_principal_id-- using the two system tables sys.sql_modules and sys.objectsFROM sys.sql_modules AS smJOIN sys.objects AS o ON sm.object_id = o.object_id-- from the function 'dbo.ufnGetProductDealerPrice'WHERE sm.object_id = OBJECT_ID('dbo.ufnGetProductDealerPrice')ORDER BY o.type;GO

（2）获取示例函数的定义。

USE AdventureWorks2012;GO-- Get the definition of the function dbo.ufnGetProductDealerPriceSELECT OBJECT_DEFINITION (OBJECT_ID('dbo.ufnGetProductDealerPrice')) AS ObjectDefinition;GO

7.2、获取函数的依赖项

USE AdventureWorks2012;GO-- Get all of the dependency informationSELECT OBJECT_NAME(sed.referencing_id) AS referencing_entity_name,o.type_desc AS referencing_desciption,COALESCE(COL_NAME(sed.referencing_id, sed.referencing_minor_id), '(n/a)') AS referencing_minor_id,sed.referencing_class_desc, sed.referenced_class_desc,sed.referenced_server_name, sed.referenced_database_name, sed.referenced_schema_name,sed.referenced_entity_name,COALESCE(COL_NAME(sed.referenced_id, sed.referenced_minor_id), '(n/a)') AS referenced_column_name,sed.is_caller_dependent, sed.is_ambiguous-- from the two system tables sys.sql_expression_dependencies and sys.objectFROM sys.sql_expression_dependencies AS sedINNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id-- on the function dbo.ufnGetProductDealerPriceWHERE sed.referencing_id = OBJECT_ID('dbo.ufnGetProductDealerPrice');GO