sqlserver中的資料表值函式和純量值函式

標籤：

顧名思義：資料表值函式返回的是表，而純量值函式可以返回基底類型

一、資料表值函式

使用者定義資料表值函式返回 table 資料類型。對於內聯資料表值函式，沒有函數主體；表是單個 SELECT 語句的結果集。

以下樣本建立了一個內聯資料表值函式。此函數的輸入參數為客戶（商店）ID，而返回 ProductID、Name 以及 YTD Total（銷售到商店的每種產品的本年度節截止到現在的銷售總額）列。

 

USE AdventureWorks;GOCREATE FUNCTION Sales.fn_SalesByStore (@storeid int)RETURNS TABLEASRETURN (    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

以下樣本調用此函數並指定客戶 ID 為 602

SELECT * FROM Sales.fn_SalesByStore (602);

對於多語句資料表值函式，在 BEGIN...END 語句塊中定義的函數體包含一系列 Transact-SQL 陳述式，這些語句可產生行並將其插入將返回的表中。

以下樣本建立了一個資料表值函式。此函數具有一個輸入參數 EmployeeID 而返回直接或間接向指定員工報告的所有員工的列表。

USE AdventureWorks;GOCREATE FUNCTION dbo.fn_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.*/ASBEGIN   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    RETURNEND;GO

在以下樣本中，調用了此函數。

-- Example invocationSELECT EmployeeID, Name, Title, EmployeeLevelFROM dbo.fn_FindReports(109)ORDER BY Sort;

二、純量值函式

寫一個純量值函式

CREATE FUNCTION [dbo].[testGetSubNodes_]( @nodeId int)RETURNS intASBEGIN    declare @nodeCount int select @nodeCount=5 from MenuTree    return @nodeCountEND

這個函數很簡單返回一個整型值，然後就可以在預存程序中調用了，不過調用的方式有所不同，象上面的資料表值函式調用是不需要所有者的，只要寫函數名稱就可以，對於純量值函式來說，是需要加上所有者的，比如所有者是dboselect dbo.testGetSubNodes_,這樣就可以返回5，如果不加dbo，那sql會不認識這個函數。再來一個純量值函式：

CREATE FUNCTION FUN_DataFormat (@strDate datetime)  RETURNS varchar(20)  AS  BEGIN         declare @date varchar(20)      set @date = DATENAME(YY,@strDate)+‘年‘+Convert(VARCHAR,MONTH(@strDate))+‘月‘+Convert(VARCHAR,DAY(@strDate))+‘日‘    return @dateEND

可以通過select dbo.FUN_DataFormat(getdate())來使用。

 

sqlserver中的資料表值函式和純量值函式