sql server 自訂函數

自訂函數

 

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

使用者自訂函數的類型:

純量涵式:返回一個標量值

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

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

將預存程序轉變成函數