1、函數(shù)的種類

標(biāo)量函數(shù)：返回一個(gè)值的函數(shù)

內(nèi)嵌表函數(shù)：返回一個(gè)表，多行多列，只能寫Select語句

多語句函數(shù)：是一種受限的T_SQL ，可以寫 while if語句。受限表現(xiàn)在：在增刪改方面：只能對(duì)返回的表進(jìn)行增刪改，不能對(duì)別的表進(jìn)行增刪改。

For example 1:

create function fn_age (@ageadd int)
returns int
as
begin
declare @age int
declare @ageresult int

set @age=@ageadd+1
select @ageresult=avg(age) from lucy where age like @age
return @ageresult
end

//執(zhí)行，看一下結(jié)果
select dbo.fn_age(20) as result(指明列名)

For example2:

Create function ? 函數(shù)名（參數(shù)）

Returns ? 返回值數(shù)據(jù)類型

[with {Encryption | Schemabinding }]

[as]

begin

SQL 語句( 必須有return ? 變量或值)

?

End

?

Schemabinding : 將函數(shù)綁定到它引用的對(duì)象上（注：函數(shù)一旦綁定，則不能刪除、修改，除非刪除綁定）

?

?

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

?

執(zhí)行用戶自定義函數(shù)

select ? 用戶名。函數(shù)名 as ? 字段別名

select dbo.AvgResult(‘s0002’) as result

?

用戶自定義函數(shù)返回值可放到局部變量中，用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

?

函數(shù)引用

?

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’)

?ref: http://blog.sina.com.cn/s/blog_614316190100etjp.html