UDF—MSQL Server 2000的新特性

　　
　　　　本文將介紹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的功能越來越強大我們編程人員工作起來就會越來越輕松

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22014.html