CREATE FUNCTION (Transact-SQL)
适用于:SQL Server
Azure SQL 数据库 Azure SQL 托管实例
创建用户定义的函数 (UDF),该函数是 Transact-SQL 或公共语言运行时 (CLR) 例程。 用户定义的函数接受参数,执行复杂计算等操作,并将该操作的结果作为值返回。 返回值可以是标量(单个)值或表。 使用此语句可以创建可通过以下方式使用的重复使用的例程:
- 在 Transact-SQL 语句中,例如
SELECT
- 在调用函数的应用程序中
- 在另一个用户定义函数的定义中
- 用于参数化视图或改进索引视图的功能
- 用于在表中定义列
- 定义
CHECK
列的约束 - 用于替换存储过程
- 使用内联函数作为安全策略的筛选器谓词
本文介绍了将 .NET Framework CLR 集成到 SQL Server。 CLR 集成不适用于Azure SQL 数据库。
对于 Azure Synapse Analytics 或 Microsoft Fabric,请参阅 CREATE FUNCTION(Azure Synapse Analytics 和 Microsoft Fabric)。
语法
Transact-SQL 标量函数的语法。
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ , ...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Transact-SQL 内联表值函数的语法。
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ , ...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Transact-SQL 多语句表值函数的语法。
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ , ...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
Transact-SQL 函数子句的语法。
<function_option> ::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
| [ INLINE = { ON | OFF } ]
}
<table_type_definition> ::=
( { <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
{ column_name data_type }
[ [ DEFAULT constant_expression ]
[ COLLATE collation_name ] | [ ROWGUIDCOL ]
]
| [ IDENTITY [ (seed , increment ) ] ]
[ <column_constraint> [ ...n ] ]
}
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
[ ON { filegroup | "default" } ] ]
| [ CHECK ( logical_expression ) ] [ , ...n ]
}
<computed_column_definition> ::=
column_name AS computed_column_expression
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ , ...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ , ...n ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
}
CLR 标量函数的语法。
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] }
[ , ...n ]
)
RETURNS { return_data_type }
[ WITH <clr_function_option> [ , ...n ] ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
CLR 表值函数的语法。
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] }
[ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
[ WITH <clr_function_option> [ , ...n ] ]
[ ORDER ( <order_clause> ) ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
CLR 函数子句的语法。
<order_clause> ::=
{
<column_name_in_clr_table_type_definition>
[ ASC | DESC ]
} [ , ...n ]
<method_specifier> ::=
assembly_name.class_name.method_name
<clr_function_option> ::=
{
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )
本机编译标量用户定义的函数的内存中 OLTP 语法。
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ NULL | NOT NULL ] [ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS return_data_type
WITH <function_option> [ , ...n ]
[ AS ]
BEGIN ATOMIC WITH (set_option [ , ... n ] )
function_body
RETURN scalar_expression
END
<function_option> ::=
{
| NATIVE_COMPILATION
| SCHEMABINDING
| [ EXECUTE_AS_Clause ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}
注意
若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法,请参阅早期版本文档。
参数
OR ALTER
适用于:SQL Server 2016 (13.x) SP 1 及更高版本,以及Azure SQL 数据库。
只有在函数已存在时才对其进行有条件地更改。
可选 OR ALTER
语法适用于 CLR,从 SQL Server 2016 (13.x) SP 1 CU 1 开始。
schema_name
用户定义函数所属的架构的名称。
function_name
用户定义的函数的名称。 函数名称必须符合标识符规则,并且在数据库中以及对其架构来说是唯一的。
即使未指定参数,函数名称后也需要括号。
@parameter_name
用户定义的函数中的参数。 可声明一个或多个参数。
一个函数最多可以有 2,100 个参数。 执行函数时,如果未定义参数的默认值,则用户必须提供每个已声明参数的值。
通过将 at 符号 (@) 用作第一个字符来指定参数名称。 参数名称必须符合标识符规则。 参数是对应于函数的局部参数;其他函数中可使用相同的参数名称。 参数只能取代常量;不能使用它们,而不是表名、列名或其他数据库对象的名称。
ANSI_WARNINGS
在存储过程、用户定义的函数中传递参数或在批处理语句中声明和设置变量时,不遵循此条件。 例如,如果将一个变量定义为 char(3),然后将其值设置为大于三个字符,则数据会被截断为定义的大小,并且 INSERT
或 UPDATE
语句可以成功执行。
[ type_schema_name. ] parameter_data_type
参数数据类型,以及它所属的架构(可选)。 对于 Transact-SQL 函数,允许使用除 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型和用户定义表类型)。 对于 CLR 函数,允许除文本、ntext、image、用户定义的表类型和时间戳数据类型之外的所有数据类型(包括 CLR 用户定义类型)。 不能在 Transact-SQL 或 CLR 函数中将非可标类型 、游标 和 表指定为参数数据类型。
如果未指定type_schema_name,则数据库引擎按以下顺序查找scalar_parameter_data_type:
- 包含 SQL Server 系统数据类型名称的架构。
- 当前数据库中当前用户的默认架构。
- 当前数据库中的 dbo 架构。
[ = default ]
参数的默认值。 如果定义了 default 值,则无需指定此参数的值即可执行函数。
可以为 CLR 函数指定默认参数值,但 varchar(max) 和 varbinary(max) 数据类型除外。
当函数的参数具有默认值时,必须在调用函数以检索默认值时指定关键字 DEFAULT
。 此行为与在存储过程中使用具有默认值的参数不同,在后一种情况下,不提供参数同样意味着使用默认值。 但是, DEFAULT
在使用 EXECUTE
语句调用标量函数时,不需要关键字。
READONLY
指示不能在函数的定义中更新或修改参数。 READONLY
是用户定义的表类型参数(TVP)所必需的,不能用于任何其他参数类型。
return_data_type
标量用户定义函数的返回值。 对于 Transact-SQL 函数,可以使用除 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型)。 对于 CLR 函数,允许使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型) 。 不能在 Transact-SQL 或 CLR 函数中将非可标类型 、游标 和 表指定为返回数据类型。
function_body
指定一系列 Transact-SQL 语句(一起不产生副作用,如修改表)定义函数的值。 function_body 仅用于标量函数和多语句表值函数 (MSTVF)。
在标量函数中,function_body 是一系列 Transact-SQL 语句,这些语句一起使用可计算出标量值。
在 MSTVF 中, function_body 是一系列 Transact-SQL 语句,用于填充 TABLE
返回变量。
scalar_expression
指定标量函数返回的标量值。
TABLE
指定表值函数 (TVF) 的返回值为表。 只有常量和 @local_variables 可以传递到 TVF。
在内联 TVF 中,返回 TABLE
值通过单个 SELECT
语句定义。 内联函数没有关联的返回变量。
在 MSTVF 中, @return_variable 是一个 TABLE
变量,用于存储和累积应作为函数值返回的行。 @只能将 return_variable 指定用于 Transact-SQL 函数,而不能用于 CLR 函数。
select_stmt
定义内联表值函数(TVF)的返回值的单个 SELECT
语句。
ORDER (<order_clause>)
指定从表值函数中返回结果的顺序。 有关详细信息,请参阅本文后面的“在 CLR 表值函数中使用排序顺序”部分。
EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name
适用于:SQL Server 2008 (10.0.x) SP 1 及更高版本。
指定创建的函数名称应引用的程序集和方法。
assembly_name - 必须与
SELECT * FROM sys.assemblies;
的name
列中的值匹配。语句上使用
CREATE ASSEMBLY
的名称。class_name - 必须与
SELECT * FROM sys.assembly_modules;
的assembly_name
列中的值匹配。此值通常包含嵌入的句点或圆点。 在这种情况下,Transact-SQL 语法要求该值与一对方括号(
[]
)或一对双引号(""
)绑定。method_name - 必须与
SELECT * FROM sys.assembly_modules;
的method_name
列中的值匹配。该方法必须是静态方法。
在典型的示例中 MyFood.dll
,所有类型都在命名空间中 MyFood
, EXTERNAL NAME
该值可以是 MyFood.[MyFood.MyClass].MyStaticMethod
。
默认情况下,SQL Server 不能执行 CLR 代码。 可以创建、修改和删除引用公共语言运行时模块的数据库对象。 但是,在启用 clr 启用选项之前,无法在 SQL Server 中执行这些引用。 若要启用此选项,请使用 sp_configure。 此选项在包含的数据库中不可用。
<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ]
定义 Transact-SQL 函数的表数据类型。 表声明包含列定义和列约束(或表约束)。 表始终放在主文件组中。
<> clr_table_type_definition ( { column_namedata_type } [ , ...n ]
适用于:SQL Server 2008 (10.0.x) SP 1 及更高版本,以及Azure SQL 数据库(某些区域中的预览版)。
定义 CLR 函数的表数据类型。 表声明仅包含列名称和数据类型。 表始终放在主文件组中。
NULL | NOT NULL
仅本机编译的标量用户定义函数支持该参数。 有关详细信息,请参阅内存中 OLTP 的标量用户定义函数。
NATIVE_COMPILATION
指示用户定义函数是否已本机编译。 对于本机编译的标量用户定义函数,此参数是必需的。
BEGIN ATOMIC WITH
本机编译的标量用户定义的函数必需且仅受支持。 有关详细信息,请参阅 本机过程中的原子块。
SCHEMABINDING
本机编译的标量用户定义函数需要该 SCHEMABINDING
参数。
EXECUTE AS
EXECUTE AS
本机编译的标量用户定义函数是必需的。
<> function_option ::= 和<clr_function_option> ::=
指定函数具有以下一个或多个选项。
ENCRYPTION
适用于:SQL Server 2008 (10.0.x) SP 1 及更高版本。
指示数据库引擎将语句的原始文本CREATE FUNCTION
转换为模糊格式。 混淆的输出在任何目录视图中都不直接可见。 无权访问系统表或数据库文件的用户无法检索混淆的文本。 但是,通过数据库管理员的诊断连接访问系统表或直接访问数据库文件的特权用户可以使用文本。 此外,能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。 有关如何访问系统元数据的详细信息,请参阅元数据可见性配置。
使用此选项可防止将函数作为 SQL Server 复制的一部分发布。 不能为 CLR 函数指定此选项。
SCHEMABINDING
指定将函数绑定到其引用的数据库对象。 指定后 SCHEMABINDING
,不能以影响函数定义的方式修改基对象。 必须首先修改或删除函数定义本身,才能删除将要修改的对象的依赖关系。
只有发生下列操作之一时,才会删除函数与其引用对象的绑定:
- 删除函数。
- 在未指定
SCHEMABINDING
选项的情况下,使用ALTER
语句修改函数。
只有满足以下条件时,函数才能绑定到架构:
- 函数是一个 Transact-SQL 函数。
- 该函数引用的用户定义函数和视图也绑定到架构。
- 该函数引用的对象是用由两部分组成的名称引用的。
- 该函数及其引用的对象属于同一数据库。
- 执行
CREATE FUNCTION
语句的用户对该函数引用的数据库对象具有REFERENCES
权限。
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
指定 OnNULLCall
标量函数的属性。 如果未指定, CALLED ON NULL INPUT
则默认隐含。 换句话说,即使 NULL
作为参数传递,函数正文也会执行。
如果在 RETURNS NULL ON NULL INPUT
CLR 函数中指定,则指示 SQL Server 可以在接收NULL
的任何参数时返回NULL
,而无需实际调用函数的主体。 如果指定的 <method_specifier>
CLR 函数的方法已具有指示 RETURNS NULL ON NULL INPUT
的自定义属性,但 CREATE FUNCTION
该语句指示 CALLED ON NULL INPUT
,该 CREATE FUNCTION
语句优先。 OnNULLCall
不能为 CLR 表值函数指定属性。
EXECUTE AS
指定用于执行用户定义函数的安全上下文。 所以,你可以控制 SQL Server 使用哪一用户帐户来验证针对该函数引用的任何数据库对象的权限。
EXECUTE AS
不能为内联表值函数指定。
有关详细信息,请参阅 EXECUTE AS 子句 (Transact-SQL)。
INLINE = { ON | OFF }
适用于:SQL Server 2019(15.x)及更高版本,以及Azure SQL 数据库。
指定是否应内联此标量 UDF。 此子句仅适用于标量用户定义函数。 INLINE
子句不是强制性的。 如果未指定子 INLINE
句,则会自动将其 ON
设置为或 OFF
基于 UDF 是否可内联。 如果 INLINE = ON
指定了 UDF,但发现 UDF 不可内联,则会引发错误。 有关详细信息,请参阅标量 UDF 内联。
<> column_definition ::=
定义表数据类型。 表声明包含列定义和约束。 对于 CLR 函数,只能指定 column_name 和 data_type 。
column_name
表中列的名称。 列名称必须遵循标识符的规则,且在表中必须唯一。 column_name 可以包含 1 到 128 个字符。
data_type
指定列数据类型。 对于 Transact-SQL 函数,可以使用除 timestamp 之外的所有数据类型(包括 CLR 用户定义类型)。 对于 CLR 函数,允许除文本、ntext、image、char、varchar、varchar(max)和时间戳之外的所有数据类型(包括 CLR 用户定义类型)。不能在 Transact-SQL 或 CLR 函数中将非可标类型游标指定为列数据类型。
DEFAULT constant_expression
如果在插入过程中未显式提供值,则指定为列提供的值。 constant_expression是常量或NULL
系统函数值。 DEFAULT
定义可以应用于除具有 IDENTITY
属性的列以外的任何列。 DEFAULT
不能为 CLR 表值函数指定。
COLLATE collation_name
指定列的排序规则。 如果未指定,则为该列分配数据库的默认排序规则。 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。 如需获取排序规则列表和详细信息,请参阅 Windows 排序规则名称 (Transact-SQL) 和 SQL Server 排序规则名称 (Transact-SQL)。
子COLLATE
句可用于仅更改 char、varchar、nchar 和 nvarchar 数据类型的列的排序规则。 COLLATE
不能为 CLR 表值函数指定。
ROWGUIDCOL
指示新列是行的全局唯一标识符列。 每个表只能指定一个 uniqueidentifier 列作为 ROWGUIDCOL
列。 该 ROWGUIDCOL
属性只能分配给 uniqueidentifier 列。
该 ROWGUIDCOL
属性不强制实施存储在列中的值的唯一性。 它也不会为插入到表中的新行自动生成值。 若要为每个列生成唯一值,请使用对INSERT
语句的NEWID
函数。 可以指定默认值;但是, NEWID
不能指定为默认值。
IDENTITY
指示新列是标识列。 在为表添加新行时,SQL Server 将为该列提供唯一的增量值。 标识列通常与约束一 PRIMARY KEY
起使用,用作表的唯一行标识符。 可以将 IDENTITY
属性分配给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。 每个表只能创建一个标识列。 绑定的默认值和 DEFAULT
约束不能与标识列一起使用。 必须同时指定 seed 和 increment,或者都不指定 。 如果二者都未指定,则取默认值 (1,1)。
IDENTITY
不能为 CLR 表值函数指定。
seed
要分配给表中第一行的整数值。
increment
要添加到 表中连续行的种子 值的整数值。
<> column_constraint ::= 和<table_constraint> ::=
为指定列或表定义约束。 对于 CLR 函数,唯一允许的约束类型是 NULL
。 不允许命名约束。
NULL | NOT NULL
确定列中是否允许空值。 NULL
不是严格限制,但可以像指定一样 NOT NULL
。 NOT NULL
不能为 CLR 表值函数指定。
PRIMARY KEY
通过唯一索引强制指定列的实体完整性的约束。 在表值用户定义函数中, PRIMARY KEY
每个表只能对一列创建约束。 PRIMARY KEY
不能为 CLR 表值函数指定。
UNIQUE
一个约束,该约束通过唯一索引为一个或多个指定列提供实体完整性。 表可以有多个 UNIQUE
约束。 UNIQUE
不能为 CLR 表值函数指定。
CLUSTERED | NONCLUSTERED
指示为 PRIMARY KEY
或 UNIQUE
约束创建聚集索引或非聚集索引。 PRIMARY KEY
约束用法 CLUSTERED
和 UNIQUE
约束使用 NONCLUSTERED
。
CLUSTERED
只能为一个约束指定。 如果 CLUSTERED
为 UNIQUE
约束指定,并且 PRIMARY KEY
还指定了约束,则 PRIMARY KEY
使用 NONCLUSTERED
。
CLUSTERED
不能 NONCLUSTERED
为 CLR 表值函数指定。
CHECK
一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。 CHECK
不能为 CLR 表值函数指定约束。
logical_expression
TRUE
返回或 FALSE
.
<> computed_column_definition ::=
指定计算列。 有关计算列的详细信息,请参阅 CREATE TABLE (Transact-SQL)。
column_name
计算列的名称。
computed_column_expression
定义计算列的值的表达式。
<index_option> ::=
指定或UNIQUE
索引的PRIMARY KEY
索引选项。 有关索引选项的详细信息,请参阅 CREATE INDEX (Transact-SQL)。
PAD_INDEX = { ON | OFF }
指定索引填充。 默认为 OFF
。
FILLFACTOR = fillfactor
指定一个百分比,指示在数据库引擎创建或更改索引的过程中,应将每个索引页面的叶级填充到什么程度。 fillfactor 必须是 1 到 100 之间的整数。 默认值为 0。
IGNORE_DUP_KEY = { ON | OFF }
指定在插入操作尝试向唯一索引插入重复键值时的错误响应。 IGNORE_DUP_KEY
选项仅适用于创建或重新生成索引后发生的插入操作。 默认为 OFF
。
STATISTICS_NORECOMPUTE = { ON | OFF}
指定是否重新计算分布统计信息。 默认为 OFF
。
ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允许行锁。 默认为 ON
。
ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允许使用页锁。 默认为 ON
。
最佳做法
如果未使用 SCHEMABINDING
子句创建用户定义的函数,则对基础对象所做的更改可能会影响函数的定义,并在调用函数时产生意外结果。 我们建议实现以下方法之一,以便确保函数不会由于对于其基础对象的更改而过期:
创建函数时指定
WITH SCHEMABINDING
子句。 此选项可确保无法修改函数定义中引用的对象,除非也修改了该函数。在修改在函数定义中指定的任何对象后执行 sp_refreshsqlmodule 存储过程。
有关内联表值函数(内联 TVF)和多语句表值函数(MSTVF)的详细信息和性能注意事项,请参阅创建用户定义的函数(数据库引擎)。
数据类型
如果在 CLR 函数中指定了参数,则这些参数应为 SQL Server 类型,即以前为 scalar_parameter_data_type 定义的类型。 有关将 SQL Server 系统数据类型与 CLR 集成数据类型或 .NET Framework 公共语言运行时数据类型进行比较的详细信息,请参阅 映射 CLR 参数数据。
要使 SQL Server 在类中重载时引用正确的方法,所 <method_specifier>
指示的方法必须具有以下特征:
- 接收与在中指定的
[ , ...n ]
参数数相同的数目。 - 通过值而不是引用来接收所有参数。
- 使用与 SQL Server 函数中指定的类型兼容的参数类型。
如果 CLR 函数的返回数据类型指定表类型(RETURNS TABLE
),则该方法 <method_specifier>
的返回数据类型应为类型 IEnumerator
, IEnumerable
并且它假定接口由函数的创建者实现。 与 Transact-SQL 函数不同,CLR 函数不能包含PRIMARY KEY
或UNIQUE
CHECK
约束。<table_type_definition>
<table_type_definition>
中指定的列数据类型必须与 <method_specifier>
中的方法在执行时返回的结果集中的对应列的类型相匹配。 创建函数时不会执行此类型检查。
有关如何对 CLR 函数编程的详细信息,请参阅 CLR 用户定义函数。
注解
可以在使用标量表达式的位置调用标量函数,其中包括计算列和 CHECK
约束定义。 还可以使用 EXECUTE (Transact-SQL) 语句执行标量函数。 必须使用至少由两部分组成名称的函数 (<schema>.<function>
) 来调用标量函数。 有关多部分名称的详细信息,请参阅 Transact-SQL 语法约定 (Transact-SQL)。 在允许表表达式的情况下,可在 SELECT
、INSERT
、UPDATE
或 DELETE
语句的 FROM
子句中调用表值函数。 有关详细信息,请参阅 “执行用户定义函数”。
互操作性
下列语句在函数内有效:
- 赋值语句。
TRY...CATCH
语句以外的控制流语句。- 定义局部数据变量和局部游标的
DECLARE
语句。 SELECT
语句,其中的选择列表包含为局部变量分配值的表达式。- 游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。 仅
FETCH
允许使用INTO
子句向局部变量赋值的语句;FETCH
不允许将数据返回给客户端的语句。 - 修改本地表变量的
INSERT
、UPDATE
和DELETE
语句。 - 调用扩展存储过程的
EXECUTE
语句。
有关详细信息,请参阅创建用户定义的函数(数据库引擎)。
计算列互操作性
函数具有下列属性。 这些属性的值确定了函数是否可用于持久化计算列或索引计算列。
properties | 说明 | 备注 |
---|---|---|
IsDeterministic |
函数是确定性函数还是不确定性函数。 | 确定性函数中允许本地数据访问。 例如,每当使用一组特定的输入值调用时,始终返回相同结果的函数,并且数据库具有相同状态的函数将被标记为确定性。 |
IsPrecise |
函数是精确函数还是不精确函数。 | 不精确函数包含浮点运算之类的运算。 |
IsSystemVerified |
SQL Server 可验证函数的精度和确定性属性。 | |
SystemDataAccess |
函数可以访问 SQL Server 的本地实例中的系统数据(系统目录或虚拟系统表)。 | |
UserDataAccess |
函数可以访问 SQL Server 的本地实例中的用户数据。 | 包含用户定义表和临时表,但不包含表变量。 |
Transact-SQL 函数的精度和确定性属性由 SQL Server 自动确定。 CLR 函数的数据访问权限和确定性属性可由用户指定。 有关详细信息,请参阅 CLR 集成:CLR 例程的自定义属性。
若要显示这些属性的当前值,请使用 OBJECTPROPERTYEX (Transact-SQL)。
重要
必须使用确定性的 SCHEMABINDING
创建函数。
如果用户定义函数具有下列属性值,则可以在索引中使用调用用户定义函数的计算列:
IsDeterministic
是true
IsSystemVerified
是true
(除非计算列持久化)UserDataAccess
为false
SystemDataAccess
为false
有关详细信息,请参阅 计算列上的索引。
从函数中调用扩展存储过程
从函数内部调用扩展存储过程时,无法将结果集返回给客户端。 将结果集返回给客户端的任何 ODS API,返回 FAIL
。 扩展存储过程可以重新连接到 SQL Server 实例;但是,它不应尝试联接与调用扩展存储过程的函数相同的事务。
与批处理或存储过程的调用类似,扩展存储过程在运行 SQL Server 的 Windows 安全帐户的上下文中执行。 为用户授予 EXECUTE
对存储过程的权限时,存储过程的所有者应考虑此方案。
限制
用户定义函数不能用于执行修改数据库状态的操作。
用户定义函数不能包含将表作为其目标的 OUTPUT INTO
子句。
无法将以下 Service Broker 语句包含在 Transact-SQL 用户定义函数的定义中:
BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND
用户定义函数可以嵌套;也就是说,用户定义函数可相互调用。 被调用函数开始执行时,嵌套级别将增加;被调用函数执行结束后,嵌套级别将减少。 用户定义函数的嵌套级别最多可达 32 级。 如果超出最大嵌套级别数,整个调用函数链将失败。 从 Transact-SQL 用户定义函数对托管代码的任何引用都将根据 32 级嵌套限制计入一个级别。 从托管代码内部调用的方法不计入此限制。
在 CLR 表值函数中使用排序顺序
在 CLR 表值函数中使用 ORDER
子句时请遵循以下准则:
必须确保始终按指定的顺序对结果进行排序。 如果结果不是按指定顺序排列的,则执行查询时,SQL Server 将生成错误消息。
如果指定了
ORDER
子句,则必须根据列(显式或隐式)的排序规则对表值函数的输出进行排序。 例如,如果列排序规则为中文,则返回的结果必须按照中文排序规则进行排序。 (在 DDL 中为表值函数指定排序规则,或从数据库排序规则中获取排序规则)。SQL Server 始终在返回结果时验证
ORDER
子句(无论查询处理器是否使用它来执行进一步优化)。 仅当知道查询处理器有用时,才使用ORDER
子句。在以下情况下,SQL Server 查询处理器将自动使用
ORDER
子句:- “插入”查询,其中
ORDER
子句与索引兼容。 - 与
ORDER
子句兼容的ORDER BY
子句。 - 聚合,其中
GROUP BY
与ORDER
子句兼容。 DISTINCT
聚合,其中不同的列与ORDER
子句兼容。
- “插入”查询,其中
ORDER
除非ORDER BY
在查询中也指定了子句,否则子句不保证执行查询时的SELECT
有序结果。 有关如何查询表值函数排序顺序中所包含的列的信息,请参阅 sys.function_order_columns (Transact-SQL)。
元数据
下表列出可用于返回与用户定义函数有关的元数据的系统目录视图。
系统视图 | 说明 |
---|---|
sys.sql_modules | 请参阅“示例” 部分中的示例 E。 |
sys.assembly_modules | 显示 CLR 用户定义函数的有关信息。 |
sys.parameters | 显示用户定义函数中定义的参数的有关信息。 |
sys.sql_expression_dependencies | 显示函数所引用的基础对象。 |
权限
需要在数据库中具有 CREATE FUNCTION
权限,并对创建函数时所在的架构具有 ALTER
权限。 如果函数指定用户定义类型,则需要对该类型具有 EXECUTE
权限。
示例
有关 UDF 的更多示例和性能注意事项,请参阅创建用户定义的函数(数据库引擎)。
A. 使用计算 ISO 周的标量值用户定义函数
下面的示例将创建用户定义函数 ISOweek
。 此函数使用日期参数来计算 ISO 周数。 要使此函数能正确计算,必须在调用该函数前调用 SET DATEFIRST 1
。
该示例还演示如何使用 EXECUTE AS 子句(Transact-SQL) 子句指定可以执行存储过程的安全上下文。 在该示例中,该选项 CALLER
指定过程在调用该过程的用户的上下文中执行。 还可以指定 SELF
、OWNER
和 user_name 等其他选项。
下面是函数调用。 将 DATEFIRST
设置为 1
。
CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek INT;
SET @ISOweek = DATEPART(wk, @DATE) + 1 -
DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek = 0)
SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
+ '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm, @DATE) = 12)
AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
SET @ISOweek = 1;
RETURN (@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';
下面是结果集:
ISO Week
----------------
52
B. 创建内联表值函数
下面的示例在 AdventureWorks2022 数据库中返回内联表值函数。 对于销售给商店的每个产品,该函数返回三列,分别为 ProductID
、Name
以及各个商店年初至今总数的累计 YTD Total
。
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
若要调用该函数,请运行此查询。
SELECT * FROM Sales.ufn_SalesByStore (602);
°C 创建多语句表值函数
以下示例在 fn_FindReports(InEmpID)
数据库中创建表值函数 AdventureWorks2022
。 如果提供一个有效雇员 ID,该函数将返回一个表,该表对应于直接或间接向该雇员报告的所有雇员。 该函数使用递归公用表表达式 (CTE) 来生成雇员的层次结构列表。 有关递归 CTE 的详细信息,请参阅 WITH common_table_expression (Transact-SQL)。
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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.
AS
BEGIN
WITH EMP_cte (
EmployeeID,
OrganizationNode,
FirstName,
LastName,
JobTitle,
RecursionLevel
) -- CTE name and columns
AS (
-- Get the initial list of Employees for Manager n
SELECT e.BusinessEntityID,
OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
p.FirstName,
p.LastName,
e.JobTitle,
0
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
-- Join recursive member to anchor
SELECT e.BusinessEntityID,
e.OrganizationNode,
p.FirstName,
p.LastName,
e.JobTitle,
RecursionLevel + 1
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON 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
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM dbo.ufn_FindReports(1);
GO
D. 创建 CLR 函数
此示例会创建 CLR 函数 len_s
。 在创建该函数之前,程序集 SurrogateStringFunction.dll
已在本地数据库中注册。
适用于:SQL Server 2008 (10.0.x) SP 1 及更高版本。
DECLARE @SamplesPath NVARCHAR(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';
CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO
有关如何创建 CLR 表值函数的示例,请参阅 CLR 表值函数。
E. 显示用户定义的函数的定义
SELECT DEFINITION,
type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
AND type IN ('FN', 'IF', 'TF');
GO
使用选项创建的 ENCRYPTION
函数定义无法使用 sys.sql_modules
;但是,将显示有关加密函数的其他信息。