在SQL SERVER 2005创建用户定义函数语法语法备注参数权限示例

创建用户定义函数。这是一个已保存 Transact-SQL 或公共语言运行时 (CLR) 例程，该例程可返回一个值。用户定义函数不能用于执行修改数据库状态的操作。与系统函数一样，用户定义函数可从查询中调用。标量函数和存储过程一样，可使用 EXECUTE 语句执行。

用户定义函数为标量值函数或表值函数。如果 RETURNS 子句指定了一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。

如果 RETURNS 子句指定 TABLE，则函数为表值函数。根据函数主体的定义方式，表值函数可分为内联函数或多语句函数。有关详细信息，请参阅表值用户定义函数。

下列语句在函数内有效：

赋值语句。

TRY...CATCH 语句以外的流控制语句。

定义局部数据变量和局部游标的 DECLARE 语句。

SELECT 语句，其中的选择列表包含为局部变量分配值的表达式。

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

修改 table 局部变量的 INSERT、UPDATE 和 DELETE 语句。

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

有关详细信息，请参阅创建用户定义函数（数据库引擎）。

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

注意：

从 Transact-SQL 用户定义函数对托管代码的任何引用都将计入 32 级嵌套限制的一个级别。从托管代码内部调用的方法不根据此限制进行计数。

在 SQL Server 的早期版本中，函数只能分为确定性函数和不确定性函数两类。在 SQL Server 2005 中，函数具有下列属性。这些属性的值确定了函数是否可用于持久化计算列或索引计算列。

属性

说明

注意

IsDeterministic

函数是确定性函数还是不确定性函数。

确定性函数中允许本地数据访问。例如，如果每次使用一组特定输入值和相同数据库状态调用函数时，函数都返回相同结果，则该函数将被标记为确定性函数。

IsPrecise

函数是精确函数还是不精确函数。

不精确函数包含浮点运算之类的运算。

IsSystemVerified

SQL Server 可验证函数的精度和确定性属性。

SystemDataAccess

函数可以访问 SQL Server 的本地实例中的系统数据（系统目录或虚拟系统表）。

UserDataAccess

函数可以访问 SQL Server 的本地实例中的用户数据。

包含用户定义表和临时表，但不包含表变量。

Transact-SQL 函数的精度和确定性属性由 SQL Server 自动确定。有关详细信息，请参阅用户定义函数的设计指导原则。CLR 函数的数据访问权限和确定性属性可由用户指定。有关详细信息，请参阅 Overview of CLR Integration Custom Attributes。

如果用户定义函数具有下列属性值，则可以在索引中使用调用用户定义函数的计算列：

IsDeterministic = true

IsSystemVerified = true（计算列是持久性计算列时除外）

UserDataAccess = false

SystemDataAccess = false

有关详细信息，请参阅为计算列创建索引。

如果在函数中调用扩展存储过程，则该过程不能向客户端返回结果集。向客户端返回结果集的任何 ODS API 都将返回 FAIL。扩展存储过程可以连接回 SQL Server 的实例；不过，该过程不应尝试与调用扩展存储过程的函数同时联接到同一事务。

与通过批处理或存储过程进行调用相似，扩展存储过程在运行 SQL Server 的 Windows 安全帐户的上下文中执行。存储过程的所有者在授予用户 EXECUTE 权限时应考虑这一点。

如果在 CLR 函数中指定了参数，则这些参数应为 SQL Server 类型，即以前为 scalar_parameter_data_type 定义的类型。有关将 SQL Server 系统数据类型与 CLR 集成数据类型或 .NET Framework 公共语言运行时数据类型进行比较的信息，请参阅 SQL Server Data Types and Their .NET Framework Equivalents。

为了使 SQL Server 在类中重载时引用正确方法，<method_specifier> 中指示的方法必须具有下列特征：

接收 [ ,...n ] 中指定的参数数量。

通过值而不是引用来接收所有参数。

使用与 SQL Server 函数中指定的类型兼容的参数类型。

如果 CLR 函数的返回数据类型指定表类型 (RETURNS TABLE)，则 <method_specifier> 中方法的返回数据类型应为 IEnumerator 或 IEnumerable 类型，且假定由函数创建者来实现接口。与 Transact-SQL 函数不同，CLR 函数不能在 <table_type_definition> 中包含 PRIMARY KEY、UNIQUE 或 CHECK 约束。 <table_type_definition> 中指定的列的数据类型，必须与 <method_specifier> 中的方法在执行时返回的结果集中的对应列的类型匹配。创建函数时不执行上述类型检查。

有关对 CLR 函数编程的详细信息，请参阅 CLR User-Defined Functions。

下列 Service Broker 语句不能包含在 Transact-SQL 用户定义函数的定义中：

BEGIN DIALOG CONVERSATION

END CONVERSATION

GET CONVERSATION GROUP

MOVE CONVERSATION

RECEIVE

SEND

例如：

复制代码

不能使用 sys.sql_modules 查看使用 ENCRYPTION 选项创建的函数定义；不过，可显示有关加密函数的其他信息。

<dl></dl>

<dt>schema_name</dt>

<dd></dd>

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

<dt>function_name</dt>

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

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

<dt>@parameter_name</dt>

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

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

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

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

<dt>[ type_schema_name. ] parameter_data_type</dt>

参数的数据类型及其所属的架构，后者为可选项。对于 Transact-SQL 函数，可以使用除 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。对于 CLR 函数，可以使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。不能将非标量类型 cursor 和 table 指定为 Transact-SQL 函数或 CLR 函数中的参数数据类型。

如果未指定 type_schema_name，则 SQL Server 2005 Database Engine 将按以下顺序查找 scalar_parameter_data_type：

包含 SQL Server 系统数据类型名称的架构。

当前数据库中当前用户的默认架构。

当前数据库中的 dbo 架构。

<dt>[ = default ]</dt>

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

可以为除 varchar(max) 和 varbinary(max) 数据类型之外的 CLR 函数指定默认参数值。

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

<dt>return_data_type</dt>

标量用户定义函数的返回值。对于 Transact-SQL 函数，可以使用除 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。对于 CLR 函数，可以使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。不能将非标量类型 cursor 和 table 指定为 Transact-SQL 函数或 CLR 函数中的返回数据类型。

<dt>function_body</dt>

指定一系列定义函数值的 Transact-SQL 语句，这些语句在一起使用不会产生负面影响（例如修改表）。function_body 仅用于标量函数和多语句表值函数。

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

在多语句表值函数中，function_body 是一系列 Transact-SQL 语句，这些语句将填充 TABLE 返回变量。

<dt>scalar_expression</dt>

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

<dt>TABLE</dt>

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

在内联表值函数中，TABLE 返回值是通过单个 SELECT 语句定义的。内联函数没有关联的返回变量。

在多语句表值函数中，@return_variable 是 TABLE 变量，用于存储和汇总应作为函数值返回的行。只能将 @return_variable 指定用于 Transact-SQL 函数，而不能用于 CLR 函数。

<dt>select_stmt</dt>

定义内联表值函数的返回值的单个 SELECT 语句。

<dt>EXTERNAL NAME &lt;method_specifier&gt;, assembly_name.class_name.method_name</dt>

指定将程序集与函数绑定的方法。assembly_name 必须与 SQL Server 中当前数据库内具有可见性的现有程序集匹配。class_name 必须是有效的 SQL Server 标识符，并且必须作为类存在于程序集中。如果类具有以命名空间限定的名称，该名称使用句点 (.) 来分隔命名空间的各部分，则必须使用方括号 ([ ]) 或引号 (" ") 分隔类名称。method_name 必须是有效的 SQL Server 标识符，并且必须作为静态方法存在于指定类中。

<dt>&lt;table_type_definition&gt;, ( { &lt;column_definition&gt; &lt;column_constraint&gt; ,   | &lt;computed_column_definition&gt; } ,   [ &lt;table_constraint&gt; ] [ ,...n ] ) ,</dt>

定义 Transact-SQL 函数的表数据类型。表声明包含列定义和列约束（或表约束）。表始终放在主文件组中。

<dt>&lt; clr_table_type_definition &gt; , ( { column_name data_type } [ ,...n ] ),</dt>

定义 CLR 函数的表数据类型。表声明仅包含列名称和数据类型。表始终放在主文件组中。

&lt;function_option&gt;::= and &lt;clr_function_option&gt;::=

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

<dt>ENCRYPTION</dt>

指示数据库引擎 对包含 CREATE FUNCTION 语句文本的目录视图列进行加密。使用 ENCRYPTION 可以防止将函数作为 SQL Server 复制的一部分发布。不能为 CLR 函数指定 ENCRYPTION。

<dt>SCHEMABINDING</dt>

指定将函数绑定到其引用的数据库对象。如果其他架构绑定对象也在引用该函数，此条件将防止对其进行更改。

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

删除函数。

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

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

函数为 Transact-SQL 函数。

该函数引用的用户定义函数和视图也绑定到架构。

该函数引用的对象是用由两部分组成的名称引用的。

该函数及其引用的对象属于同一数据库。

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

不能为 CLR 函数或引用别名数据类型的函数指定 SCHEMABINDING。

<dt>RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT</dt>

指定标量值函数的 OnNULLCall 属性。如果未指定，则默认为 CALLED ON NULL INPUT。这意味着即使传递的参数为 NULL，也将执行函数体。

如果在 CLR 函数中指定了 RETURNS NULL ON NULL INPUT，它指示当 SQL Server 接收到的任何一个参数为 NULL 时，它可以返回 NULL，而无需实际调用函数体。如果 &lt;method_specifier&gt; 中指定的 CLR 函数的方法已具有指示 RETURNS NULL ON NULL INPUT 的自定义属性，但 CREATE FUNCTION 语句指示 CALLED ON NULL INPUT，则优先采用 CREATE FUNCTION 语句指示的属性。不能为 CLR 表值函数指定 OnNULLCall 属性。

<dt>EXECUTE AS 子句</dt>

指定用于执行用户定义函数的安全上下文。所以，您可以控制 SQL Server 使用哪一个用户帐户来验证针对该函数引用的任何数据库对象的权限。

不能为内联用户定义函数指定 EXECUTE AS。

&lt; column_definition &gt;::=

定义表数据类型。表声明包含列定义和约束。对于 CLR 函数，只能指定 column_name 和 data_type。

<dt>column_name</dt>

表中列的名称。列名称必须符合标识符规则，并且在表中必须是唯一的。column_name 可以由 1 至 128 个字符组成。

<dt>data_type</dt>

指定列数据类型。对于 Transact-SQL 函数，可以使用除 timestamp 之外的所有数据类型（包括 CLR 用户定义类型）。对于 CLR 函数，可以使用除 text、ntext、image、char、varchar、varchar(max) 和 timestamp 之外的所有数据类型（包括 CLR 用户定义类型）。在 Transact-SQL 或 CLR 函数中，非标量类型 cursor 不能指定为列数据类型。

<dt>DEFAULT constant_expression</dt>

指定当插入过程中没有显式提供值时为列提供的值。constant_expression 可以是常量、NULL 或系统函数值。DEFAULT 定义可以应用于除具有 IDENTITY 属性的列之外的任何列。不能为 CLR 表值函数指定 DEFAULT。

<dt>COLLATE collation_name</dt>

COLLATE 子句只能用来更改数据类型为 char、varchar、nchar 和 nvarchar 的列的排序规则。

不能为 CLR 表值函数指定 COLLATE。

<dt>ROWGUIDCOL</dt>

指示新列是行的全局唯一标识符列。对于每个表，只能将其中的一个 uniqueidentifier 列指定为 ROWGUIDCOL 列。ROWGUIDCOL 属性只能分配给 uniqueidentifier 列。

ROWGUIDCOL 属性并不强制实现列中存储的值的唯一性。该属性也不会为插入表的新行自动生成值。若要为每列生成唯一值，请在 INSERT 语句中使用 NEWID 函数。可以指定默认值；但是，不能将 NEWID 指定为默认值。

<dt>IDENTITY</dt>

指示新列是标识列。在为表添加新行时，SQL Server 将为该列提供唯一的增量值。标识列通常与 PRIMARY KEY 约束一起使用，作为表的唯一行标识符。可以将 IDENTITY 属性分配给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。每个表只能创建一个标识列。不能将绑定默认值和 DEFAULT 约束用于标识列。必须同时指定 seed 和 increment，或者二者都不指定。如果二者都未指定，则取默认值 (1,1)。

不能为 CLR 表值函数指定 IDENTITY。

<dt>seed</dt>

要分配给表中第一行的整数值。

<dt>increment</dt>

要加到表中后续行的 seed 值上的整数值。

&lt; column_constraint &gt;::= and &lt; table_constraint&gt;::=

为指定列或表定义约束。对于 CLR 函数，允许的唯一约束类型为 NULL。不允许命名约束。

<dt>NULL | NOT NULL</dt>

确定列中是否允许空值。严格讲来，NULL 不是约束，但可以像指定 NOT NULL 那样指定它。不能为 CLR 表值函数指定 NOT NULL。

<dt>PRIMARY KEY</dt>

一个约束，该约束通过唯一索引来强制指定列的实体完整性。在表值用户定义函数中，只能对每个表中的一列创建 PRIMARY KEY 约束。不能为 CLR 表值函数指定 PRIMARY KEY。

<dt>UNIQUE</dt>

一个约束，该约束通过唯一索引为一个或多个指定列提供实体完整性。一个表可以有多个 UNIQUE 约束。不能为 CLR 表值函数指定 UNIQUE。

<dt>CLUSTERED | NONCLUSTERED</dt>

指示为 PRIMARY KEY 或 UNIQUE 约束创建聚集索引还是非聚集索引。PRIMARY KEY 约束使用 CLUSTERED，而 UNIQUE 约束使用 NONCLUSTERED。

只能为一个约束指定 CLUSTERED。如果为 UNIQUE 约束指定了 CLUSTERED，并且指定了 PRIMARY KEY 约束，则 PRIMARY KEY 使用 NONCLUSTERED。

不能为 CLR 表值函数指定 CLUSTERED 和 NONCLUSTERED。

<dt>CHECK</dt>

一个约束，该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。不能为 CLR 表值函数指定 CHECK 约束。

<dt>logical_expression</dt>

返回 TRUE 或 FALSE 的逻辑表达式。

&lt;computed_column_definition&gt;::=

计算列的名称。

<dt>computed_column_expression</dt>

定义计算列的值的表达式。

&lt;index_option&gt;::=

<dt>PAD_INDEX = { ON | OFF }</dt>

指定索引填充。默认值为 OFF。

<dt>FILLFACTOR = fillfactor</dt>

指定一个百分比，指示在创建或更改索引期间，数据库引擎 对各索引页的叶级填充的程度。fillfactor 必须为介于 1 至 100 之间的整数值。默认值为 0。

<dt>IGNORE_DUP_KEY = { ON | OFF }</dt>

指定当对唯一聚集索引或唯一非聚集索引的多行插入事务中出现重复键值时的错误响应。默认值为 OFF。

<dt>STATISTICS_NORECOMPUTE = { ON | OFF }</dt>

指定是否重新计算分布统计信息。默认值为 OFF。

<dt>ALLOW_ROW_LOCKS = { ON | OFF }</dt>

指定是否允许行锁。默认值为 ON。

<dt>ALLOW_PAGE_LOCKS = { ON | OFF }</dt>

指定是否允许页锁。默认值为 ON。

需要在数据库中具有 CREATE FUNCTION 权限，并对创建函数时所在的架构具有 ALTER 权限。如果函数指定用户定义类型，则需要对该类型具有 EXECUTE 权限。

以下示例将创建用户定义函数 <code>ISOweek</code>。此函数使用日期参数来计算 ISO 周数。要使此函数能正确计算，必须在调用该函数前调用 <code>SET DATEFIRST 1</code>。

下面是函数调用。请注意，<code>DATEFIRST</code> 设置为 <code>1</code>。

下面是结果集： 

以下示例将返回内联表值函数。对于销售给商店的每个产品，该函数返回三列，分别为 <code>ProductID</code>、<code>Name</code> 以及各个商店年初至今总数的累计 <code>YTD Total </code>。

若要调用该函数，请运行此查询。

以下示例假定在本地计算机的默认位置安装了 SQL Server Database Engine samples，并且已编译了 StringManipulate.csproj 示例应用程序。有关详细信息，请参阅 能够识别补充字符的字符串操作。

该示例将创建 CLR 函数 <code>len_s</code>。在创建该函数之前，程序集 <code>SurrogateStringFunction.dll</code> 已在本地数据库中注册。

本文转自yonghu86 51CTO博客，原文链接：http://blog.51cto.com/yonghu/1321452，如需转载请自行联系原作者