UDF—MSQL Server 2000的新特性

云海顽石

　　 　　　　本文将介绍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 [, … 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 = 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的功能越来越强大，我们编程人员工作起来就会越来越轻松。 　　 <