自定義函數

 

用戶定義自定義函數像內置函數一樣返回標量值，也可以將結果集用表格變量返回

用戶自定義函數的類型:

標量函數:返回一個標量值

表格值函數{內聯表格值函數、多表格值函數}:返回行集（即返回多個值）

1、標量函數

Create function 函數名（參數）

Returns 返回值數據類型

[with {Encryption | Schemabinding }]

[as]

begin

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

End

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

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 字段別名

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

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 cmessage where 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 (@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’)