複数のSQL文や制御命令で構成された複雑な処理命令を、１つの関数として定義したものです。ストアドプロシージャと似た働きをしますが、最終的に独自に定義したテーブルを返せるため、複雑なビューを関数に置き換えることもできます。
- 複数ステートメントのテーブル値関数の書式 -
複数ステートメントのテーブル値関数は、以下のような書式になります。
Create Function <関数名> (@<引数名> As <データ型>)
Returns @<テーブル名> TABLE (<フィールド定義>)
As
Begin
:(処理)
Return
End
<関数名>で関数の名前を定義し、続いて（必要なら）“@<引数名> As <データ型>”で引数名とそのデータ型を明らかにします。引数や変数には、名前の先頭に@を付けます。
<テーブル名>は、関数の戻り値となるテーブルの名前です。実際にはデータセットなので、ここで示した名前のテーブルがデータベース上に生成される訳ではありません。あくまで一時的なテーブルです。テーブル名の前には@を付けます。
<フィールド定義>では、戻り値とするテーブルのフィールド構成（フィールド名とデータ型の組）を「，」で区切って列挙します。
Begin～Endの間に、関数本体の処理を記述します。ローカル変数はDeclare命令で宣言できます。この場合も、変数名の先頭に@を付けます。処理の中ではコメント記号“--”が使えます。
最後のReturn命令では、自動的にReturnsで定義したテーブルがデータセットとして返されます。
- テーブルを関連付ける -
３つのテーブル「商品_mr」「仕入先_mr」「在庫_mr」を関連付け、各商品の仕入先と在庫数を表示する処理を作ってみましょう。その際、在庫数が引数で指定した数量以下の商品だけを抽出するようにします(リスト6)。
リスト6：指定した在庫数以下の商品を抽出するテーブル値関数“ViewStocksLess”(ex09.sql)
Create Function ViewStocksLess (@Value int)
Returns @Stocks TABLE -- テーブル（戻り値）の定義
(
商品ID int,
品名 nvarchar(30),
仕入単価 money,
仕入先ID int,
仕入先 nvarchar(30),
在庫 int
)
As
Begin
INSERT @Stocks -- テーブルにレコードを追加する
SELECT 商品_mr.商品ID, 商品_mr.品名,
商品_mr.仕入単価, 商品_mr.仕入先ID,
仕入先_mr.仕入先名,
在庫_mr.在庫
FROM 在庫_mr INNER JOIN -- JOINで関連付け
商品_mr ON 在庫_mr.商品ID = 商品_mr.商品ID INNER JOIN
仕入先_mr ON 商品_mr.仕入先ID = 仕入先_mr.仕入先ID
WHERE 在庫 <= @Value -- 引数と在庫数を比較する条件式
Return
End
実行するには、先ほどと同じようにSELECT命令のFROM句に指定します。在庫数が「10以下」の商品を調べるなら、以下のようなSQLを記述します(ex10.sql)。
SELECT * FROM ViewStocksLess(10)
実行結果は画面6のようになります。
- 戻り値をさらに加工できる -
テーブル値関数の返すテーブルは一般のテーブルと同じように扱えるため、表示するフィールドを限定したり、レコードをさらに絞り込んだり並べ替えたりできます。例えば、
在庫数が10以下のレコードを
「在庫」フィールドの値の昇順に並べ替え
「商品ID、品名、在庫」フィールドだけを表示
させたいなら、以下のようなSQLを記述します(ex11.sql)。
SELECT 商品ID, 品名, 在庫
FROM ViewStocksLess(10)
ORDER BY 在庫
実行結果は画面7のようになります。
ユーザー定義関数の種類、作り方、使い方を紹介してきました。
ユーザー定義関数は、機能の面から見ればストアドプロシージャやビューと似ています。そのため、大抵の処理は上記２つの一般的なオブジェクトで置き換えることが可能です。特にテーブル値関数は、内部で複雑な制御構造を作っていたり特殊な関数を呼び出していた場合に、データベースがバージョンアップしてTransact-SQLの仕様が変更されると、書き換えの手間がかかることもあります。
その点ビューは仕組みが単純なため、ビューで置き換えられるならビューを使った方が安全でしょう。その場合は、ビューを呼び出すアプリケーションの側で複雑な制御構造を作らなければならなくなるかもしれません。
ある複雑な処理をアプリケーションで担うか、ストアドプロシージャやビューを使うか、それともユーザー定義関数にするのか……は、非常に迷うところです。この点については回を追って考えてみたいと思います。
 サンプルファイル (LZH形式
1.66KB)
 |
|
|
