関数の種類
SQL Server 2008 では、ユーザー定義関数と組み込みのシステム関数がサポートされます。
スカラー関数
ユーザー定義のスカラー関数は、RETURNS 句で定義された型の単一のデータ値を返します。インライン スカラー関数の場合、スカラー値は単一ステートメントの結果であり、関数の本体がありません。複数ステートメントを持つスカラー関数の場合、BEGIN...END ブロックで定義された関数本体に、単一の値を返す一連の Transact-SQL ステートメントが含まれています。この関数の戻り値には、text、ntext、image、cursor、および timestamp 以外の任意のデータ型を指定できます。
次に、複数ステートメントのスカラー関数を作成する例を示します。この関数は、1 つの入力値 ProductID を受け取り、単一のデータ値 (在庫品目中の指定された製品に関する集計量) を返します。
USE AdventureWorks2008R2;
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
次に、ufnGetInventoryStock 関数を使用して、ProductModelID が 75 ～ 80 の製品の現在の在庫量を返す例を示します。
USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
テーブル値関数
ユーザー定義テーブル値関数は、table データ型を返します。インライン テーブル値関数の場合、テーブルは単一の SELECT ステートメントの結果セットであり、関数の本体がありません。
次に、インライン テーブル値関数を作成する例を示します。この関数は、入力パラメーターとして 1 つの顧客 (商店) ID を受け取り、ProductID 列と Name 列、および過去 1 年間の集計である YTD Total を商店に販売した製品ごとに返します。
USE AdventureWorks2008R2;
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 '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
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
次に、この関数を呼び出して顧客 ID 602 を指定する例を示します。
SELECT * FROM Sales.ufn_SalesByStore (602);
複数ステートメントを持つテーブル値関数の場合、BEGIN...END ブロックで定義される関数本体には、行を構築し、結果として返すテーブルに挿入する一連の Transact-SQL ステートメントが含まれています。
次に、テーブル値関数を作成する例を示します。この関数は、単一の入力パラメーター EmployeeID を受け取り、指定された従業員に直接または間接的に報告書を提出するすべての従業員の一覧を返します。関数が呼び出され、従業員 ID 109 を指定します。
USE AdventureWorks2008R2;
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,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
GO
組み込み関数
SQL Server には、さまざまな操作を実行しやすくするための組み込み関数が用意されています。組み込み関数は変更できません。Transact-SQL ステートメントで組み込み関数を使用すると、次のことが行えます。
SQL Server のシステム テーブルに直接アクセスすることなく、その情報にアクセスできます。詳細については、「システム関数の使用」を参照してください。
SUM、GETDATE、または IDENTITY などの一般的なタスクを実行できます。詳細については、「組み込み関数 (Transact-SQL)」を参照してください。
組み込み関数は、スカラーまたは table のいずれかのデータ型を返します。たとえば、@@ERROR は、最後に実行した Transact-SQL ステートメントが成功した場合、0 を返します。最後のステートメントでエラーが発生した場合、@@ERROR はエラー番号を返します。関数 SUM (parameter) は、指定したパラメーターのすべての値の合計を返します。