SQL SERVER——自定义函数

用户自定义函数（User Defined Functions）是SQL Server 的数据库对象，它不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。用户自定义函数中存储了一个Transact-SQL 例程，可以返回一定的值。

　　在SQL Server 中根据函数返回值形式的不同将用户自定义函数分为三种类型：

　　标量型函数（Scalar functions）

　　标量型函数返回一个确定类型的标量值其返回值类型为除TEXT、 NTEXT、 IMAGE、CURSOR、 TIMESTAMP 和TABLE 类型外的其它数据类型。函数体语句定义在BEGIN-END语句内，其中包含了可以返回值的Transact-SQL 命令。

　　内联表值型函数（Inline table-valued functions）

　　内联表值型函数以表的形式返回一个返回值，即它返回的是一个表内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。

　　多声明表值型函数（Multi-statement table-valued functions）

　　多声明表值型函数可以看作标量型和内联表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。 　

　　SQL Server 为三种类型的用户自定义函数提供了不同的命令创建格式。

　　（1） 创建标量型用户自定义函数（Scalar functions） 其语法如下：

Create function --函数名（参数）
Returns --返回值数据类型，注意是Returns(有s)
[with {Encryption | Schemabinding }]
[as]
begin
--SQL语句(必须有return 变量或值)
End
Schemabinding :--将函数绑定到它引用的对象上（注：函数一旦绑定，则不能删除、修改，除非删除绑定）
Create function AvgResult(@scode varchar(10))
Returns real
As
Begin
   Declare @avg real
   Declare @code varchar(11)
   Set @code=@scode + ‘%’
   Select @avg=avg(result) from LearnResult_baijiali
Where scode like @code
Return @avg
End

--执行用户自定义函数
select 用户名.函数名 as 字段别名
select dbo.AvgResult(‘s0002’) as result
--用户自定义函数返回值可放到局部变量中，用set ,select,exec赋值
declare @avg1 real ,@avg2 real ,@avg3 real
select @avg1= dbo.AvgResult(‘s0002’)
set @avg2= dbo.AvgResult(‘s0002’)
exec @avg3= dbo.AvgResult ‘s0002’
select @avg1 as avg1 ,@avg2 as avg2 ,@avg3 as avg3
--函数引用
create function code(@scode varchar(10))
returns varchar(10)
as
begin
declare @ccode varchar(10)
set @scode = @scode + ‘%’
select @ccode=ccode from cmessage
   where ccode like @scode
return @ccode
end
select name from class where ccode = dbo.code(‘c001’)

2、表格值函数

a、 内联表格值函数

格式：

create function 函数名（参数）
returns table
[with {Encryption | Schemabinding }]
as
return(一条SQL语句)
create function tabcmess(@code varchar(10))
returns table
as
return(select ccode,scode from cmessage where ccode like @ccode)

b、 多句表格值函数

格式：

create function 函数名（参数）
returns 表格变量名table (表格变量定义)
[with {Encryption | Schemabinding }]
as
begin
--SQL语句
end

多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值表格变量格式

returns @变量名 table (column 定义| 约束定义 [,…])

对表格变量中的行可执行select,insert,update,delete ， 但select into 和 insert 语句的结果集是从存储过程插入。

Create function tabcmessalot (@code varchar(10))
Returns @ctable table(code varchar(10) null,cname varchar(100) null)
As
Begin
Insert @ctable
Select ccode,explain from cmessage
Where scode like @code
return
End
Select * from tabcmessalot(‘s0003’)