CREATE FUNCTION（Azure Synapse Analytics 和 Microsoft Fabric）

项目
04/26/2024

适用于：Azure Synapse Analytics 分析平台系统 (PDW) Microsoft Fabric 中的 SQL 分析端点 Microsoft Fabric 中的仓库

在 Azure Synapse Analytics、Analytics Platform System （PDW）或 Microsoft Fabric 中创建用户定义的函数（UDF）。用户定义函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的 Transact-SQL 例程。

在 Analytics Platform System (PDW) 中，返回值必须是标量（单个）值。
在 Azure Synapse Analytics 中，CREATE FUNCTION 可以通过使用内联表值函数（预览）的语法来返回表，也可以通过使用标量函数的语法来返回单个值。
在 Azure Synapse Analytics 的 Microsoft Fabric 和无服务器 SQL 池中，CREATE FUNCTION 可以创建内联表值函数，但不能创建标量函数。用户定义表值函数 (TVF) 返回 table 数据类型。

使用此语句可以创建可通过以下方式使用的重复使用的例程：
在 Transact-SQL 语句中，例如 SELECT
在调用该函数的应用程序中
在另一个用户定义函数的定义中
用于为列定义 CHECK 约束
用于替换存储过程
使用内联函数作为安全策略的筛选器谓词

Transact-SQL 语法约定

语法

标量函数语法

-- Transact-SQL Scalar Function Syntax  (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics or Microsoft Fabric

CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  
  
<function_option>::=   
{  
    [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}

内联表值函数语法

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics and Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

参数

schema_name

用户定义函数所属的架构的名称。

function_name

用户定义的函数的名称。函数名称必须符合标识符规则，并且在数据库中以及对其架构来说是唯一的。

注意

即使未指定参数，函数名称后也需要加上括号。

@parameter_name

用户定义函数中的参数。可声明一个或多个参数。

一个函数最多可以有 2,100 个参数。执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值。

通过将 at 符号 (@) 用作第一个字符来指定参数名称。参数名称必须符合标识符规则。参数是对应于函数的局部参数；其他函数中可使用相同的参数名称。参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。

注意

ANSI_WARNINGS 在存储过程、用户定义的函数中传递参数或在批处理语句中声明和设置变量时，不遵循此条件。例如，如果将一个变量定义为 char(3)，然后将其值设置为大于三个字符，则数据会被截断为定义的大小，并且 INSERT 或 UPDATE 语句可以成功执行。

parameter_data_type

参数数据类型。对于 Transact-SQL 函数，允许在 Azure Synapse Analytics 中支持所有标量数据类型。不支持 timestamp (rowversion) 数据类型。

[ =default ]

参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行函数。

如果函数的参数有默认值，则调用该函数以检索默认值时，必须指定关键字 DEFAULT。此行为与在存储过程中使用具有默认值的参数不同，在后一种情况下，不提供参数同样意味着使用默认值。

return_data_type

标量用户定义函数的返回值。对于 Transact-SQL 函数，允许在 Azure Synapse Analytics 中支持所有标量数据类型。 rowversion/时间戳数据类型不是受支持的类型。不允许光标和表的非标量类型。

function_body

Transact-SQL 语句系列。 function_body不能包含 SELECT 语句，也不能引用数据库数据。 function_body不能引用表或视图。 function body 可以调用其他确定性的函数但不能调用不确定性函数。

在标量函数中，function_body 是一系列 Transact-SQL 语句，这些语句一起使用可计算出标量值。

scalar_expression

指定标量函数返回的标量值。

select_stmt

定义内联表值函数的返回值的单个 SELECT 语句。对于内联表值函数，没有函数体;表是单个 SELECT 语句的结果集。

TABLE

指定表值函数 (TVF) 的返回值为表。只有常量和 @local_variables 可以传递到 TVF。

在内联 TVF（预览版）中，TABLE 返回值通过单个 SELECT 语句定义。内联函数没有关联的返回变量。

<function_option>

指定函数具有以下一个或多个选项。

SCHEMABINDING

指定将函数绑定到其引用的数据库对象。如果指定了 SCHEMABINDING，则不能按照将影响函数定义的方式修改基对象。必须首先修改或删除函数定义本身，才能删除将要修改的对象的依赖关系。

只有发生下列操作之一时，才会删除函数与其引用对象的绑定：

删除函数。
在未指定 SCHEMABINDING 选项的情况下，使用 ALTER 语句修改函数。

只有满足以下条件时，函数才能绑定到架构：

该函数引用的任何用户定义函数也绑定到架构。
使用由一个部分或两个部分组成的名称引用该函数和由该函数引用的其他 UDF。
UDF 的正文中只能引用同一数据库中的内置函数和其他 UDF。
执行 CREATE FUNCTION 该语句的用户对函数引用的数据库对象具有 REFERENCES 权限。

若要删除 SCHEMABINDING，请使用 ALTER。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

指定标量值函数的 OnNULLCall 属性。如果未指定， CALLED ON NULL INPUT 则默认为隐含，即使作为参数传递，函数正文也会执行 NULL 。

最佳做法

如果用户定义函数不是使用 SCHEMABINDING 子句创建的，则对基础对象进行的任何更改可能会影响函数定义并在调用函数时可能导致意外结果。我们建议您实现以下方法之一，以便确保函数不会由于对于其基础对象的更改而过期：

创建函数时指定 WITH SCHEMABINDING 子句。这确保除非也修改了函数，否则无法修改在函数定义中引用的对象。

互操作性

下列语句在标量值函数中有效：

赋值语句。
TRY...CATCH 语句以外的流控制语句。
定义本地数据变量的 DECLARE 语句。

在内联表值函数（预览）中，只允许使用单个 SELECT 语句。

限制

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

用户定义函数可以嵌套；也就是说，用户定义函数可相互调用。被调用函数开始执行时，嵌套级别将增加；被调用函数执行结束后，嵌套级别将减少。用户定义函数的嵌套级别最多可达 32 级。如果超出最大嵌套级别数，整个调用函数链将失败。

无法在 Azure Synapse Analytics 的无服务器 SQL 池的 master 数据库中创建对象（包括函数）。

元数据

本部分列出可用于返回与用户定义函数有关的元数据的系统目录视图。

sys.sql_modules：显示 Transact-SQL 用户定义函数的定义。例如：

SELECT definition, type   
FROM sys.sql_modules AS m  
JOIN sys.objects AS o   
    ON m.object_id = o.object_id   
    AND type = ('FN');

sys.parameters：显示用户定义函数中定义的参数的有关信息。
sys.sql_expression_dependencies：显示函数所引用的基础对象。

权限

需要在数据库中具有 CREATE FUNCTION 权限，并对创建函数时所在的架构具有 ALTER 权限。

示例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

A. 使用标量值用户定义函数更改数据类型

此简单函数将 int 数据类型作为输入，并返回 decimal(10,2) 数据类型作为输入。

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN  
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  
  
SELECT dbo.ConvertInput(15) AS 'ConvertedValue';

注意

标量函数在无服务器 SQL 池或 Microsoft Fabric 中不可用。

示例：Azure Synapse Analytics

A. 创建内联表值函数

下面的示例创建一个内联表值函数，以返回按 objectType 参数筛选的模块的某些关键信息。它包含一个默认值，用于在使用参数调用 DEFAULT 函数时返回所有模块。此示例使用元数据中提到的一些系统目录视图。

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

然后，可以调用该函数以返回所有视图 (V) 对象：

select * from dbo.ModulesByType('V');

注意

内联表值函数在无服务器 SQL 池中可用，但在专用表 SQL 池中是预览版。

B. 合并内联表值函数的结果

这个简单的示例使用以前创建的内联 TVF 来演示如何使用 CROSS APPLY 将其结果与其他表合并。在这里，我们将从这两个sys.objects列中选择所有列，并为类型列上匹配的所有行的结果ModulesByType。有关使用 APPLY 的详细信息，请参阅 FROM 子句以及 JOIN、APPLY、PIVOT。

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

注意