ビューとストアドプロシージャの強力な代替手段「ユーザー定義関数」

SELECT文のFROM句中でビューやストアドプロシージャ的な処理を行う

SQL Serverには、あまり目立たないながら検討に値するビューとストアドプロシージャの代替手段として、テーブル値ユーザー定義関数があります。ビューにはない豊富なコーディング機能を備えていているほか、ストアドプロシージャとは異なり、SELECT文内の任意の場所で使用できる柔軟性を兼ね備えています。

はじめに

　ビューやストアドプロシージャを使用すれば、Transact-SQL（T-SQL）コードを適切にモジュール化したり、切り離したりすることができます。しかし、それ以上のことを望んだことはありませんか？ 例えば、SELECT文の中でパラメータドリブンのビューやストアドプロシージャを使えたらいいのに、と思ったことはないでしょうか。SQL Serverには、あまり目立たないながら検討に値するビューとストアドプロシージャの代替手段があります。その代替手段とは、テーブル値ユーザー定義関数（UDF）です。テーブル値UDFは、ビューとストアドプロシージャの重要な機能をすべて備えているだけでなく、ビューとストアドプロシージャにはない別の機能も備えています。

　例えば、私の開発チームでは、テーブル値UDFを使って、旧式のASPビジネスインテリジェンスアプリケーションに、新しいフィルタリングのレイヤを追加しました。SELECT文のFROM句内のテーブルの代わりにパラメータドリブンUDFを使用するだけで、核のT-SQLの大部分は変更せずに済みました。さらに、「検索と置換」を使用することで、ほとんどの変更を自動化することもできました。

　本稿では、SQL Server 2005に付属している「AdventureWorks」サンプルデータベースを修正したサンプルを使って、T-SQLコード内でテーブル値UDFを効果的に使用する方法について説明します。「AdventureWorks」データベースに格納されているUDFを確認するには、Management Studioを使って、図1に示すデータベース内の領域にナビゲートします。

図1　「AdventureWorks」データベース内のUDFの位置

　すべてのUDFは、ストアドプロシージャやビューと同様にT-SQLで記述され、ストアドプロシージャと同様にパラメータ値を持ちます。UDFを定義するには、CREATE FUNCTION文を使用します。

ユーザー定義関数の定義

　ユーザー定義関数には、次の2種類があります。

• スカラ値UDF
ほとんど場合、単一のデータ型を返すことができます。
• テーブル値UDF
複数の行から成る一時テーブルを返します。

　スカラUDFは、DML文（INSERT、UPDATE、SELECTなど）またはT-SQL文の内部で使用できます。テーブル値UDFにはいくつかの制限があり、一般にはSELECT文のFROM句で使用します。本稿では、テーブル値UDFの使用に焦点を当てます。

テーブル値UDFの概要

　テーブル値UDFには、次の2種類があります。

• インラインテーブル値関数
TABLEデータ型を返します。各関数は、1つのT-SQL文で構成されます。
• 複数ステートメントテーブル値関数
定義されたテーブルを返します。関数は、複数のT-SQL文で構成されます。

　どちらのテーブル値UDFも、単一の結果セットを返します。インラインテーブル値UDFの定義の例を以下に示します。

CREATE FUNCTION dbo.TestInlineFunctionName
(

)
RETURNS TABLE
AS
RETURN
(
   SELECT 0 as RetVal,* from [Person].[Contact]
)

　複数ステートメントUDFはインラインUDFに似ていますが、大きな違いが1つあります。それは、次のサンプルコードに示すように、RETURNSディレクティブの後にテーブル定義が続くことです。

RETURNS @retContactInformation TABLE
(
   -- Columns returned by the function
   [ContactID] int PRIMARY KEY NOT NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   [JobTitle] [nvarchar](50) NULL,
   [ContactType] [nvarchar](50) NULL
)

　複数ステートメントUDFには複数のSELECT文（他のT-SQL文も同様）を指定できるため、次のサンプルコードのようにして、返すテーブルに明示的にデータを割り当てる必要があります。

INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

　複数ステートメントテーブル値UDFを使用すると、返されるテーブルの内容を変更するといった処理を行うことができます。例えば、次のコードは、複数ステートメントUDFの中で完全に有効です。

UPDATE @retContactInformation SET [JobTitle] = 'None'

　テーブル値関数を理解できたところで、これをビューとストアドプロシージャに置き換えて使用する方法について説明しましょう。