SQLServer自定义函数

SQLServer自定义函数

 

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

用户自定义函数的类型:

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

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

 

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/item/0d579f381111ecc6392ffa7f

 

标量值函数区别于表值函数,表值函数一般都返回含有多个记录行的结果集,而标量值函数只返回一个值。

CREATE FUNCTION [schema_name.]function_name

([{@parameter_name [type_schema_name.]data_type [=default]}[,…n]])
RETURNS return_data_type
[WITH <function_option>[,…n]
AS
BEGIN
function_body
RETURN scalar_expression
END
 

schema_name.function_name是包含架构的函数名称。
paramter_name是函数的参数。
type_schema_name.data_type是包含架构的参数数据类型。
default是参数的默认值。
return_data_type是标量值函数返回值的数据类型,可以是除TEXT、NTEXT、IMAGE、CURSOR和TIMESTAMP之外的所有数据类型。不能是非标量值的数据类型,如TABLE。
function_option是函数的选项。可以具有一个或多个选项。

function_body是一系列的SQL语句,可以将这些语句执行的结果查入到TABLE变量中。
scalar_expression是标量值的表达式,即在创建标量值函数时,一定要确保使用RETURN语句返回一个标量值。

About 智足者富

http://chenpeng.info

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>