UDF—SQL Server 2000的新特性

create function [ owner_name.] function_name

( { { @parameter_name scalar_parameter_data_type

[,…n] ] )

return scalar_return_data_type

[with < function_option> [, … n]]

[as]

begin

function_body

return scalar_expression

end

每个udf可以带有0个到1024个参数，每个参数可以是除了timestamp、cursor、table 以外所有的数据类型；函数返回值的限制要更多一些，它不可以是text、ntext、image、timestamp、cursor和table。

函数体是udf的主要部分，它有两个选项: encryotion和 schemabinding。

schemabinding是sql server 2000的新增功能，可以和视图一同使用。该选项不允许删除和修改被该函数引用的对象。这样可以防止无效的函数和视图对它们引用的对象进行结构上的修改。

大家会注意到函数体以begin开始，end结束。这一点不同于创建存储过程、触发器和视图。当您忘了写上begin/end时，系统会返回一个提示信息“incorrect syntax near ‘return’”。为什么不直接说少了begin/end，这有点让人费解。

下面我用几个例子来说明udf的应用。

greatest and least

　　为了区别于系统函数max和min，我给新函数命名为greatest和least，它们会从以参数形式输入的两个值中找出最大值和最小值。

case语句是两个函数的核心：

case when value1 > value2 then value1 else value2 end

虽然函数很简单，但用途是很广的。

create function dbo.greatest

-- return the maximum of two parameters

(@val1 sql_variant,

@val2 sql_variant)

returns sql_variant

as

begin

return (case when @val1 > @val2 then @val1 else @val2 end)

end

go

―――――――――――――――――――――――――――――――

create function dbo.least

-- return the minimum of two parameters

( @val1 sql_variant,

@val2 sql_variant )

returns sql_variant

as

begin

return (case when @val1 < @val2 then @val1 else @val2 end)

end

go

大小写转换函数

该函数有两个参数：@string和@capitalize_what。

依据 @capitalize_what的值，函数有不同的功能：

¨ @capitalize_what ＝ ‘string’“

函数将 @string的第一个非空字符转换成大写， 其余部分改为小写。

¨ @capitalize_what ＝ ‘sentence’

函数将 @string中的每一句的首个非空字符转换为大写，句子其余部分转换为小写。断句的依据是’.’、’!’、’?’

¨ @capitalize_what ＝ ‘word’

函数将 @string中的每个词都转换成首字符大写，其余小写的形式。

create function dbo.capitalize (

-- capitalize the first character of every word,

-- sentence, or the whole string. put the rest to lowercase.

@string varchar (8000),

@capitalize_what varchar (8) = ’string’

-- string: capitalize the first letter of the string

-- sentence: capitalize the first letter of every sentence.

-- delimiters: ./!/?

-- word: capitalize the first letter of every word.

-- delimiters: any characters other than letters and digits.

)

returns varchar(8000)

as

begin

declare @position smallint,

@char char(1),

@first_char char (1),

@word_start smallint

set @capitalize_what = lower( @capitalize_what )

set @word_start = 0

if @capitalize_what in (‘word’, ‘sentence’)

begin

set @position = datalength( @string )

while @position >= 0 begin

set @char = case @position

when 0 then ’.’

else upper( substring(

@string, @position,

1 ) )

end

if @char between ’a’ and ’z’

or @char between ’0’ and ’9’ begin

set @word_start = @position

set @first_char = upper( @char )

end

else begin

if @capitalize_what = ’word’

or @char in ( ’.’, ’!’, ’?’ ) begin

if @word_start > 0

and @first_char between ’a’

and ’z’

set @string = stuff(

@string, @word_start,

1, @first_char )

set @word_start = 0

end

end

set @position = @position - 1

end

end

else begin -- capitalize the first character

set @position = 0

while @position < datalength( @string )

begin

set @position = @position + 1

set @char = upper( substring( @string,

@position, 1 ) )

if @char between ’a’ and ’z’

or @char between ’0’ and ’9’ begin

set @string = stuff( @string,

@position, 1, @char )

set @position = 9999

end

end

end

return( @string )

end

go

小结

sql server 2000 的 udf的应用是很广泛的，它会给编程人员带来极大的便利。您可以建立自己的’system’ udf，存在master数据库中，可以为任何数据库进行调用。

udf也有不足，我们知道系统函数可以任意调有，不管您使用大写、小写或者大小写混合。udf却不行，它是大小写敏感的。

在未来的版本中，我希望微软为udf增加默认值的功能，以后我们可以这样定义一个函数。

creat function dbo.test_default

（ @parm int = 0 ）

return int

as

begin

return ( @parm )

end

udf中诸如此类的小问题还有不少，希望udf的功能越来越强大，我们编程人员工作起来就会越来越轻松。