本文将介绍Microsoft SQL Server 的新增特性—用户自定义函数UserDefined Function（UDF）并演示几个常用的实例
UDF的功能类似SQL Server中内建的系统函数如CONVERTSUBSTRING DATAADD GETDATAISNULL等等一个UDF可以没有参数或者带有一个或多个参数函数运行后将会返回一个函数值定义UDF的语法如下
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可以带有个到个参数每个参数可以是除了timestampcursortable 以外所有的数据类型函数返回值的限制要更多一些它不可以是textntextimagetimestampcursor和table
函数体是UDF的主要部分它有两个选项: ENCRYOTION和 SCHEMABINDING
SCHEMABINDING是SQL Server 的新增功能可以和视图一同使用该选项不允许删除和修改被该函数引用的对象这样可以防止无效的函数和视图对它们引用的对象进行结构上的修改
大家会注意到函数体以Begin开始End结束这一点不同于创建存储过程触发器和视图当您忘了写上Begin/End时系统会返回一个提示信息Incorrect syntax near RETURN为什么不直接说少了Begin/End这有点让人费解
下面我用几个例子来说明UDF的应用
Greatest and Least
为了区别于系统函数Max和Min我给新函数命名为Greatest和Least它们会从以参数形式输入的两个值中找出最大值和最小值
Case语句是两个函数的核心
CASE WHEN value > value THEN value ELSE value END
虽然函数很简单但用途是很广的
CREATE FUNCTION dboGreatest
Return the maximum of two parameters
(@Val SQL_VARIANT
@Val SQL_VARIANT)
RETURNS SQL_VARIANT
AS
BEGIN
RETURN (CASE WHEN @val > @val THEN @val ELSE @val END)
END
go
―――――――――――――――――――――――――――――――
CREATE FUNCTION dboLeast
Return the minimum of two parameters
( @val SQL_VARIANT
@val SQL_VARIANT )
RETURNS SQL_VARIANT
AS
BEGIN
RETURN (CASE WHEN @val < @val THEN @val ELSE @val END)
END
Go
大小写转换函数
该函数有两个参数@String和@Capitalize_What
依据 @Capitalize_What的值函数有不同的功能
¨ @Capitalize_What ＝ string
函数将 @string的第一个非空字符转换成大写 其余部分改为小写
¨ @Capitalize_What ＝ sentence
函数将 @string中的每一句的首个非空字符转换为大写句子其余部分转换为小写断句的依据是!?
¨ @Capitalize_What ＝ word
函数将 @string中的每个词都转换成首字符大写其余小写的形式
CREATE FUNCTION dboCapitalize (
Capitalize the first character of every word
sentence or the whole string Put the rest to lowercase
@String VARCHAR ()
@Capitalize_What VARCHAR () = 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()
AS
BEGIN
DECLARE @Position SMALLINT
@Char CHAR()
@First_Char CHAR ()
@Word_Start SMALLINT
SET @Capitalize_What = LOWER( @Capitalize_What )
SET @Word_Start =
IF @Capitalize_What IN (word sentence)
BEGIN
SET @Position = DATALENGTH( @String )
WHILE @Position >= BEGIN
SET @Char = CASE @Position
WHEN THEN
ELSE UPPER( SUBSTRING(
@String @Position
) )
END
IF @Char BETWEEN A AND Z
OR @Char BETWEEN and BEGIN
SET @Word_Start = @Position
SET @First_Char = UPPER( @Char )
END
ELSE BEGIN
IF @Capitalize_What = word
OR @Char in ( ! ? ) BEGIN
IF @Word_Start >
AND @First_Char BETWEEN A
AND Z
SET @String = STUFF(
@String @Word_Start
@First_Char )
SET @Word_Start =
END
END
SET @Position = @Position
END
END
ELSE BEGIN Capitalize the first character
SET @Position =
WHILE @Position < DATALENGTH( @String )
BEGIN
SET @Position = @Position +
SET @Char = UPPER( SUBSTRING( @String
@Position ) )
IF @Char BETWEEN A AND Z
OR @Char BETWEEN AND BEGIN
SET @String = STUFF( @String
@Position @Char )
SET @Position =
END
END
END
RETURN( @String )
END
go
小结
SQL Server 的 UDF的应用是很广泛的它会给编程人员带来极大的便利您可以建立自己的system UDF存在Master数据库中可以为任何数据库进行调用
UDF也有不足我们知道系统函数可以任意调有不管您使用大写小写或者大小写混合UDF却不行它是大小写敏感的
在未来的版本中我希望微软为UDF增加默认值的功能以后我们可以这样定义一个函数
CREAT FUNCTION dboTest_default
（ @parm int = ）
RETURN INT
AS
BEGIN
RETURN ( @parm )
END
UDF中诸如此类的小问题还有不少希望UDF的功能越来越强大我们编程人员工作起来就会越来越轻松
