ユーザー定義関数
適用対象:
SQL Server (サポートされているすべてのバージョン) Azure SQL Database
プログラミング言語の関数と同様に、SQL Serverユーザー定義関数は、パラメーターを受け入れ、複雑な計算などのアクションを実行し、そのアクションの結果を値として返すルーチンです。 戻り値は、単一のスカラー値または結果セットになります。
ユーザー定義関数
ユーザー定義関数 (UDF) を使用する理由とは?
モジュール プログラミングが可能になります。
関数を作成してからデータベースに保存すると、プログラムの中で何度でも呼び出せます。 ユーザー定義関数は、プログラムのソース コードとは切り離して変更できます。
実行が高速になります。
ストアド プロシージャと同様に、Transact SQL ユーザー定義関数は、プランをキャッシュし、繰り返し実行するために再利用することで、Transact SQL コードのコンパイル コストを削減します。 つまり、ユーザー定義関数は、使用するたびに解析し直したり、最適化し直す必要がないので、実行時間が短縮されます。
CLR 関数は、計算タスク、文字列操作、ビジネス ロジックに対する Transact SQL 関数よりも大きなパフォーマンス上の利点を提供します。 Transact-SQL 関数は、データ アクセス集中型ロジックに適しています。
ネットワーク トラフィックが減少します。
1 つのスカラー式で表現できない複雑な制約に基づいてデータをフィルター選択する操作を、1 つの関数として表現できます。 その後、WHERE 句でこの関数を呼び出して、クライアントに送信される行の数を減らすことができます。
重要
クエリ内の Transact-SQL UDF は、1 つのスレッド (シリアル実行プラン) でのみ実行できます。 そのため、UDF を使用すると、並列クエリ処理が禁止されます。 並列クエリ処理の詳細については、「クエリ処理アーキテクチャ ガイド」をご覧ください。
関数の種類
スカラー関数
ユーザー定義のスカラー関数は、RETURNS 句で定義された型の単一のデータ値を返します。 インライン スカラー関数の場合、返されるスカラー値は単一ステートメントの結果です。 マルチステートメント スカラー関数の場合、関数本体には、1 つの値を返す一連の Transact-SQL ステートメントを含めることができます。 戻り値の型は、 text、 ntext、 image、 cursor、 timestampを除く各種のデータ型になります。
使用例。
テーブル値関数
ユーザー定義テーブル値関数は、table データ型を返します。 インライン テーブル値関数の場合、テーブルは単一の SELECT ステートメントの結果セットであり、関数の本体がありません。 使用例。
システム関数
SQL Serverには、さまざまな操作を実行するために使用できる多数のシステム機能が用意されています。 システム関数は変更できません。 詳細については、「組み込み関数 (Transact-SQL)、システム ストアド関数 (Transact-SQL)、動的管理ビューと関数 (Transact-SQL)」を参照してください。
ガイドライン
ステートメントが取り消され、モジュール内の次のステートメント (トリガーやストアド プロシージャなど) を続行する Transact-SQL エラーは、関数内で扱い方が異なります。 関数内では、このようなエラーによって関数自体の実行が停止されます。 そのため、次に関数を呼び出したステートメントも取り消されることになります。
BEGIN...END ブロック内のステートメントは、副作用を伴いません。 関数の副作用とは、データベース テーブルの変更など、その関数の有効範囲外のリソースの状態を永続的に変更してしまうことです。 関数内のステートメントが変更できる内容は、ローカル カーソルまたはローカル変数など、その関数に対してローカルなオブジェクトの変更のみです。 データベース テーブルの変更、関数に対してローカルではないカーソルの操作、電子メールの送信、カタログ変更、ユーザーへ返す結果セットの生成などの操作は、関数では実行できません。
注意
ステートメントのCREATE FUNCTION発行時に存在しないリソースに対してステートメントが副作用をCREATE FUNCTION生じる場合は、ステートメントSQL Server実行されます。 ただし、SQL Serverは呼び出されたときに関数を実行しません。
クエリで指定した関数が実際に実行される回数は、オプティマイザーで作成された実行プランによって異なります。 WHERE 句のサブクエリによって起動された関数がその一例です。 サブクエリとその関数が実行された回数は、オプティマイザーが選択したアクセス パスの違いによって変わります。
重要
ユーザー定義関数の詳細とパフォーマンスに関する考慮事項については、「ユーザー定義関数の作成 (データベース エンジン)」を参照してください。
関数で有効なステートメント
関数では、次の種類のステートメントが有効です。
関数に対してローカルなデータ変数やカーソルを定義するために使用できる
DECLAREステートメント。関数に対してローカルなオブジェクトへの値の代入。たとえば、
SETを使用してスカラー変数やテーブルのローカル変数に値を代入します。ローカル カーソルを参照するカーソル操作。ローカル カーソルとは、関数内で宣言され、開かれ、閉じられ、割り当てが解除されるカーソルです。 クライアントにデータを返す
FETCHステートメントは使用できません。INTO句を使用してローカル変数に値を代入する FETCH ステートメントのみを使用できます。TRY...CATCHステートメントを除く、フロー制御ステートメント。選択リストに、関数に対してローカルな変数に値を代入する式が含まれる、
SELECTステートメント。関数に対してローカルなテーブル変数を変更する
UPDATE、INSERT、DELETEの各ステートメント。拡張ストアド プロシージャを呼び出す
EXECUTEステートメント。
組み込みシステム関数
Transact-SQL ユーザー定義関数では、次の非決定的な組み込み関数を使用できます。
CURRENT_TIMESTAMP
@@MAX_CONNECTIONS
GET_TRANSMISSION_STATUS
@@PACK_RECEIVED
GETDATE
@@PACK_SENT
GETUTCDATE
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE
@@IO_BUSY
Transact-SQL ユーザー定義関数では、次の非決定的な組み込み関数を使用できません。
NEWID
RAND
NEWSEQUENTIALID
TEXTPTR
決定的および非決定的な組み込みシステム関数の一覧については、「決定的関数と非決定的関数」を参照してください。
スキーマ バインド関数
CREATE FUNCTION は、SCHEMABINDING 句をサポートしています。この句は、テーブル、ビュー、およびその他のユーザー定義関数など、参照対象オブジェクトのスキーマにその関数をバインドします。 スキーマ バインド関数によって参照されるオブジェクトを変更または削除しようとすると、失敗します。
CREATE FUNCTION に SCHEMABINDING を指定するには、次の条件を満たしている必要があります。
CREATE 関数が参照するすべてのビューとユーザー定義関数が、スキーマにバインドされている必要があります。
この関数が参照するすべてのオブジェクトが、その関数と同じデータベースに含まれている必要があります。 対象となるオブジェクトは、1 つまたは 2 つの要素で構成される名前を使用して参照する必要があります。
関数内のすべての参照対象オブジェクト (テーブル、ビュー、ユーザー定義関数) に
REFERENCES権限を所持している必要があります。
ALTER FUNCTION を使用してスキーマ バインドを削除できます。 関数を再定義するには、ALTER FUNCTION ステートメントを使用します。WITH SCHEMABINDING は指定しないでください。
パラメーターの指定
ユーザー定義関数は、0 個またはそれ以上の入力パラメーターを受け取り、スカラー値またはテーブルのいずれかを返します。 1 つの関数では、最大で 1,024 個の入力パラメーターを受け取ることができます。 関数のパラメーターが既定値を持つ場合は、既定値を得るために、関数を呼び出すときに DEFAULT キーワードを指定する必要があります。 この動作はユーザー定義ストアド プロシージャ内の既定値を持つパラメーターとは異なります。ユーザー定義ストアド プロシージャの場合は、パラメーターを省略すると既定値が暗黙的に使用されます。 ユーザー定義関数では、出力パラメーターがサポートされません。
その他の例
| タスクの説明 | トピック |
|---|---|
| Transact-SQL ユーザー定義関数を作成する方法について説明します。 | ユーザー定義関数の作成 (データベース エンジン) |
| CLR 関数を作成する方法について説明します。 | CLR 関数の作成 |
| ユーザー定義集計関数を作成する方法について説明します。 | ユーザー定義集計の作成 |
| Transact-SQL ユーザー定義関数を変更する方法について説明します。 | ユーザー定義関数の変更 |
| ユーザー定義関数を削除する方法について説明します。 | ユーザー定義関数の削除 |
| ユーザー定義関数を実行する方法について説明します。 | ユーザー定義関数の実行 |
| ユーザー定義関数の名前を変更する方法について説明します。 | ユーザー定義関数名の変更 |
| ユーザー定義関数の定義を表示する方法について説明します。 | ユーザー定義関数の表示 |