sql server 自定義函數的使用

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

?

來自：http://hi.baidu.com/datachina/blog/item/801def0366c4e7ea09fa9344.html

下面是我從別的地方弄的一個例子，淺顯易懂：

編寫一個函數，該函數，可以通過輸入借書時間來判斷是否到期，當借閱時間大于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))--結果未到期