SQLServer之创建标量函数

创建标量函数注意事项

在 sql server 和 azure sql database 中创建用户定义函数。 用户定义函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的 transact-sql 或公共语言运行时 (clr) 例程。 返回值可以是标量（单个）值或表。 使用此语句可以创建可通过以下方式使用的重复使用的例程：

• 在 transact-sql 语句（如 select）中

• 在调用该函数的应用程序中

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

• 用于参数化视图或改进索引视图的功能

• 用于在表中定义列

• 用于为列定义 check 约束

• 用于替换存储过程

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

可在使用标量表达式的位置调用标量值函数。 这包括计算列和 check 约束定义。 也可以使用 execute 语句执行标量值函数。必须使用至少由两部分组成名称的函数来调用标量值函数。

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

使用ssms数据库管理工具和t-sql脚本创建标量函数语法相同，本示例演示使用t-sql脚本创建标量函数。

使用t-sql脚本创建标量函数

语法：

--声明数据库引用

use 数据库名;

go

 

--判断是否存在需要创建的函数，如果存在则删除

if exists(select * from sys.objects where name=函数名称)

drop function 函数名称;

go

 

--创建用户自定义函数

create function [schema_name.] function_name

(@parameter_name  [as] [type_schema_name.] parameter_data_type [ = default ] [ readonly],..n)

returns return_data_type

[with] [encryption][,][schemabinding][,][returns null on null input | called on null input][,][execute as clause]

as

begin

function_body;

return scalar_expression;

end

go

语法解析：

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

--function_name
--用户定义函数的名称。 函数名称必须符合标识符规则，并且在数据库中以及对其架构来说是唯一的,即使未指定参数，函数名称后也需要加上括号。

--@parameter_name
--用户定义函数中的参数。 可声明一个或多个参数。
--一个函数最多可以有 2,100 个参数。 执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值。
--通过将 at 符号 (@) 用作第一个字符来指定参数名称。 参数名称必须符合标识符规则。 参数是对应于函数的局部参数；其他函数中可使用相同的参数名称。
--参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。

--[ type_schema_name. ] parameter_data_type
--参数的数据类型及其所属的架构，后者为可选项。 对于 transact-sql 函数，允许使用除 timestamp 数据类型之外的所有数据类型（包括 clr 用户定义类型和用户定义表类型）。
--对于 clr 函数，允许使用除 text、ntext、image、用户定义表类型和 timestamp 数据类型之外的所有数据类型（包括 clr 用户定义类型）。 在 transact-sql 函数或 clr 函数中，
--不能将非标量类型 cursor 和 table 指定为参数数据类型。
--如果未指定 type_schema_name， 数据库引擎会按以下顺序查找 scalar_parameter_data_type：
--包含 sql server 系统数据类型名称的架构。
--当前数据库中当前用户的默认架构。
--当前数据库中的 dbo 架构。

--[ =default ]
--参数的默认值。 如果定义了 default 值，则无需指定此参数的值即可执行函数。
--如果函数的参数有默认值，则调用该函数以检索默认值时，必须指定关键字 default。 此行为与在存储过程中使用具有默认值的参数不同，在后一种情况下，不提供参数同样意味着使用默认值。
--但在通过使用 execute 语句调用标量函数时，default 关键字不是必需的。

--readonly
--指示不能在函数定义中更新或修改参数。 如果参数类型为用户定义的表类型，则应指定 readonly。

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

--encryption
--适用范围： sql server 2008 到 sql server 2017。
--指示 数据库引擎会将 create function 语句的原始文本转换为模糊格式。 模糊代码的输出在任何目录视图中都不能直接显示。 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。
--但是，可以通过 dac 端口访问系统表的特权用户或直接访问数据库文件的特权用户可以使用此文本。 此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。
--使用此选项可防止将函数作为 sql server 复制的一部分发布。 不能为 clr 函数指定此选项。

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

--returns null on null input | called on null input
--指定标量值函数的 onnullcall 属性。 如果未指定，则默认为 called on null input。 这意味着即使传递的参数为 null，也将执行函数体。
--如果在 clr 函数中指定了 returns null on null input，它指示当 sql server 接收到的任何一个参数为 null 时，它可以返回 null，而无需实际调用函数体。 如果 <method_specifier> 中指定的 clr 函数的方法已具有指示 returns null on null input 的自定义属性，
--但 create function 语句指示 called on null input，则优先采用 create function 语句指示的属性。 不能为 clr 表值函数指定 onnullcall 属性。

--execute as 子句
--对于本机编译的标量用户定义函数，execute as 是必需的。
--self
--execute as self 与 execute as user_name 等价，其中指定用户是创建或更改模块的用户。 创建或更改模块的用户的实际用户 id 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。
--owner
--指定模块内的语句在模块的当前所有者上下文中执行。 如果模块没有指定的所有者，则使用模块架构的所有者。 不能为 ddl 或登录触发器指定 owner。
--' user_name '
--指定模块内的语句在 user_name 指定的用户的上下文中执行。 将根据 user_name 来验证对模块内任意对象的权限。 不能为具有服务器作用域的 ddl 触发器或登录触发器指定 user_name。 请改用 login_name。
--user_name 必须存在于当前数据库中，并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置帐户，如 nt authority\localservice、nt authority\networkservice 或 nt authority\localsystem。
--执行上下文的用户 id 存储在元数据中，可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
--' login_name '
--指定模块内的语句在 login_name 指定的 sql server 登录的上下文中执行。 将根据 login_name 来验证对模块内任意对象的权限。 只能为具有服务器作用域的 ddl 触发器或登录触发器指定 login_name。
--login_name 不能为组、角色、证书、密钥或内置帐户，如 nt authority\localservice、nt authority\networkservice 或 nt authority\localsystem。

--function_body
--指定一系列定义函数值的 transact-sql 语句，这些语句在一起使用不会产生负面影响（例如修改表）。 function_body 仅用于标量函数和多语句表值函数。
--在标量函数中，function_body 是一系列 transact-sql 语句，这些语句一起使用可计算出标量值。
--在多语句表值函数中，function_body 是一系列 transact-sql 语句，这些语句将填充 table 返回变量。

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

示例：

--声明数据库引用
use testss;
go

--判断是否存在需要创建的函数，如果存在则删除
if exists(select * from sys.objects where name='scalarfun')
drop function scalarfun;
go

--创建用户自定义函数
create function dbo.scalarfun
(@num1 int,@num2 int,@num3 int =null)
returns int
--with encryption,schemabinding,returns null on null input,execute as owner
as
begin
declare @sum1 int=null;
if (@num1 is not null) and (@num2 is not null)
set @sum1=@num1+@num2;
else if (@num1 is not null)
set @sum1=@num1;
else if (@num2 is not null)
set @sum1=@num2;
else
set @sum1=0;
return @sum1;
end
go

示例结果：依次显示创建结果和调用结果。