関数の種類
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
次に、複数ステートメントのスカラ関数を作成する例を示します。この関数は、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
テーブル値関数
ユーザー定義テーブル値関数は、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 に直接的または間接的に監督されるすべての従業員の一覧を返します。次に、この例では従業員 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) は、指定したパラメータのすべての値の合計を返します。
変更履歴
変更内容 |
|---|
インライン スカラ関数についての誤った内容を削除しました。 |