發行項
09/24/2010

函數類型

SQL Server 2008 同時支援使用者定義函數和內建系統函數。

純量函數

使用者定義的純量函數會傳回在 RETURNS 子句中所定義之類型的單一資料值。簡單的純量函數並沒有函數主體，純量值為單一函數陳述式 (通常是 SELECT 陳述式) 的結果。在多重陳述式純量函數中，函數主體會定義在 BEGIN...END 區塊中，而且包含了一系列傳回單一值的 Transact-SQL 陳述式。傳回類型可以是任何資料類型，但 text、ntext、image、cursor、spatial、hierarchyID 和 timestamp 除外。

下例範例會使用單一純量函數 ufnGetInventoryStock，傳回 ProductModelID 介於 75 和 80 之間的產品目前的存貨量。

USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

下列範例會建立多重陳述式純量函數。這個函數採用一個輸入值 ProductID，並傳回存貨中指定之產品的彙總數量當做單一傳回值。

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

資料表值函數

使用者定義的資料表值函數會傳回 table 資料類型。內嵌資料表數值函數並沒有函數主體；資料表為單一 SELECT 陳述式的結果集。

下例會建立一個內嵌資料表值函數。這個函數使用了一個輸入參數，也就是客戶 (商店) 識別碼，並傳回 ProductID 和 Name 資料行，以及從年初至今將每項產品銷售給商店的彙總銷售額 YTD Total。

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

下列範例會叫用這個函數並指定客戶識別碼 602。

SELECT * FROM Sales.ufn_SalesByStore (602);

在多重陳述式的資料表值函數中，BEGIN...END 區塊會定義函數主體，並包含可建立資料列並將資料列插入資料表結果的一系列 Transact-SQL 陳述式。

下列範例會建立一個資料表值函數。這個函數會採用單一輸入參數 EmployeeID，並傳回一份清單，列出直接或間接報告給指定之員工識別碼 109 的所有員工。然後此範例會使用員工識別碼 109 當做輸入參數，而且結果資料表中會傳回一份員工清單。

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

內建函數

為了協助您執行各種作業，SQL Server 提供了內建函數。這些函數不能修改。您可以在 Transact-SQL 陳述式中使用內建函數來：

從 SQL Server 系統資料表存取資訊，而不用直接存取系統資料表。如需詳細資訊，請參閱＜使用系統函數＞。
執行如 SUM、GETDATE 或 IDENTITY 等常見工作。如需詳細資訊，請參閱＜函數 (Transact-SQL)＞。

內建函數會傳回純量或 table 資料類型。例如，如果最後一個 Transact-SQL 陳述式執行成功，@@ERROR 會傳回 0。如果陳述式產生錯誤，則 @@ERROR 會傳回錯誤號碼。而函數 SUM(parameter) 則會傳回該參數所有值的總和。

變更記錄

更新的內容
已經移除有關嵌入純量函數的不正確內容。

Share via