内置函数:
sql原有内置函数,包括:合并函数(处理多数据返回单一结果)、scala函数(处理单一数据返回单一结果)。
自定义标量函数:
- 自定义函数可以返回单一标量值也可以返回表格变量。
- 结构(单一标量值):
Create function 函数名(参数)
Returns 返回值数据类型
[with {Encryption | Schemabinding《将函数绑定到它引用的对象上(注:函数一旦绑定,则不能删除、修改,除非删除绑定)》 }]
[as]
begin
SQL语句(必须有return 变量或值)
End
- 例子:
Create function AvgResult(@scode varchar(10))
Returns real
As
Begin
Declare @avg real
Declare @code varchar(11)
Set @code=@scode + ‘%’
Select @avg=avg(result) from LearnResult_baijiali
Where scode like @code
Return @avg
End
- 执行用户自定义函数:select 用户名.函数名 as 字段别名
- 用户自定义函数返回值可放到局部变量中,用set ,select,exec赋值
declare @avg1 real ,@avg2 real ,@avg3 real
select @avg1= dbo.AvgResult(‘s0002’)
set @avg2= dbo.AvgResult(‘s0002’)
exec @avg3= dbo.AvgResult ‘s0002’
select @avg1 as avg1 ,@avg2 as avg2 ,@avg3 as avg3
- 函数应用例子:
create function code(@scode varchar(10))
returns varchar(10)
as
begin
declare @ccode varchar(10)
set @scode = @scode + ‘%’
select @ccode=ccode from cmessage
where ccode like @scode
return @ccode
end
select name from class where ccode = dbo.code(‘c001’)
表格值函数:
-
内联表格值函数
- 格式:
create function 函数名(参数)
returns table
[with {Encryption | Schemabinding }]
as
return(一条SQL语句)
- 例子:
create function tabcmess(@code varchar(10))
returns table
as
return(select ccode,scode from cmessage where ccode like @ccode)
-
-
多句表格值函数
- 格式:
create function 函数名(参数)
returns 表格变量名table (表格变量定义);或者是returns @变量名 table (column 定义| 约束定义 [,…])
[with {Encryption | Schemabinding }]
as
begin
SQL语句
end
- 包含多条SQL语句,至少有一条在表格变量中填上数据值
- 对表格变量中的行可执行select,insert,update,delete , 但select into 和 insert 语句的结果集是从存储过程插入。
- 例子:
Create function tabcmessalot (@code varchar(10))
Returns @ctable table(code varchar(10) null,cname varchar(100) null)
As
Begin
Insert @ctable
Select ccode,explain from cmessage
Where scode like @code
return
End
Select * from tabcmessalot(‘s0003’)
例子实践:
- 功能说明:通过输入借书时间来判断是否到期,当借阅时间大于30天,返回已经过期;否则返回还未到期。
- 代码:
CREATE FUNCTION IsDateout(@BDate datetime)
returns nvarchar(20)
AS
BEGIN
DECLARE @myresult nvarchar(20)
IF (datediff(day,@BDate,getdate())>30)
BEGIN
SET @myresult='已过期'
end
else
begin
set @myresult='未到期'
end
RETURN (@myresult)
END
SELECT dbo.IsDateout(cast('2009-12-17' AS datetime))--结果已过期
SELECT dbo.IsDateout(cast('2010-12-17' AS datetime))--结果未到期