SQLServer之创建表值函数
表值函数创建注意事项
⽤户定义表值函数返回 table 数据类型。
对于内联表值函数，没有函数主体，表是单个 SELECT 语句的结果集。
表值函数主要⽤于数据计算出来返回结果集。
使⽤SSMS数据库管理⼯具和T-SQL脚本创建表值函数语法相同。
使⽤T-SQL脚本创建表值函数
语法：
语法⼀：
--声明数据库引⽤
use 数据库名;
go
--判断是否存在表值函数，如果存在则删除，不存在则创建
if exists(select * from sys.objects where name=表值函数名称)
drop function 表值函数名称;
go
parameter数据类型--创建表值函数
create functino [schema_name.] function_name
(@parameter_name [as] [type_schema_name.] parameter_data_type [ = default ] [ readonly],..n) returns table
[with] [encryption][,][schemabinding]
as
return [ ( ] select_stmt [ ) ]
go
语法⼆：
--声明数据库引⽤
use 数据库名;
go
--判断是否存在表值函数，如果存在则删除，不存在则创建
if exists(select * from sys.objects where name=表值函数名称)
drop function 表值函数名称;
go
--创建表值函数
create functino [schema_name.] function_name
(@parameter_name [as] [type_schema_name.] parameter_data_type [ = default ] [ readonly],..n)
returns @tablename table
(
@parameter_name [as] [type_schema_name.] parameter_data_type [ = default ] ,
...n
)
begin
insert into @tablename(参数1，参数2,...n)
select 参数1，参数2,...n from table
[inner | left | right] [join] [table] [on] [条件]
[where 条件]
[group by 条件]
[having 条件]
[order by 条件]
;
return
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，则不能按照将影响函数定义的⽅式修改基对象。 必须⾸先修改或删除函数定义本⾝，才能删除将要修改的对象的依赖关系。
--@tablename
--⾃定义表名
调⽤语法：
select 参数名 from [架构名.][函数名称](参数) ;
⽰例：
--声明数据库引⽤
use testss;
go
--判断是否存在表值函数，如果存在则删除
if exists(select * from sys.objects where name='tablefun')
drop function tablefun;
go
--创建表值函数
create function dbo.tablefun(@ids int=null)
returns table
with encryption,schemabinding
as
return (select id,name,sex,age,classid,height st1 where id=@ids); go
⽰例结果：依次显⽰创建结果和调⽤结果。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论