VFP 愛用者社區 首頁 VFP 愛用者社區
本討論區為 Visual Foxpro 愛用者經驗交流的地方, 請多多利用"搜尋"的功能, 先查看看有無前例可循, 如果還有不懂的再發問. 部份主題有附加檔案, 須先註冊成為社區居民才可以下載.
 
 常見問題常見問題   搜尋搜尋   會員列表會員列表   會員群組會員群組   會員註冊會員註冊 
 個人資料個人資料   登入檢查您的私人訊息登入檢查您的私人訊息   登入登入 

[推薦]UDF—SQL Server 2000的新特性

 
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> SQL 討論區
上一篇主題 :: 下一篇主題  
發表人 內容
Ruey



註冊時間: 2003-03-12
文章: 1698
來自: tunglo

第 1 樓

發表發表於: 星期六 二月 14, 2004 1:06 pm    文章主題: [推薦]UDF—SQL 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 < 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 (Cool = ’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的功能越來越強大,我們編程人員工作起來就會越來越輕鬆。

_________________
#############################
快樂媽咪系列幸福宅配,喝十全雞湯~原來幸福那麼簡單!!

學會VFP使用者社區的搜尋,Code才會更有趣~
#############################
回頂端
檢視會員個人資料 發送私人訊息
summer



註冊時間: 2003-06-19
文章: 32


第 2 樓

發表發表於: 星期一 三月 22, 2004 9:37 pm    文章主題: 引言回覆

對不起!可否打岔,我想這是純技討,不是修正 Ruey 兄您的發表...
有關 Ruey 提及『....;函數返回值的限制要更多一些,它不可以是text、ntext、image、timestamp、cursor和table。 』這個部份,應該可以剔除最後的兩項,跟據在下的使用經驗,這兩項是包含在『純量』回傳值裡的,是可被回傳的....

以下是引述微軟MSDN的說明及實際範例...
scalar_parameter_data_type
 為參數的資料型別。所有純量資料型別,包括 bigint 和 sql_variant,都可以作為使用者定義函數的參數。 不支援 timestamp及使用者自訂的資料型別。非純量型別 (如資料指標和資料表) 都不能被指定。

scalar_return_data_type
 為純量使用者自訂函數的傳回值。scalar_return_data_type 可以是 SQL Server 所支援的任何純量資料型別,text、ntext、image 和 rowversion (timestamp) 等除外。

飯粒:
USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)

Ruey 兄,若有唐突之處,尚請包涵

Summe
回頂端
檢視會員個人資料 發送私人訊息
從之前的文章開始顯示:   
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> SQL 討論區 所有的時間均為 台北時間 (GMT + 8 小時)
1頁(共1頁)

 
前往:  
無法 在這個版面發表文章
無法 在這個版面回覆文章
無法 在這個版面編輯文章
無法 在這個版面刪除文章
無法 在這個版面進行投票
無法 在這個版面附加檔案
無法 在這個版面下載檔案


Powered by phpBB © 2001, 2005 phpBB Group
正體中文語系由 phpbb-tw 維護製作