CREATE FUNCTION

适用于:Microsoft Fabric 中的 SQL 分析端点和 Microsoft Fabric 中的 Warehouse

CREATE FUNCTION 创建内联表值函数和标量函数。

注意

外部UDF是Fabric Data Warehouse中的预览功能。

本文适用于Microsoft Fabric Data Warehouse以及Fabric项的SQL分析端点。 其他平台请参见 CREATE FUNCTION (Transact-SQL)。

用户自定义函数是一种 Transact-SQL 例程,它接受参数,执行诸如复杂计算等操作,并将该操作的结果返回为值。 标量函数返回标量值,例如数字或字符串。 用户定义的表值函数 (TVF) 返回表。

使用 CREATE FUNCTION 它可以创建一个可重复使用的T-SQL例程,你可以以下方式使用:

  • 在 Transact-SQL 语句中,如 SELECT。
  • 在仓库中,Transact-SQL 数据操作语句(DML) UPDATE如 、 INSERT、 DELETE和 。
  • 在应用程序中调用该函数。
  • 在另一个用户自定义函数的定义中。
  • 用来替换存储过程。

如果没有该名称的函数,指定 CREATE OR ALTER FUNCTION 创建一个新函数,或者在一个语句中修改现有函数。

Transact-SQL 语法约定

语法

标量函数语法

CREATE [ OR ALTER ] 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>::=
{
    [ INLINE = AUTO ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

内联表值函数语法

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

外部函数语法

外部 UDF 是一个引用外部 Fabric 用户数据函数的函数。

CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]

注意

外部UDF是Fabric Data Warehouse中的预览功能。

该CREATE FUNCTION语句自动从引用的外部 Fabric 用户数据函数的定义中推导出返回类型、参数和参数类型。 如果外部 Fabric 用户数据函数的签名发生变化,您必须重新创建该函数,使其定义与更新后的签名同步。

参数

schema_name

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

function_name

用户定义的函数的名称。 函数名称必须遵循标识符规则,并在数据库及其模式中保持唯一。

对于原生 T-SQL 标量和内联表值函数定义,即使没有参数,也请在函数名后加上括号。 外部函数定义使用单独 AS EXTERNAL FUNCTION 的语法。

@ parameter_name

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

一个函数最多可包含2100个参数。 当用户或应用调用函数时,除非你为该参数定义了默认值,否则必须为每个声明的参数提供值。

通过将 at 符号 (@) 用作第一个字符来指定参数名称。 参数名称必须遵循标识符的规则。 该函数的参数是局部参数。 您可以在其他函数中使用相同的参数名称。 参数只能替换常量。 它们不能替代表名、列名或其他数据库对象的名称。

ANSI_WARNINGS 当您在存储过程、用户自定义函数中传递参数,或在批处理语句中声明和设置变量时,不会被执行。 例如,如果你定义一个变量为 char(3),然后将其设置为大于三个字符的值,数据就会被截断到定义大小,SQL 语句就会成功。

parameter_data_type

参数数据类型。 对于 Transact-SQL 函数,允许 所有标量数据类型 。

[ = 默认 ]

参数的默认值。 如果你定义了一个 默认 值,就可以在不指定该参数值的情况下执行函数。

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

return_data_type

标量用户定义函数的返回值。

对于Fabric Data Warehouse中的函数,你可以使用除行版本/时间戳以外的所有数据类型。 非标量类型如 表格 不被允许。

对于外部 UDF(预览),该 RETURNS 条款为可选。 如果省略,返回类型将自动从引用的 Fabric 用户数据函数的返回类型推断出来。 你可以指定 RETURNS 条款覆盖推断返回类型。

function_body

一系列 Transact-SQL 语句。

在标量函数中, function_body 是一系列 Transact-SQL 语句,它们共同计算为标量值,其中包括:

  • 单语句表达式
  • 多语句表达式(IF/ELSE 及 BEGIN/END 块)
  • 局部变量
  • 对可用的内置 SQL 函数的调用
  • 调用其他 UDF
  • SELECT 语句,以及对表、视图和内联表值函数的引用
  • 流量控制语句,包括 IF/ELSE、 WHILE、 BREAK、 和 CONTINUERETURN

对于外部UDF(预览版),你无法指定function_body,因为函数实现是在关联的Fabric用户数据函数中外部定义的。 数据库仅存储函数元数据。 可执行逻辑位于外部函数定义中。

scalar_expression

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

select_stmt

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

TABLE

指定表值函数 (TVF) 的返回值为表。 你只能把常量和@local_variables 传递给TVF。

在内联TVF(预览)中,你通过一个TABLE语句定义SELECT返回值。 内联函数没有关联的 return 变量。

<function_option>

Fabric Data Warehouse中,ENCRYPTION和 EXECUTE AS 关键字不被支持。

支持的功能选项包括:

直列 = 自动

规定是否可以创建或修改标量用户定义函数,不受内联要求影响。 INLINE 条款是可选的。 对于可列标量UDF,指定 INLINE = AUTO 不改变其可线性或执行行为。

有关支持的独立调用模式,请参阅 为独立执行创建标量 UDF。

SCHEMABINDING

指定将函数绑定到其引用的数据库对象。 当你指定 SCHEMABINDING时,你无法修改底层对象(例如视图或表),从而影响函数定义。 你必须先修改或放弃函数定义,以消除对你想修改对象的依赖。

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

  • 你去掉这个函数。

  • 你 ALTER 输入函数语句并移除选项 SCHEMABINDING 。

只有当满足以下条件时,函数才能进行模式绑定:

  • 该函数引用的任何用户自定义函数也是模式绑定的。

  • 该函数通过使用两部分名称来引用对象。

  • 在UDF正体中,你只能引用同一数据库中的内置函数和其他UDF。

  • 执行该 CREATE FUNCTION 语句的用户对该函数引用的数据库对象拥有REFERENCES权限。

若要删除 SCHEMABINDING,请使用 ALTER。

在 NULL 输入时返回 NULL |在 NULL 输入时调用

指定 OnNULLCall 标量值函数的属性。 如果你不指定这个属性,默认 CALLED ON NULL INPUT 是隐含的,即使 作为参数传递,函数 body也会 NULL 执行。

作为外部 FUNCTION

引用Fabric用户数据函数(UDF)。 当调用该函数时,执行会委托给被引用的 Fabric 用户数据函数,逻辑在此执行,结果返回给 T-SQL 调用者。

该条款接受以下参数:

  • 函数集名称:包含 Fabric 用户数据函数的函数集名称。 每个 Fabric 用户数据函数都必须属于一个函数集。
  • 函数名称:要引用的 Fabric 用户数据函数的名称。

以下语法展示了这些参数的具体定义方式:

CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>

由于函数是远程执行的,调用外部函数通常比执行原生 T-SQL 用户自定义函数时延迟更高。 当所需的业务逻辑在 Fabric 中实现且无法直接用 T-SQL 表达时,请使用外部函数。

该CREATE FUNCTION语句自动从引用的外部 Fabric 用户数据函数的定义中推导出返回类型、参数和参数类型。 如果外部 Fabric 用户数据函数的签名发生变化,您必须重新创建该函数,使其定义与更新后的签名同步。

最佳做法

  • 把用户自定义函数写成可内嵌的。 关于内联概念的更多信息,请参见 标量UDF内联。 关于如何使标量UDF可线化的示例,请参见在 Microsoft Fabric Data Warehouse 中创建标量UDF。

  • 如果你没有创建带有schemabinding的用户自定义函数,底层对象的更改可能会影响函数的定义,并在调用该函数时引发意想不到的结果。 当你指定 WITH SCHEMABINDING 创建函数的时间时,你确保后续对底层对象的更改不会改变或破坏函数的行为。

  • 尽可能将逻辑实现为 T-SQL 用户定义函数(UDF)。 仅用于无法用 T-SQL 语言实现的功能,使用外部 UDF。 这种方法有助于最大限度地减少远程执行开销,并通常提供更好的查询性能。

互操作性

内联表值用户定义函数

内联表值函数只接受一个 SELECT 语句。

标量用户定义函数

  • 当标量UDF是非线列时, CREATE OR ALTER 语句会拒绝带有自定义错误消息的DDL语句。

  • 非线性函数可以通过指定 INLINE=AUTO 函数选项来创建。 非线化函数不能在用户表的查询中使用 SELECT ... FROM 。 INLINE=AUTO 这并不意味着功能是不可分割的。

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

    • 赋值语句。
    • 除了和GOTO语句外,流量控制语句TRY...CATCH。
    • DECLARE 定义本地数据变量的语句。
    • 调用内置函数。
    • 引用表/视图/iTVFs/其他标量UDF。
  • DML 语句不允许在标量用户自定义函数中使用。

元数据

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

  • sys.sql_modules:显示 Transact-SQL 用户自定义函数的定义以及可内联性信息。 例如:

      SELECT 
          SCHEMA_NAME(o.schema_id) AS SchemaName,
          o.name AS FunctionName,
          m.definition AS FunctionDefinition,
          m.is_inlineable AS Inlineable,
          m.inline_eligibility_mask AS InlineEligibilityMask
      FROM sys.objects o
      JOIN sys.sql_modules m ON o.object_id = m.object_id
      WHERE o.type = 'FN';
    
  • sys.parameters:显示用户自定义函数中定义的参数信息。 以下示例使用 sys.parameters 目录视图显示函数签名,包括返回类型和参数数据类型:

     WITH metadata AS
     (
         SELECT  o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name
         FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id
         WHERE o.type IN ('FN', 'XF')
     ),
     signature AS (
     SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT(
         SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id),
         ') -> ',  MAX(CASE WHEN parameter_id = 0 THEN type_name END)
     ) AS signature
     FROM metadata GROUP BY schema_id, object_id, name
     )
     SELECT *
     FROM signature;
    
  • sys.sql_expression_dependencies:显示函数引用的底层对象。

权限

Fabric 工作区管理员、成员和参与者角色的成员可以创建函数。

标量UDF的内联

Microsoft Fabric Data Warehouse在查询编译过程中自动内联符合条件的标量 UDF。

表达式块内联化将函数的语句(包括分支和循环)编译为标量表达式,作为调用查询的一部分执行。 标量 UDF 内联化将函数体转换为表达式或子查询,作为调用查询的一部分。

一个函数可以满足这两种技术的要求,但引擎会根据其定义选择其中一种。 同一查询中的不同函数可以使用不同的技术。

随着Microsoft Fabric Data Warehouse扩展内联功能,现有函数会自动获得这些功能。

支持的函数构造

下表比较了支持的函数体结构。

函数-体构造 表达式块内联 标量用户定义函数(UDF)内联
标量表达式、局部变量,以及 IF/ELSE Supported Supported
WHILE 包含 BREAK 和 CONTINUE Supported 不支持
多个 RETURN 语句 Supported 不支持
NEWID() Supported 不支持
时间依赖函数 (例如, GETDATE() 和 SYSDATETIME()) Supported 不支持
表格、视图或内联TVF访问 不支持 Supported
调用标量 UDF* 不支持 Supported

* 用户自定义函数可以嵌套:一个 UDF 可以调用另一个 UDF。 当被调用函数开始时,嵌套级别会增加;函数结束时则降低。 当 UDF 正体引用表格、视图或内联 TVF 时,Microsoft Fabric Data Warehouse支持最多四个层级的嵌套,否则最多支持 32 层。 超过嵌套限制会导致调用函数链失败。

内置函数

对内置功能(包括 系统功能)的支持在两种内联技术之间有所不同。

例如,创建一个 UDF @@ROWCOUNTINLINE = AUTO 并无返回错误 19841。 错误消息包含以下内容:

Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.

有关具体函数定义,请参见 “检查标量 UDF 是否可以内联”。

支持的调用查询

下表比较了对可串联标量 UDF 的调用查询支持情况。

查询功能 表达式块内联 标量用户定义函数(UDF)内联
UDF 调用列表中的 SELECTWHERE,或 JOIN ... ON Supported Supported
直接 UDF 呼叫或GROUP BYHAVING Supported 不支持
直接 UDF 呼叫 ORDER BY Supported 不支持
调用查询中的 CTE Supported 不支持
ROLLUP, CUBE, 或 GROUPING SETS 不支持 不支持

如果任何 UDF 调用都依赖标量 UDF 内联,整个查询必须满足该技术的调用查询要求。

在 Scalar UDF 内联时,将 UDF 结果投影到SELECT列表中,并按列别名或序数排序,而不是直接调用 UDF 。ORDER BY 有关查询重写示例,请参见 “为标量 UDF 内联适配调用查询”。 有关其他执行限制,请参见 “限制”。

检查是否可以内联标量 UDF

sys.sql_modules目录视图根据函数定义描述资格。 is_inlineable 是 1 用于内线定义,否则 0 。 inline_eligibility_mask 确定符合条件的技术:

inline_eligibility_mask 定义资格
0 两种方法都不是
1 标量用户定义函数(UDF)内联
2 表达式块内联
3 无论哪种方法

这些值描述的是函数的定义,而不是特定查询的执行计划。

使用以下示例查询检查标量 UDF 是否为可内联:

SELECT
    SCHEMA_NAME(b.schema_id) AS function_schema_name,
    b.name AS function_name,
    b.type_desc AS function_type,
    a.is_inlineable,
    a.inline_eligibility_mask
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('FN');

限制

  • 在不支持的查询上下文中调用标量 UDF 可能会返回错误 Scalar UDF execution is currently unavailable in this context.
  • 依赖标量 UDF 内联的查询如果包含超过 10 次 UDF 调用,可能会失败。

例子

答: 创建内联表值函数

以下示例创建了一个内联表值函数,通过参数过滤 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',
            sm.is_inlineable AS 'Inlineable'
        FROM sys.sql_modules AS sm
        INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
        WHERE o.type LIKE '%' + @objectType + '%'
        );
GO

调用返回所有内联表值函数的函数(IF):

SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION

或者,查找所有标量函数(FN):

SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION

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

这个简单的例子使用了之前创建的内联TVF,演示了如何通过使用 CROSS APPLY。 在这里,你从两sys.objects列中选择所有列ModulesByType,并对该列中匹配的所有行选择结果type。 关于如何使用 APPLY的更多信息,请参见 FROM 子句加 JOIN, APPLY, PIVOT (Transact-SQL)

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

C. 创建标量 UDF 函数

以下示例创建一个可嵌入标量 UDF,用于屏蔽输入文本。

CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Result VARCHAR(50);
        DECLARE @CleanedInput VARCHAR(50);

        -- Trim whitespace
        SET @CleanedInput = LTRIM(RTRIM(@InputString));

        -- Handle empty or null input
        IF @CleanedInput = '' OR @CleanedInput IS NULL
        BEGIN
            SET @Result = '';
        END
        ELSE IF LEN(@CleanedInput) <= 2
        BEGIN
            -- If string length is 1 or 2, just return the cleaned string
            SET @Result = @CleanedInput;
        END
        ELSE
        BEGIN
            -- Construct the masked string
            SET @Result = 
                LEFT(@CleanedInput, 1) +
                REPLICATE('*', LEN(@CleanedInput) - 2) +
                RIGHT(@CleanedInput, 1);
        END

        RETURN @Result
    END

可以调用如下所示的函数:

DECLARE @input varchar(100) = '123456789';

SELECT dbo.cleanInput (@input) AS function_output;

有关如何在 Fabric 数据仓库中使用标量 UDF 的更多示例:

SELECT在语句中:

SELECT TOP 10 
t.id, t.name, 
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;

WHERE在子句中:

 SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

JOIN在子句中:

SELECT t1.id, t1.name, 
     dbo.cleanInput (t1.name) AS function_output, 
     dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
    INNER JOIN dbo.MyTable2 AS t2 
        ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);

ORDER BY在子句中:

SELECT  t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;

在 Warehouse 中,你还可以调用数据操作语句中的标量 UDF,例如 INSERT、 UPDATE、 DELETE。 这些数据修改示例不适用于 SQL 分析端点。

SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output 
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;

UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;

DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

D. 创建一个外部函数

以下示例在模式中创建一个外部geohashdbo函数,该函数引用geohash函数集中的 Fabric 用户数据函数spatial_functions。 由于参数定义和返回类型是从引用的 Fabric 用户数据函数推断而来的,因此无需在语句中CREATE FUNCTION明确指定。

CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;

一旦创建,外部函数可以像其他用户自定义或内置函数一样用于 T-SQL 查询。 只要支持函数调用,都可以引用它,从而实现 Fabric 用户数据函数无缝集成到 T-SQL 代码中。

E. 为独立执行创建一个标量 UDF

以下表格的创建和数据加载设置需要 Warehouse。 在 SQL 分析端点上,使用带有所需列和数据的现有源支持表。

CREATE TABLE dbo.Customer
(
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    CustomerType VARCHAR(20) NOT NULL,
    MemberSince DATE NOT NULL,
    YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO

INSERT INTO dbo.Customer
(
    CustomerID,
    CustomerName,
    CustomerType,
    MemberSince,
    YearToDateRevenue
)
VALUES
    (1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
    (2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
    (3, 'Customer 3', 'VIP',     '2024-03-10', 36000.00);
GO

下面的函数将表访问 GETUTCDATE()与 结合在一起。 表访问阻止表达式块内联,并 GETUTCDATE() 阻止标量 UDF 内联。

如果没有 WITH INLINE = AUTO,该定义在创建或修改时被拒绝。 错误报告该函数无法内联,并给出两种技术的拒绝理由。 指定 WITH INLINE = AUTO 以允许创建:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO

函数被创建但保持非线性: is_inlineable 和 inline_eligibility_mask 都是 0。 独立调用成功:

DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';

不支持在对用户表的查询中调用该函数:

SELECT
    CustomerID,
    dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;

此查询返回 Scalar UDF execution is currently unavailable in this context.

小窍门

要在对用户表进行查询中使用此逻辑,请在调用者中评估当前时间值并将其作为参数传递。 关于重写函数和调用查询,请参见 向数据访问 UDF 传递时间相关值。

适用于:Azure Synapse Analytics

在 Azure Synapse Analytics 中创建用户定义函数(UDF)。 用户定义函数是接受参数、执行操作(例如复杂计算)并将操作结果以值的形式返回的 Transact-SQL 例程。 用户定义表值函数 (TVF) 返回 table 数据类型。

小窍门

关于Fabric Data Warehouse语法,请参见 for CREATE FUNCTION for Fabric Data Warehouse 的版本。

  • 在 Azure Synapse Analytics 中, CREATE FUNCTION 可以使用内联表值函数(预览)的语法返回表,也可以通过使用标量函数的语法返回单个值。

  • 在 Azure Synapse Analytics 中的无服务器 SQL 池中,可以创建内联表值函数, CREATE FUNCTION 但不能创建标量函数。

    使用这句话创建一个可重复使用的例程,你可以以下方式使用:

  • 在 Transact-SQL 语句中,例如 SELECT

  • 在调用函数

  • 在另一个用户定义函数的定义中

  • 用于为列定义 CHECK 约束

  • 用于替换存储过程

  • 使用内联函数作为安全策略的筛选器谓词

Transact-SQL 语法约定

语法

标量函数语法

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics)
-- Not available in the serverless SQL pools in Azure Synapse Analytics

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
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

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

一个函数最多可包含2100个参数。 当用户或应用调用函数时,除非你为该参数定义了默认值,否则必须为每个声明的参数提供值。

通过将 at 符号 (@) 用作第一个字符来指定参数名称。 参数名称必须遵循标识符的规则。 参数是函数的局部变量;你可以在其他函数中使用相同的参数名称。 参数只能替代常数;它们不能替代表名、列名或其他数据库对象的名称。

注意

ANSI_WARNINGS 当您在存储过程、用户自定义函数中传递参数,或在批处理语句中声明和设置变量时,不会被执行。 例如,如果你定义一个变量为char(3),然后将其设置为大于三个字符的值,数据会被截断到定义大小,或者INSERTUPDATE语句成功。

parameter_data_type

参数数据类型。 对于 Transact-SQL 函数,允许在 Azure Synapse Analytics 中支持所有标量数据类型。 时间戳(行版本)数据类型不支持。

[ = 默认 ]

参数的默认值。 如果你定义了一个 默认 值,就可以在不指定该参数值的情况下执行函数。

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

return_data_type

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

function_body

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

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

scalar_expression

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

select_stmt

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

TABLE

指定表值函数 (TVF) 的返回值为表。 你只能把常量和@local_variables 传递给TVF。

在内联TVF(预览)中,你通过一个TABLE语句定义SELECT返回值。 内联函数没有关联的 return 变量。

<function_option>

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

SCHEMABINDING

指定将函数绑定到其引用的数据库对象。 当你指定 SCHEMABINDING时,你无法修改底层对象(例如视图或表),从而影响函数定义。 你必须先修改或放弃函数定义,以消除对你想修改对象的依赖。

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

  • 你去掉这个函数。

  • 你 ALTER 输入函数语句并移除选项 SCHEMABINDING 。

只有当满足以下条件时,函数才能进行模式绑定:

  • 该函数引用的任何用户自定义函数也是模式绑定的。

  • 函数引用使用单部分或两部分名称。

  • 在UDF正体中,你只能引用同一数据库中的内置函数和其他UDF。

  • 执行该 CREATE FUNCTION 语句的用户对该函数引用的数据库对象拥有REFERENCES权限。

若要删除 SCHEMABINDING,请使用 ALTER。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

指定 OnNULLCall 标量值函数的属性。 如果你不指定这个属性,默认 CALLED ON NULL INPUT 是隐含的,即使 作为参数传递,函数 body也会 NULL 执行。

最佳做法

如果你没有创建带有 SCHEMABINDING 子句的用户自定义函数,底层对象的更改会影响函数的定义,并在调用时引发意想不到的结果。 创建函数时指定子句。WITH SCHEMABINDING 这个子句确保你不能修改函数定义中引用的对象,除非你也修改了函数。

互操作性

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

  • 赋值语句。

  • Control-of-Flow 语句,除了 TRY...CATCH声明。

  • DECLARE 语句定义本地数据变量。

在内联表值函数(预览)中,你只能使用一个选择语句。

限制

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

你可以嵌套用户自定义函数。 一个用户自定义函数可以调用另一个。 当被调用函数开始执行时,嵌套级别递增;当被调用函数完成执行时递减。 如果超过最大嵌套级别,整个调用函数链就会失败。

你不能在 Azure Synapse Analytics 的master无服务器 SQL 池数据库中创建对象,包括函数。

元数据

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

  • 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 权限。

例子

答: 使用标量值用户定义函数更改数据类型

这个简单的函数以 整数 数据类型为输入,输出为十 进制(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池中没有。

B. 创建内联表值函数

以下示例创建了一个内联表值函数,通过参数过滤 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 池中处于预览阶段。

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

这个简单的例子使用了之前创建的内联TVF,演示了如何通过使用 CROSS APPLY。 在这个例子中,你从两列sys.objects中选择所有列,并对该列上匹配ModulesByType的所有行选择结果type。 关于如何使用 APPLY的更多信息,请参见 FROM 子句加 JOIN, APPLY, PIVOT (Transact-SQL)

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

注意

内联表值函数可在无服务器的 SQL 池中使用,但在专用 SQL 池中处于预览阶段。

下一步