ユーザー定義関数の作成 (データベースエンジン)

2017-06-13

このトピックでは、Transact-SQL を使用して SQL Server でユーザー定義関数を作成する方法について説明します。

このトピックについて

作業を開始する準備:

制限事項と制約事項

セキュリティ
ユーザー定義関数を作成するには:

スカラー関数を作成する

テーブル値関数を作成する

開始する前に

制限事項と制約条件

ユーザー定義関数を使用して、データベースの状態を変更するアクションを実行することはできません。
ユーザー定義関数には、ターゲットとしてテーブルを含む OUTPUT INTO 句を含めることはできません。
ユーザー定義関数は、複数の結果セットを返せません。複数の結果セットを返す必要がある場合は、ストアドプロシージャを使用します。
エラー処理は、ユーザー定義関数で制限されます。 UDF は TRY をサポートしていません...CATCH、 @ERROR 、RAISERROR。
ユーザー定義関数はストアドプロシージャを呼び出すことはできませんが、拡張ストアドプロシージャを呼び出すことができます。
ユーザー定義関数では、動的 SQL テーブルまたは一時テーブルを使用できません。テーブル変数は使用できます。
SET ステートメントは、ユーザー定義関数では使用できません。
FOR XML 句は使用できません
ユーザー定義関数は入れ子にすることができます。つまり、あるユーザー定義関数が別の関数を呼び出すことができます。入れ子レベルは、呼び出された関数が実行を開始するとインクリメントされ、呼び出された関数の実行が完了するとデクリメントされます。ユーザー定義関数は、最大 32 レベルまで入れ子にすることができます。入れ子の最大レベルを超えると、呼び出し元関数チェーン全体が失敗します。 Transact-SQL ユーザー定義関数からのマネージドコードへの参照は、32 レベルの入れ子の制限に対して 1 つのレベルとしてカウントされます。マネージドコード内から呼び出されたメソッドは、この制限に対してカウントされません。
次の Service Broker ステートメントを、Transact-SQL ユーザー定義関数の定義に含めることはできません。
- ダイアログ会話を開始する
- 会話の終了
- 会話グループを取得する
- 会話の移動
- 受ける
- 送信

安全

権限

データベースの CREATE FUNCTION 権限と、関数が作成されているスキーマに対する ALTER 権限が必要です。関数がユーザー定義型を指定する場合は、その型に対する EXECUTE 権限が必要です。

スカラー関数

次の例では、AdventureWorks2012 データベースに複数ステートメントスカラー関数を作成します。この関数は、1 つの入力値 ( ProductID) を受け取り、1 つのデータ値 (インベントリ内の指定された製品の集計数量) を返します。

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 の製品の現在の在庫数量を返します。

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;

Table-Valued 関数

次の例では、AdventureWorks2012 データベースにインラインテーブル値関数を作成します。この関数は、1 つの入力パラメーター、顧客 (ストア) ID を受け取り、店舗に販売された各製品のYTD Totalとして、ProductID、Name、および年累計売上の集計を返します。

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  
);

次の例では、関数を呼び出し、顧客 ID 602 を指定します。

SELECT * FROM Sales.ufn_SalesByStore (602);

次の例では、AdventureWorks2012 データベースにテーブル値関数を作成します。この関数は、単一の入力パラメーター ( EmployeeID ) を受け取り、指定された従業員に直接または間接的に報告するすべての従業員の一覧を返します。その後、従業員 ID 109 を指定して関数が呼び出されます。

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);

こちらもご覧ください

ユーザー定義関数
 関数を作成 (Transact-SQL)