SQL Server 自定义函数（Function）——参数默认值

ybaidukuai

　　sql server 自定义函数分为三种类型：标量函数（Scalar Function）、内嵌表值函数（Inline Function）、多声明表值函数（Multi-Statement Function）
　　标量函数：标量函数是对单一值操作，返回单一值。
　　内嵌表值函数：内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表，内联表值型函数没有由BEGIN-END 语句括起来的函数体。
　　多声明表值函数：它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。
　　它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。
　　
　　创建标量函数的语法：



create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
returns 标量返回值类型
[with {Encryption | Schemabinding }]
[as]
begin
     函数体(即 Transact-SQL 语句)
     return 变量/标量表达式
end
　　下面自定义一个根据传入参数（即学生性别）来计算学生平均身高的 Function，示例如下：



use Test
go
create function dbo.AvgHeight(@Sex varchar(10)='男')   
returns decimal
as
begin
     declare @AvgHeight decimal(10,2)
     select @AvgHeight=AVG(Convert(decimal(10,2),S_Height)) from Student where S_Sex=@Sex
     return @AvgHeight
end
go
　　执行用户自定义标量函数：



select [函数的所有者].函数名 as 字段别名

select dbo.AvgHeight(default) as AvgHeight;   
select dbo.AvgHeight('男');     
select dbo.AvgHeight('女');     
　　PS:创建函数时指定了函数所有者，那么调用的时候也必须指定函数的所有者。（一般都为 dbo）
　　调用自定义函数时如果想不传入参数而使用默认值，那么必须使用 default 关键字。如果自定义函数的参数没有默认值，那么会返回 null。
　　创建内嵌表值函数语法：



create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
returns table
[with {Encryption | Schemabinding }]
[as]
return(单个 SELECT 语句，确定返回的表的数据。)
　　下面自定义一个根据传入参数（即学生学号）来查询学生信息的 Function，示例如下：



use Test
go
create function dbo.SearchStuInfo(@Stu_No varchar(50)='001')
returns table
as
return(
select S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate
from Student where S_StuNo=@Stu_No)
go
　　执行用户自定义内嵌表值函数：



select * from dbo.SearchStuInfo(default);   
select * from dbo.SearchStuInfo('008');        
　　创建多声明表值函数的语法：



create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
returns @表变量 table 表的定义(即列的定义和约束)
[with {Encryption | Schemabinding }]
[as]
begin
     函数体(即 Transact-SQL 语句)
     return
end
　　下面自定义一个根据传入参数（即学生学号）来查询学生信息和课程，并计算出该学生的身高是否大于所有学生的平均身高的 Function，示例如下：



use Test
go
create function dbo.SearchStuCou(@StuN varchar(10)='001')
returns @StuInfo table (
     StuNo varchar(50) not null primary key,
     StuName varchar(10) not null,
     StuSex  varchar(10) not null,
     StuHeight varchar(10) null,
     StuAvgHeight decimal(10,2) null,
     StuConAvgHeight varchar(30) null,
     StuCou varchar(10) null
)
as
begin
     declare @Height decimal(10,2)
     declare @AvgHeight decimal(10,2)
     declare @ConAvgHeight varchar(30)

     select @AvgHeight=AVG(Convert(decimal(10,2),S_Height))        
     from Student

     select @Height=Convert(decimal(10,2),S_Height)        
     from Student
     where S_StuNo=@StuN
     if((@Height-@AvgHeight) > Convert(decimal(10,2),0))
     begin
         set @ConAvgHeight='大于平均身高'
     end
     else if((@Height-@AvgHeight) = Convert(decimal(10,2),0))
     begin
         set @ConAvgHeight='等于平均身高'
     end
     else
     begin
         set @ConAvgHeight='小于平均身高'
     end
     insert into @StuInfo(StuNo,StuName,StuSex,StuHeight,StuAvgHeight,StuConAvgHeight,StuCou)
     select s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,@AvgHeight,@ConAvgHeight,c.C_Name from Student s
     left join Course c on s.C_S_Id=c.C_Id
     where S_StuNo=@StuN
     return
end
go
　　执行用户自定义多声明表值函数：



select * from dbo.SearchStuCou(default);

select * from dbo.SearchStuCou('015');

select * from dbo.SearchStuCou('009');

　　适用范围
　　1.      只查询，不修改数据库的状态（修改、删除表中记录等）
　　2.      结果集需要通过递归等方法得到时，可以使用函数，函数比较灵活
　　3.      结果集需要直接被引用时，可以使用函数。需要对结果集进行再加工（指放在select语句中等），可以使用函数，函数可以嵌在select等sql语句中。
　　注意事项：
　　用户自定义函数不能用于执行一系列改变数据库状态的操作
　　在编写自定义函数时需要注意的：
　　对于标量函数：
　　1.      所有的入参前都必须加@
　　2.      create后的返回，单词是returns，而不是return
　　3.      returns后面的跟的不是变量，而是返回值的类型，如：int，char等。
　　4.      在begin/end语句块中，是return。
　　内嵌表值函数：
　　1.      只能返回table，所以returns后面一定是TABLE
　　2.      AS后没有begin/end，只有一个return语句来返回特定的记录。
　　多语句表值函数：
　　1.      returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。
　　2.      在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。
　　3.      最后只需要return，return后面不跟任何变量。
　　参考：
　　http://www.cnblogs.com/xueyuangudiao/archive/2011/08/03/2126580.html