SQL 2000使用者自訂函數

一。SQL Server 的三種自訂函數

“自訂函數”是我們平常的說法，而“使用者定義的函數”是 SQL Server 中書面的說法。

SQL Server 2000 允許使用者建立自訂函數，自訂函數可以有傳回值。

自訂函數分為：純量值函式或資料表值函式

• 如果 RETURNS 子句指定一種純量資料型別，則函數為純量值函式。可以使用多條 Transact-SQL 陳述式定義純量值函式。
• 如果 RETURNS 子句指定 TABLE，則函數為資料表值函式。

資料表值函式又可分為：內嵌資料表值函式（行內函數）或多語句函數

• 如果 RETURNS 子句指定的 TABLE 不附帶列的列表，則該函數為內嵌資料表值函式。
• 如果 RETURNS 子句指定的 TABLE 類型帶有列及其資料類型，則該函數是多語句資料表值函式。

純量值函式樣本

CREATE FUNCTION dbo.Foo()
RETURNS int
AS 
BEGIN
    declare @n int
    select @n=3
    return @n
END

create function GetSex(@sex int)
returns varchar(2)-- 用nvarchar(1)
as                    --  nvarchar:不管是一個字元還是一個漢字,都存為2個位元組
begin                 -- varchar:漢字是2個位元組,其他字元存為1個位元組
declare @ssex varchar(2)-- 用nvarchar(1)
 if(@sex=1)
    set @ssex='男'
  else
    set @ssex='女'
return (@ssex)
end
調用：
select id,username,dbo.GetSex(Sex) as sex ,loginnum from t_user

內嵌資料表值函式樣本

CREATE FUNCTION dbo.Foo()
RETURNS TABLE
AS 
    return select id, title from msgs

內嵌資料表值函式只有一個 select 語句。

多語句資料表值函式樣本（部分）

CREATE FUNCTION FindUsers (@userid int )
RETURNS @FindUser TABLE (id int primary key, UserName varchar(30), RegDateTime DateTime,LoginNum int)
as
begin
insert into @FindUser Select ID, UserName,RegDateTime,LoginNum From T_User Where ID=@userid
return
end 
調用：
SELECT * FROM FindUsers(6)

注意其 RETURNS 部分。

多語句函數的主體中允許使用以下語句。未在下面的列表中列出的語句不能用在函數主體中。

• 指派陳述式。
• 控制流程語句。
• DECLARE 語句，該語句定義函數局部的資料變數和遊標。
• SELECT 語句，該語句包含帶有運算式的挑選清單，其中的運算式將值賦予函數的局部變數。
• 遊標操作，該操作引用在函數中聲明、開啟、關閉和釋放的局部遊標。只允許使用以 INTO 子句向局部變數賦值的 FETCH 語句；不允許使用將資料返回到用戶端的 FETCH 語句。
• INSERT、UPDATE 和 DELETE 語句，這些語句修改函數的局部 table 變數。
• EXECUTE 語句調用擴充預存程序。

二.如何調用自訂函數

 

調用自訂函數（使用者定義的函數）和調用內建函數方式基本相同，

• 當調用純量值函式時，必須加上“所有者”，通常是 dbo（但不是絕對，可以在企業管理器中的“使用者定義的函數”中查看所有者）。
• 當調用資料表值函式時，可以只使用函數名稱。

樣本

SELECT *
select dbo.Sta(cnt) from tbl

樣本

SELECT *
FROM fn_MyTableFunc( 123.09, N'O''Neill' )三。預存程序和自訂函數的區別

預存程序（使用者定義的預存程序）和自訂函數（使用者定義的函數）的區別，在網上已經有討論過，但有些顯得過於零散，有些又篇幅過長不能保證每個人都細心閱讀。這裡作一個歸納，當然只是應用程式層面的，可能不全面。

• 預存程序，功能強大，可以執行包括修改表等一系列資料庫操作，也可以建立為 SQL Server 啟動時自動啟動並執行預存程序。
• 自訂函數，使用者定義函數不能用於執行一組修改全域資料庫狀態的操作。

 

• 預存程序，可以使用非確定函數。
• 自訂函數，不允許在使用者定義函數主體中內建非確定函數。

 

• 預存程序，可返回記錄集。
• 自訂函數，可以返回表變數。

 

• 預存程序，其傳回值不能被直接引用。
• 自訂函數，其傳回值可以被直接引用。

 

• 預存程序，用 EXECUTE 語句執行。
• 自訂函數，在查詢語句中調用。

來之：http://www.cftea.com/c/805.asp