mysql function 参数默认值_SQL Server 自定义函数（Function）——参数默认值

sql server 自定义函数分为三种类型：标量函数(Scalar Function)、内嵌表值函数(Inline Function)、多声明表值函数(Multi-Statement Function)

标量函数：标量函数是对单一值操作，返回单一值。

内嵌表值函数：内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表，内联表值型函数没有由BEGIN-END 语句括起来的函数体。

多声明表值函数：它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。

它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。

创建标量函数的语法：

1 create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])2 returns标量返回值类型3 [with {Encryption | Schemabinding }]

4 [as]

5 begin

6 函数体(即 Transact-SQL 语句)7 return 变量/标量表达式8 end

下面自定义一个根据传入参数(即学生性别)来计算学生平均身高的 Function，示例如下：

1 useTest2 go

3

4 create function dbo.AvgHeight(@Sex varchar(10)='男') --在这里是给参数默认值，即可以不传参数调用这个函数

5 returns decimal

6 as

7 begin

8 declare @AvgHeight decimal(10,2)9 select @AvgHeight=AVG(Convert(decimal(10,2),S_Height)) from Student where S_Sex=@Sex

10 return @AvgHeight

11 end

12 go

执行用户自定义标量函数：

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

3 select dbo.AvgHeight(default) as AvgHeight; --返回 179

4 select dbo.AvgHeight('男'); --返回 179

5 select dbo.AvgHeight('女'); --返回 176

PS:创建函数时指定了函数所有者，那么调用的时候也必须指定函数的所有者。(一般都为 dbo)

调用自定义函数时如果想不传入参数而使用默认值，那么必须使用 default 关键字。如果自定义函数的参数没有默认值，那么会返回 null。

创建内嵌表值函数语法：

1 create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])2 returns table

3 [with {Encryption | Schemabinding }]

4 [as]

5 return(单个 SELECT 语句，确定返回的表的数据。)

下面自定义一个根据传入参数(即学生学号)来查询学生信息的 Function，示例如下：

1 useTest2 go

3

4 create function dbo.SearchStuInfo(@Stu_No varchar(50)='001')5 returns table

6 as

7 return(8 selectS_StuNo,S_Name,S_Sex,S_Height,S_BirthDate9 from Student where S_StuNo=@Stu_No)10 go

执行用户自定义内嵌表值函数：

1 select * from dbo.SearchStuInfo(default); --返回学号为 001 的学生信息

2

3 select * from dbo.SearchStuInfo('008'); --返回学号为 008 的学生信息

创建多声明表值函数的语法：

1 create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])2 returns @表变量 table表的定义(即列的定义和约束)3 [with {Encryption | Schemabinding }]

4 [as]

5 begin

6 函数体(即 Transact-SQL 语句)7 return

8 end

下面自定义一个根据传入参数(即学生学号)来查询学生信息和课程，并计算出该学生的身高是否大于所有学生的平均身高的 Function，示例如下：

1 useTest2 go

3

4 create function dbo.SearchStuCou(@StuN varchar(10)='001')5 returns @StuInfo table(6 StuNo varchar(50) not null primary key,7 StuName varchar(10) not null,8 StuSex varchar(10) not null,9 StuHeight varchar(10) null,10 StuAvgHeight decimal(10,2) null,11 StuConAvgHeight varchar(30) null,12 StuCou varchar(10) null

13 )14 as

15 begin

16 declare @Height decimal(10,2)17 declare @AvgHeight decimal(10,2)18 declare @ConAvgHeight varchar(30)19

20 select @AvgHeight=AVG(Convert(decimal(10,2),S_Height)) --查询所有同学的平均身高

21 fromStudent22

23 select @Height=Convert(decimal(10,2),S_Height) --查询指定学号的学生身高

24 fromStudent25 where S_StuNo=@StuN

26

27 if((@Height-@AvgHeight) > Convert(decimal(10,2),0))28 begin

29 set @ConAvgHeight='大于平均身高'

30 end

31 else if((@Height-@AvgHeight) = Convert(decimal(10,2),0))32 begin

33 set @ConAvgHeight='等于平均身高'

34 end

35 else

36 begin

37 set @ConAvgHeight='小于平均身高'

38 end

39

40 insert into @StuInfo(StuNo,StuName,StuSex,StuHeight,StuAvgHeight,StuConAvgHeight,StuCou)41 select s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,@AvgHeight,@ConAvgHeight,c.C_Name fromStudent s42 left join Course c on s.C_S_Id=c.C_Id43 where S_StuNo=@StuN

44

45 return

46 end

47 go

执行用户自定义多声明表值函数：

1 select * from dbo.SearchStuCou(default);2

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

5 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