我們今天主要向大家講述的是對SQL Server 2000 UDF進(jìn)行深度發(fā)掘的實(shí)際操作，即Microsoft SQL Server 2000數(shù)據(jù)庫的新增特性—用戶的User-Defined Function（UDF）自定義函數(shù)，并演示幾個(gè)常用的實(shí)例。

UDF的功能類似SQL Server中內(nèi)建的系統(tǒng)函數(shù)，如CONVERT、SUBSTRING、 DATAADD、 GETDATA、ISNULL等

本文將介紹Microsoft SQL Server 2000的新增特性—用戶自定義函數(shù)User-Defined Function（UDF），并演示幾個(gè)常用的實(shí)例。

UDF的功能類似SQL Server中內(nèi)建的系統(tǒng)函數(shù)，如CONVERT、SUBSTRING、 DATAADD、 GETDATA、ISNULL等等。一個(gè)UDF可以沒有參數(shù)，或者帶有一個(gè)或多個(gè)參數(shù)，函數(shù)運(yùn)行后將會(huì)返回一個(gè)函數(shù)值。定義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   

每個(gè)UDF可以帶有0個(gè)到1024個(gè)參數(shù)，每個(gè)參數(shù)可以是除了timestamp、cursor、table 以外所有的數(shù)據(jù)類型；函數(shù)返回值的限制要更多一些，它不可以是text、ntext、image、timestamp、cursor和table。

函數(shù)體是UDF的主要部分，它有兩個(gè)選項(xiàng): ENCRYOTION和 SCHEMABINDING。

SCHEMABINDING是SQL Server 2000的新增功能，可以和視圖一同使用。該選項(xiàng)不允許刪除和修改被該函數(shù)引用的對象。這樣可以防止無效的函數(shù)和視圖對它們引用的對象進(jìn)行結(jié)構(gòu)上的修改。

大家會(huì)注意到函數(shù)體以Begin開始，End結(jié)束。這一點(diǎn)不同于創(chuàng)建存儲過程、觸發(fā)器和視圖。當(dāng)您忘了寫上Begin/End時(shí)，系統(tǒng)會(huì)返回一個(gè)提示信息“Incorrect syntax near ‘RETURN’”。為什么不直接說少了Begin/End，這有點(diǎn)讓人費(fèi)解。

下面我用幾個(gè)例子來說明UDF的應(yīng)用。

Greatest and Least  

為了區(qū)別于系統(tǒng)函數(shù)Max和Min，我給新函數(shù)命名為Greatest和Least，它們會(huì)從以參數(shù)形式輸入的兩個(gè)值中找出***值和最小值。

Case語句是兩個(gè)函數(shù)的核心：

CASE WHEN value1 > value2 THEN value1 ELSE value2 END

雖然函數(shù)很簡單，但用途是很廣的。

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   

大小寫轉(zhuǎn)換函數(shù)

該函數(shù)有兩個(gè)參數(shù)：@String和@Capitalize_What。

依據(jù) @Capitalize_What的值，函數(shù)有不同的功能：

¨ @Capitalize_What ＝ ‘string’“

函數(shù)將 @string的***個(gè)非空字符轉(zhuǎn)換成大寫， 其余部分改為小寫。

¨ @Capitalize_What ＝ ‘sentence’

函數(shù)將 @string中的每一句的***非空字符轉(zhuǎn)換為大寫，句子其余部分轉(zhuǎn)換為小寫。斷句的依據(jù)是’.’、’!’、’?’

¨ @Capitalize_What ＝ ‘word’

函數(shù)將 @string中的每個(gè)詞都轉(zhuǎn)換成首字符大寫，其余小寫的形式。

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   

小結(jié)

SQL Server 2000 的 UDF的應(yīng)用是很廣泛的，它會(huì)給編程人員帶來極大的便利。您可以建立自己的’system’ UDF，存在Master數(shù)據(jù)庫中，可以為任何數(shù)據(jù)庫進(jìn)行調(diào)用。

UDF也有不足，我們知道系統(tǒng)函數(shù)可以任意調(diào)有，不管您使用大寫、小寫或者大小寫混合。UDF卻不行，它是大小寫敏感的。

在未來的版本中，我希望微軟為UDF增加默認(rèn)值的功能，以后我們可以這樣定義一個(gè)函數(shù)。

CREAT FUNCTION dbo.Test_default  
（ @parm int = 0 ）  
RETURN INT  
AS  
BEGIN  
RETURN ( @parm )  
END  

UDF中諸如此類的小問題還有不少，希望UDF的功能越來越強(qiáng)大，我們編程人員工作起來就會(huì)越來越輕松。

【編輯推薦】

1. SQL Server數(shù)據(jù)庫在安裝時(shí)的注意事項(xiàng)
2. SQL Server 2005 Express混合模式登錄的設(shè)置
3. SQL Server 2005數(shù)據(jù)庫安裝實(shí)例演示
4. SQL Server 2000全文檢索的使用方案描述
5. SQL Server數(shù)據(jù)庫與identity列