ユーザー定義関数の基礎
プログラミング言語の関数と同様に、Microsoft SQL Server 2005 のユーザー定義関数は、パラメータを受け取って、複雑な計算などの操作を実行し、この操作の結果を値として返すルーチンです。戻り値は、単一のスカラ値または結果セットになります。
ユーザー定義関数の利点
次に SQL Server でユーザー定義関数を使用する利点を示します。
- モジュール プログラミングが可能になります。
関数を作成してからデータベースに保存すると、プログラムの中で何度でも呼び出せます。ユーザー定義関数は、プログラムのソース コードとは切り離して変更できます。 - 実行が高速になります。
Transact-SQL ユーザー定義関数を使用すると、ストアド プロシージャと同様に、プランがキャッシュされ、これを再利用して繰り返し実行することで、Transact-SQL コードのコンパイル コストを削減できます。つまり、ユーザー定義関数は、使用するたびに解析し直したり、最適化し直す必要がないので、実行時間が短縮されます。
計算や文字列の操作、ビジネス ロジックの場合は CLR 関数を使用することで、Transact-SQL 関数に比べてかなり高いパフォーマンスが得られます。Transact-SQL 関数は、データ アクセスの多いロジックに適しています。 - ネットワーク トラフィックが減少します。
1 つのスカラ式で表現できない複雑な制約に基づいてデータをフィルタ選択する操作を、1 つの関数として表現できます。このような関数を WHERE 句で使用すれば、クライアントに送信される数や行を削減できます。
ユーザー定義関数の構成要素
SQL Server 2005 では、Transact-SQL またはいずれかの .NET プログラミング言語を使って、ユーザー定義集計関数を記述できます。関数での .NET 言語の使用の詳細については、「CLR ユーザー定義関数」を参照してください。
すべてのユーザー定義関数は、ヘッダーと本体の 2 つの部分で構成されます。ユーザー定義関数は、0 個以上の入力パラメータを受け取り、スカラ値またはテーブルのいずれかを返します。
ヘッダーでは、次の事項を定義します。
- 関数名と、必要に応じてスキーマ名または所有者名
- 入力パラメータ名とデータ型
- 入力パラメータに適用可能なオプション
- 戻り値パラメータのデータ型と、必要であれば名前
- 戻り値パラメータに適用可能なオプション
本体では、関数が実行する操作 (ロジック) を定義します。次のいずれかを含めます。
- 関数のロジックを実行する 1 つ以上の Transact-SQL ステートメント
- .NET アセンブリへの参照
次の例では、単純な Transact-SQL ユーザー定義関数とその関数の主な構成要素を示しています。この関数では指定された日付が評価され、その日が何曜日かを示す値が返されます。
IF OBJECT_ID(N'dbo.GetWeekDay', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetWeekDay;
GO
CREATE FUNCTION dbo.GetWeekDay -- function name
(@Date datetime) -- input parameter name and data type
RETURNS int -- return parameter data type
AS
BEGIN -- begin body definition
RETURN DATEPART (weekday, @Date) -- action performed
END;
GO
次に、Transact-SQL ステートメントでのこの関数を使用する例を示します。
SELECT dbo.GetWeekDay(CONVERT(DATETIME,'20020201',101)) AS DayOfWeek;
GO
以下に結果セットを示します。
DayOfWeek
---------
6
(1 row(s) affected)