熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

UDF—MSQL Server 2000的新特性

2013-11-15 14:34:49  來源: SQL Server 

  
    本文將介紹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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.