原文: SQL Server 2005------函數

SQL Server 2005支持用戶自定義函數和內置系統函數，根據返回值類型又分為標量函數和表值函數。

1.標量函數
標量函數：返回單個數據值，返回類型可以是除 text、ntext、image、cursor 和 timestamp 外的任何數據類型。
標量函數：分為內聯標量函數和多語句標量函數。
內聯標量函數：沒有函數體，標量值是單個語句的結果。
多語句標量函數：定義在 BEGIN...END 塊中的函數體包含一系列返回單個值的 Transact-SQL 語句。
多語句標量函數范例：
????? CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
????? RETURNS int
????? AS
????? 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;

2.標值函數
表值函數：返回 table 數據類型，分為內聯表值函數和多語句表值函數。
內聯表值函數：沒有函數主體。
區別：如果 RETURNS 子句指定的 TABLE 不附帶列的列表，則該函數為內嵌表值函數。
????? 如果 RETURNS 子句指定的 TABLE 類型帶有列及其數據類型，則該函數是多語句表值函數。
內聯表值函數范例：
????? 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
????????? );
多語句表值函數：在 BEGIN...END 語句塊中定義的函數體包含一系列 Transact-SQL 語句，這些語句可生成行并將其插入將返回的表中。
多語句表值函數范例：
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;

多語句表值函數中只允許使用下面的語句類型：
???? 賦值語句。
???? 控制流語句。
???? DECLARE 語句，該語句定義函數局部的數據變量和游標。
???? SELECT 語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦予函數的局部變量。
???? 游標操作，該操作引用在函數中聲明、打開、關閉和釋放的局部游標。只允許使用以 INTO 子句向局部變量賦值的 FETCH 語句；不允許使用將數據返回到客戶端的 FETCH 語句。
???? INSERT、UPDATE 和 DELETE 語句，這些語句修改函數的局部 table 變量。
???? EXECUTE 語句調用擴展存儲過程。

3.內置函數
?? SQL Server 提供了內置函數幫助您執行各種操作。這些函數不能修改。可以在 Transact-SQL 語句中使用內置函數，完成以下操作：
????? 從 SQL Server 系統表中訪問信息而不直接訪問系統表。有關詳細信息，請參閱使用系統函數。
????? 執行常見任務，例如 SUM、GETDATE 或 IDENTITY。有關詳細信息，請參閱Functions (Transact-SQL)。
?? 內置函數返回標量數據類型或 table 數據類型。例如，如果成功執行了最后一條 Transact-SQL 語句，@@ERROR 將返回 0。如果該語句生成錯誤，則 @@ERROR 將返回錯誤號。而函數 SUM(parameter) 將返回參數的所有值的和。?????

4.函數與存儲過程的區別
??? 函數不能用于執行一系列改變物理數據庫狀態的操作。BEGIN...END 塊中的語句不能有任何副作用。函數副作用是指對具有函數外作用域（例如數據庫表的修改）的資源狀態的任何永久性更改。函數中的語句唯一能做的更改是對函數上的局部對象（如局部游標或局部變量）的更改。不能在函數中執行的操作包括：對數據庫表的修改，對不在函數上的局部游標進行操作，發送電子郵件，嘗試修改目錄，以及生成返回至用戶的結果集。

5.用戶定義函數中不允許使用會對每個調用返回不同數據的內置函數，用戶定義函數中不允許使用以下內置函數：
???????? @@CONNECTIONS @@PACK_SENT GETDATE
???????? @@CPU_BUSY @@PACKET_ERRORS GetUTCDate
???????? @@IDLE @@TIMETICKS NEWID
???????? @@IO_BUSY @@TOTAL_ERRORS RAND
???????? @@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
???????? @@PACK_RECEIVED @@TOTAL_WRITE

SQL Server 2005------函數