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 use Test
 2 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 use Test
 2 go
 3 
 4 create function dbo.SearchStuInfo(@Stu_No varchar(50)='001')
 5 returns table
 6 as
 7 return(
 8 select S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate 
 9 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 use Test
 2 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     from Student 
22 
23     select @Height=Convert(decimal(10,2),S_Height)        --查询指定学号的学生身高
24     from Student 
25     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 from Student s 
42     left join Course c on s.C_S_Id=c.C_Id
43     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

适用范围

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

相关文章