関数の種類
SQL Server 2005 では、ユーザー定義関数と組み込みのシステム関数がサポートされます。
スカラ関数
ユーザー定義のスカラ関数は、RETURNS 句で定義された型の単一のデータ値を返します。インライン スカラ関数の場合、スカラ値は単一ステートメントの結果であり、関数の本体がありません。複数ステートメントを持つスカラ関数の場合、BEGIN...END ブロックで定義された関数本体に、単一の値を返す一連の Transact-SQL ステートメントが含まれています。この関数の戻り値には、text、ntext、image、cursor、および timestamp 以外の任意のデータ型を指定できます。
次に、複数ステートメントのスカラ関数を作成する例を示します。この関数は、1 つの入力値 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
次に、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
テーブル値関数
ユーザー定義テーブル値関数は、table データ型を返します。インライン テーブル値関数の場合、テーブルは単一の SELECT ステートメントの結果セットであり、関数の本体がありません。
次に、インライン テーブル値関数を作成する例を示します。この関数は、入力パラメータとして 1 つの顧客 (商店) ID を受け取り、ProductID 列と Name 列、および過去 1 年間の集計である 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
次に、この関数を呼び出して顧客 ID 602 を指定する例を示します。
SELECT * FROM Sales.ufn_SalesByStore (602);
複数ステートメントを持つテーブル値関数の場合、BEGIN...END ブロックで定義される関数本体には、行を構築し、結果として返すテーブルに挿入する一連の Transact-SQL ステートメントが含まれています。
次に、テーブル値関数を作成する例を示します。この関数は、単一の入力パラメータ EmployeeID を受け取り、指定された従業員に直接または間接的に報告書を提出するすべての従業員の一覧を返します。関数が呼び出され、従業員 ID 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 のいずれかのデータ型を返します。たとえば、@@ERROR は、最後に実行した Transact-SQL ステートメントが成功した場合、0 を返します。最後のステートメントでエラーが発生した場合、@@ERROR はエラー番号を返します。関数 SUM (parameter) は、指定したパラメータのすべての値の合計を返します。