• [アーティクル]

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

ユーザー定義関数は、クエリや他のステートメント、または計算列や文字列式などの式で呼び出すことができます。スカラー値関数は、EXECUTE ステートメントを使用して実行できます。

スカラー値を返すユーザー定義関数の呼び出し

スカラー値を返すユーザー定義関数は、Transact-SQL ステートメントで同一データ型のスカラー式が使用できる任意の場所で呼び出すことができます。スカラー値関数は、2 つ以上の要素から構成される名前を使用して呼び出す必要があります。複数の要素で構成される名前の詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。

クエリ

スカラー値を返すユーザー定義関数は次の場合に使用できます。

  • SELECT ステートメントの select_list で expression として使用する場合。

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO

  • WHERE 句または HAVING 句の述語で expression または string_expression として使用する場合。

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO

  • GROUP BY 句で group_by_expression として使用する場合。

  • ORDER BY 句で order_by_expression として使用する場合。

  • 次のように、UPDATE ステートメントの SET 句で expression として使用する場合。

    USE AdventureWorks2008R2;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO

  • INSERT ステートメントの VALUES 句で expression として使用する場合。

    これらの場所で参照されるユーザー定義関数は、1 行ずつ論理的に実行されます。

CHECK 制約

スカラー値を返すユーザー定義関数は、その関数に渡される引数値によってそのテーブルのみに含まれている列か定数が参照される場合、CHECK 制約内で呼び出すことができます。クエリ プロセッサは、制約をチェックするたびに、現在チェック中の行に関連付けられた引数値を持つ関数を呼び出します。テーブルの所有者は、そのテーブルの CHECK 制約で呼び出されたユーザー定義関数の所有者でもある必要があります。

DEFAULT 定義

ユーザー定義関数は、その関数に渡される引数値に定数しか含まれていない場合、DEFAULT 定義の constant_expression として呼び出すことができます。テーブルの所有者は、そのテーブルの DEFAULT 定義で呼び出されたユーザー定義関数の所有者でもある必要があります。

計算列

関数に渡される引数値によって、テーブルに含まれている列か定数しか参照されない場合、計算列によって関数を呼び出すことができます。テーブルの所有者は、そのテーブルの計算列で呼び出されたユーザー定義関数の所有者でもある必要があります。

代入演算子

代入演算子 (left_operand = right_operand) では、ユーザー定義関数を呼び出し、右辺オペランドとして指定された式にスカラー値を返すことができます。

流れ制御ステートメント

スカラー値を返すユーザー定義関数は、そのブール式にある流れ制御ステートメントによって呼び出すことができます。

CASE 式

スカラー値を返すユーザー定義関数は、任意の CASE 式で呼び出すことができます。

文字列を返すユーザー定義関数は、PRINT ステートメントの string_expr 式として呼び出すことができます。

関数とストアド プロシージャ

  • スカラー値を返すユーザー定義関数への参照を関数の引数にすることも可能です。

  • ストアド プロシージャの RETURN integer_expression ステートメントによって、整数を integer_expression として返すユーザー定義関数を呼び出すことができます。

  • ユーザー定義関数内の RETURN return_type_spec ステートメントによって、return_type_spec などのスカラー データ型を返すユーザー定義関数を呼び出すことができます。ただしこの場合、呼び出されたユーザー定義関数から返される値が、呼び出し元の関数の戻りデータ型に暗黙に変換できる必要があります。

スカラー値を返すユーザー定義関数の実行

スカラー値を返すユーザー定義関数を実行する方法は、ストアド プロシージャを実行する方法と同じです。スカラー値を返すユーザー定義関数を実行する場合、パラメーターは次のようにストアド プロシージャの場合と同じように指定します。

  • 引数値はかっこで囲みません。

  • パラメーター名を指定できます。

  • パラメーター名を指定した場合、引数値はそれらのパラメーターと同じシーケンスに存在しなくてもかまいません。

次の例では、10 進数のスカラー値を返すユーザー定義関数を作成します。

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

次の例では、dbo.ufn_CubicVolume 関数を実行します。Transact-SQL の EXECUTE ステートメントを使用して、関数の定義におけるパラメーターの順序とは異なる順序で引数を識別します。

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

次の例では、パラメーター名を指定せずに dbo.ufn_CubicVolume 関数を実行します。

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

ODBC CALL 構文を使用して、OLE DB アプリケーションまたは ODBC アプリケーションから dbo.ufn_CubicVolume 関数を実行することもできます。

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

table データ型を返すユーザー定義関数の呼び出し

SELECT、INSERT、UPDATE、DELETE の各ステートメントの FROM 句でテーブル式を使用できる場合は、table 型を返すユーザー定義関数を呼び出すことができます。table 型を返すユーザー定義関数を呼び出すときに、必要に応じてテーブルの別名を指定できます。次の例は、SELECT ステートメントの FROM 句でテーブル値関数 dbo.ufnGetContactInformation を呼び出す方法を示しています。

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

table 型を返すユーザー定義関数をサブクエリの FROM 句で呼び出すと、その関数の引数では、外部クエリの列を参照できません。

table 型を返すユーザー定義関数を参照する FROM 句のある SELECT ステートメントで開くことのできるカーソルは、静的で読み取り専用のカーソルのみです。

table 型を返すユーザー定義関数を参照する SELECT ステートメントによってその関数が呼び出されるのは 1 回だけです。

組み込みのテーブル値関数の呼び出し

テーブル値を返す組み込みのテーブル値関数がいくつかあります。このような組み込みのユーザー定義関数の呼び出しは、修飾されていない呼び出しにするか、sys スキーマ修飾子を使用して行うことができます。sys スキーマ修飾子を使用すると、同じ名前のユーザー定義関数との競合が回避されるので、組み込みのテーブル値関数にはこのスキーマ修飾子を使用する必要があります。次の例は、システムの組み込み関数 fn_helpcollations を呼び出す方法を示しています。

SELECT *
FROM sys.fn_helpcollations();
GO

テーブル値関数でのヒントの使用

ユーザー定義関数を作成する場合、その関数の定義を構成するすべてのクエリにテーブル ヒントを適用できます。Transact-SQL テーブル値関数を参照するビューに適用されたヒントは、その関数にも適用されます。これらの関数は、関数の定義のヒントと競合する場合があります。詳細については、「ビューの解決」を参照してください。

CLR テーブル値関数を参照するビューにヒントを適用することはできません。

注意注

ビュー定義の一部である複数ステートメントのテーブル値関数にビューのヒントを適用するためのデータベース エンジンの機能は、今後のバージョンの SQL Server で廃止される予定です。

テーブル ヒントは、クエリの FROM 句のどのテーブル値関数の結果にも適用できません。