--第一种标准函数
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
--返回固定值
--内嵌表值函数
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
--返回table
--多声明表值函数
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