【数据库】函数

函数可以单独使用，也可以套在select查询语句中使用，灵活得多，一个函数定义后，可以当库函数一样使用。

聚合函数

如：sum(列)、 avg(列) 、count() 、max( ) 、min( )

用于数据的统计的，针对数据库表操作的，是系统类的函数

系统内置函数

sql提供的通用函数，不是用于表的统计的，与上面聚合函数不一样。如sin( ) 、convert( )、 year( ) 、 getdate( )

日期时间类

1> Getdate( )

2> Year(日期) 返回日期的年份 month(日期) day(日期)

3> Dateadd(日期时间的部分量，偏差值, 日期) 返回指定日期的指定分量的偏移后的日期结果

例： 求今天后的95天后是哪一天？

dateadd(year,95,getdate()) 

其中的日期部分量：

year, month,quarter,day,week,hour,minute,second,millisecond

如：

select GETDATE()+105
select DATEADD(day,105,getdate())
select DATEADD(hour,200,getdate())
select DATEADD(hour,200,'1990-2-1 11:05:30')
select 姓名,出生日期,DATEADD(day,100,出生日期) from 学生表

4>datediff(日期时间部分量,起始日期,结束日期)

求两个日期的指定部分偏移量 (是上面dateadd反操作，根据两个日期求出指定部分的偏差量)

print Datediff(month,'1986-12-1','2001-9-2') 
declare @d1 datetime,@d2 datetime
set @d1='2021-4-20 12:00:00'
select @d2=GETDATE()
print datediff(minute,@d1,@d2)

字符串函数

1> left(串，n) 取串左n个字符

2> right(串，n) 取串右n个字符

3>substring(串,m,n) 从串的第m个字符开始共取n个字符出来(如果要取的字符串超过的实际，取完即可，容错处理)

4>len(串) 求串长

5>Ltrim(串) 去掉串左边多余的空格 Rtrim(串) 去掉串的右边多余空格

select RIGHT(学号,3) from 学生表
select SUBSTRING(学号,2,3) from 学生表
select REPLACE('很多坏人信法轮功，在中国法轮法是不允许','法轮功',REPLICATE('*',len('法轮功')))

数据类型转换

1>convert(目标类型及宽度，被转换前原值[,其它附加参数]

Convert(nchar(5), n) 
Convert(nchar(10),生日,21)  /*其中21是针对日期的格式参数*/

2> cast(表达式 as 新类型[长度])

select CAST(身高 as numeric(4,1)) from 学生表
select 姓名+'同学的身高是:'+convert(nchar(4),身高) from 学生表
select 姓名+'同学的身高是:'+cast(身高 as NCHAR(4)) from 学生表

自定义函数

标量函数

返回的是一个标准量，如一般数据类型int, char. 这种函数相对简单，通过函数体计算，返回调用处一个标量。

与其它语言类似，有参数时通过函数名后面参数表带去，也可以无参函数（但空括号不能省）。

定义格式：

Create function 函数名(形式参数表) returns 返回值的数据类型
 Begin
  函数体语句
 End

引例：输入两个整数a,b，返回之和.

C**：**

int f1(int a,int b)
{ 
   int t; 
   t=a+b;
   return t;
}

Sql中：

create function f1(@a int,@b int) returns int
begin
 declare @t int
 set @t=@a+@b
 return @t
end

print dbo.f1(5,1)

说明：C，java为把早期过程和函数概念合在一起，当函数不需要返回数据时，定义全时函数类型为void。 不需要返回值（所有输出返回操作都是过程内完成）的函数就是过程procedure。

例：传入一个正整数n，使用函数jc(n)求n! , f(n)=n!

create function jc(@n int) returns bigint
begin
 declare @t bigint,@i int
 set @t=1
 set @i=1
 while(@i<=@n)
  begin
   set @t=@t*@i
   set @i=@i+1
  end
 return @t
end

print dbo.jc(5)

完整: 如要经常根据成绩值计算实得学分（cfx(课程号,成绩）），根据成绩值评等级，90及以上为优秀，70-90为良好，60-70为合格，60重修（DJPD（成绩）

create function djpd(@cj int) returns nchar(2)
begin
 declare @dj nchar(2)
 if(@cj>=90)  set @dj='优秀'
 else if(@cj>=70) set @dj='良好'
 else if(@cj>=60) set @dj='合格'
 else set @dj='重修'
 return @dj
end

create function cxf(@kch char(3),@cj int) returns int
begin
 declare @xf int
 select @xf=学分 from 课程表 where 课程号=@kch
 if(@cj<60) set @xf=0
 return @xf
end

select 学号,课程号,成绩,dbo.cxf('103',成绩) from 成绩表 where 课程号='103'

select 学号,课程号,成绩,dbo.cxf(课程号,成绩)as 实得学分,dbo.djpd(成绩) as 等级 from 成绩表

删除一个函数：

drop function dbo.jc 

注意：函数调用时，最后加上名字空间的。

例：把上面法轮功例子函数化

create function myrepl(@str1 nvarchar(800),@str2 nvarchar(10)) returns nvarchar(800)
begin
 declare @strT nvarchar(800)
 set @strT=replace(@str1,@str2,replicate('*',len(@str2)))
 return @strT
end

select dbo.myrepl('abcdefcdaax','cd')
select 学号,姓名,家庭住址,dbo.myrepl(家庭住址,'成都') from 学生表

例：定义一个函数dj(n)判断成绩的等级。90以上为优，80以上为良，70以上中，60以上及格，60以下为差

create function dj(@n int) returns nchar(2)
begin
 declare @dj0 nchar(2)
 if(@n>=90) set @dj0='优'
 else if(@n>=80) set @dj0='良'
 else if(@n>=70) set @dj0='中'
 else if(@n>=60) set @dj0='及格'
 else set @dj0='差'
 return @dj0
end

select *,dbo.dj(成绩) as 等级 from 成绩表

例：编写函数实现：根据学生学号，课程名称，查出该生该课的实际所得学分（60及以上得学分，不及格该科学分为0）

create function sdxf(@xm nchar(6),@kcm nchar(10))returns int
begin 
 declare @xf int,@cj int
 select @cj=成绩 from 成绩表 where 学号=(select 学号 from 学生表 where 姓名=@xm) and 课程号=(select 课程号 from 课程表 where 名称=@kcm)
 if(@cj>=60) 
  select @xf=学分 from 课程表 where 名称=@kcm
 else set @xf=0;
 return @xf 
end

select 学号,姓名,dbo.sdxf(姓名,'大学英语')as 实得学分 from 学生表

表值函数: 内联表值函数

一般函数返回的一个标量值，表值函数返回的是一个表(视图)，由此得名

这种表是一个关系，在结构表头与一般的物理表相没区别，可以随意存放多行多列数据—故使用表值函数可以返回大量数据。表值函数内部函数体的核心任务是，把所有需要的返回给调用处的数据计算出来，存放到内存表中。最后一次性返回。

格式：

create function 函数名(形式参数表) returns table
As
 Begin 
   函数体
 End

引例：输入一个姓氏，查出该姓所有人的学号，姓名，生日,该姓氏比姓杨的人多几个？

理论上看，一个函数无法返回这么大一堆数据的，现在可以使用表值函数返回多个数。

create function my15(@xs nchar(1)) returns @table1 table(学号 CHAR(11),XM nchar(6),生日 smalldatetime,人数 int ,多于杨人数 int)
as 
begin
 insert into @table1(学号,XM,生日) select 学号,姓名,出生日期 from 学生表 where LEFT(姓名,1)=@xs
 declare @rs int ,@rsY int
 select @rs=COUNT(*) from 学生表 where left(姓名,1)=@xs
 select @rsY=COUNT(*) from 学生表 where left(姓名,1)='杨'
 insert into @table1(人数,多于杨人数)values(@rs,@rs-@rsY)
 return 
end

select * from dbo.my15('李')                             

因为内部的select查询的结果本身就是表视图，生成的返回临时表也它查询结果一致，不需要再自定义临时表。对表值函数的理解：当表一样的用，是一个带参数的，带条件查询的结果，返回的满足条件的结果

例：多语句表值函数。输入一个课程名，返回此课程平均分，最高分，选课人数, 总实得学分，不及格门数,该门课最高分的学生姓名.

分析：以上结果数据太多，难以一句sql命名搞定，又在不增加访问物理数据库次数情况下，减少服务器与客户端的数据来往次数，一次性在服务器完成所有计算，把结果一次性返回给客户端----使用表值函数

create function fun11(@kcm nchar(10)) returns @t table(课程名 nchar(10),平均分 numeric(4,1),最高分 numeric(4,1), 选课人数 int,得到学分人数 int,不及格比例 numeric(4,1))
as 
begin
  insert into @t(课程名) values(@kcm)
  update @t set 平均分=(select AVG(成绩) from 成绩表 where 课程号=(select 课程号 from 课程表 where 名称=@kcm))
  update @t set 最高分=(select MAX(成绩) from 成绩表 where 课程号 =(select 课程号 from 课程表 where 名称=@kcm))
  update @t set 选课人数=(select COUNT(*) from 成绩表 where 课程号 =(select 课程号 from 课程表 where 名称=@kcm))
  update @t set 得到学分人数=(select COUNT(*) from 成绩表 where 课程号=(select 课程号 from 课程表 where 名称=@kcm) and 成绩>=60 )
  Update @t set 不及格比例=（((select COUNT(*) from 成绩表 where 课程号=(select 课程号 from 课程表 where 名称=@kcm))- select COUNT(*) from 成绩表 where 课程号=(select 课程号 from 课程表 where 名称=@kcm) and 成绩>=60)/(select COUNT(*) from 成绩表 where 课程号=(select 课程号 from 课程表 where 名称=@kcm)))*100
  Declare @xm0 nchar(10)
  Select @xm0=姓名 from 学生表 where 学号=(select top 1 学号 from 成绩表 where 课程号=(select 课程号 from 课程表 where 名称=@kcm) order by 成绩 desc )
 insert into @t(课程名)values(@xm0)
 return 
end

select * from dbo.fun11('大学英语')