sql server使用者自訂函數

1. 返回標量資料的函數
使用者定義函數接受零個或更多的輸入參數，並返回單值。一些使用者定義函數返回單個的標量資料值，如 int、char 或 decimal 值。

例如，下面的語句建立一個返回 decimal 的簡單函數：

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

注意：和預存程序有三個地方不同：
１.參數必須寫在括弧裡面
２.參數後面有一條ｒｅｔｕｒｎ語句，　表示傳回值的類型
３.函數體內需要有ｒｅｔｕｒｎ，返回結果。

２.　如何調用函數

dbo.functionName(Parameter1,...)

然後可以在允許整型運算式的任何地方（如表的計算資料行中）使用該函數：

CREATE TABLE Bricks
   (
    BrickPartNmbr   int PRIMARY KEY,
    BrickColor      nchar(20),
    BrickHeight     decimal(4,1),
    BrickLength     decimal(4,1),
    BrickWidth      decimal(4,1),
    BrickVolume AS
              (
               dbo.CubicVolume(BrickHeight,
                         BrickLength, BrickWidth)
              )
   )

這裡的計算資料行還是第一次看到， 原來以為這個是更進階別的DataSet裡面的東西。

3. 返回表變數的函數
在返回 table 的使用者定義函數中：

RETURNS 子句為函數返回的表定義局部返回變數名。RETURNS 子句還定義表的格式。局部返回變數名的範圍位於函數內。

函數主體中的 Transact-SQL 陳述式產生行並將其插入 RETURNS 子句所定義的返回變數。

當執行 RETURN 語句時，插入變數的行以函數的表格格式輸出形式返回。RETURN 語句不能有參數。
函數中返回 table 的 Transact-SQL 陳述式不能直接將結果集返回使用者。函數返回使用者的唯一資訊是由該函數返回的 table。

下例在 Northwind 資料庫中建立一個返回 table 的函數：

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S INNER JOIN Orders AS O
              ON S.ShipperID = O.ShipVia
        WHERE O.Freight > @FreightParm
   RETURN
END

在這個函數中，返回的本地變數名是 @OrderShipperTab。函數主體中的語句將行插入變數 @OrderShipperTab，以產生該函數返回的表結果。

下面的查詢在其 FROM 子句中引用函數返回的表：

SELECT *
FROM LargeOrderShippers( $500 )

注意函數內不能使用暫存資料表， 但可以使用表變數
函數頭部return子句也是定義要返回的表變數， 但格式比較特殊。
在函數體內定義表變數的一般格式為：
declare @tableVar Table（
colName type，
。。。
）

暫存資料表和表變數的區別：

暫存資料表分為本地和全域兩種，本地暫存資料表的名稱都是以“#”為首碼，只有在本地當前的使用者串連中才是可見的，當使用者從執行個體中斷連線時被刪除。全域暫存資料表的名稱都是以“##”為首碼，建立後對任何使用者都是可見的，當所有引用該表的使用者中斷連線時被刪除。

表變數建立的文法類似於暫存資料表，區別就在於建立的時候，必須要為之命名。表變數是變數的一種，表變數也分為本地及全域的兩種，本地表變數的名稱都是以“@”為首碼，只有在本地當前的使用者串連中才可以訪問。全域的表變數的名稱都是以“@@”為首碼，一般都是系統的全域變數，像我們常用到的，如 @@Error代表錯誤的號，@@RowCount代表影響的行數。

　比較暫存資料表及表變數都可以通過SQL的選擇、插入、更新及刪除語句，它們的的不同主要體現在以下這些：

　　1)表變數是儲存在記憶體中的，當使用者在訪問表變數的時候，SQL Server是不產生日誌的，而在暫存資料表中是產生日誌的;

　　2)在表變數中，是不允許有非叢集索引的;

　　3)表變數是不允許有DEFAULT預設值，也不允許有約束;

　　4)暫存資料表上的統計資訊是健全而可靠的，但是表變數上的統計資訊是不可靠的;

　　5)暫存資料表中是有鎖的機制，而表變數中就沒有鎖的機制。

對於函數中不能支援暫存資料表是由於函數不能對函數範圍外部的資源狀態造成永久性的更改，在SQLServer中也稱為副作用 (sideeffect)。不過如果在函數中使用大型的臨時結果集是不推薦的，因為如果將這樣的函數放置到一個查詢中會造成很明顯的效能問題，因此這種情況一般都採用預存程序之類的批處理指令碼。

　　對於動態指令碼不支援表變數的原因是因為預存程序不接受表類型的參數。不過如果表變數的聲明和賦值都在sp_executesql的參數中的話， sp_executesql就可以執行了，因為這個時候表變數就存在sp_executesql的stmt參數裡面，不需要傳入，例如下面的代碼：(當然這樣的實用性也就沒有多少了)

　　DECLARE @m nvarchar(max)

　　SET @m = N"DECLARE @t TABLE (ID int);INSERT INTO @tVALUES(1);SELECT * FROM @t T"

　　EXEC sp_executesql @m