UDF—MSQL Server 2000的新特性

来源：作者：出处：巧巧读书 2006-07-26 进入讨论组

　　
　　　　本文将介绍Microsoft SQL Server 2000的新增特性—用户自定义函数User-Defined Function（UDF），并演示几个常用的实例。
　　

　　　　UDF的功能类似SQL Server中内建的系统函数，如CONVERT、SUBSTRING、 DATAADD、 GETDATA、ISNULL等等。一个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可以带有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的功能越来越强大，我们编程人员工作起来就会越来越轻松。文字：http://www.qqread.com/network/server/h467178181.html进入讨论组讨论。