自定义函数1

自定义函数

用户定义自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回

用户自定义函数的类型:

标量函数:返回一个标量值

表格值函数{内联表格值函数、多表格值函数}:返回行集（即返回多个值）

1、标量函数

Create function 函数名（参数）

Returns 返回值数据类型

[with {Encryption | Schemabinding }]

[as]

begin

SQL语句(必须有return 变量或值)

End

Schemabinding :将函数绑定到它引用的对象上（注：函数一旦绑定，则不能删除、修改，除非删除绑定）

Create function AvgResult(@scodevarchar(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 字段别名

select dbo.AvgResult(‘s0002’) as result

 用户自定义函数返回值可放到局部变量中，用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 ,@avg3as 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’)

2、表格值函数

a、内联表格值函数

格式：

create function 函数名（参数）

returns table

[with {Encryption | Schemabinding }]

as

return(一条SQL语句)

create function tabcmess(@code varchar(10))

returns table

as

return(select ccode,scode from cmessagewhere ccode like @ccode)

b、多句表格值函数

  create function 函数名（参数）

  returns 表格变量名table (表格变量定义)

  [with {Encryption | Schemabinding }]

as

  begin

   SQL语句

  end

多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值

表格变量格式

returns @变量名 table (column 定义| 约束定义 [,…])

对表格变量中的行可执行select,insert,update,delete， 但select into 和 insert 语句的结果集是从存储过程插入。

Create function tabcmessalot (@codevarchar(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))--结果未到期