CodeZine(コードジン)

特集ページ一覧

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

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

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2006/09/25 00:00

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の位置
図1 「AdventureWorks」データベース内のUDFの位置

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

ユーザー定義関数の定義

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

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

 スカラUDFは、DML文(INSERTUPDATESELECTなど)または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'

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


  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

あなたにオススメ

著者プロフィール

  • Jeffrey Juday(Jeffrey Juday)

    Crowe Chizek and Company LLC(インディアナ州サウスベンド)の開発者で、BizTalk、ASP.NET、Sharepoint、SQL Serverを使用する統合ソリューションが専門。軍事、製造、ファイナンシャルサービス、自動車販売店管理コンサルティング、コンピュータセキュリ...

  • japan.internet.com(ジャパンインターネットコム)

    japan.internet.com は、1999年9月にオープンした、日本初のネットビジネス専門ニュースサイト。月間2億以上のページビューを誇る米国 Jupitermedia Corporation (Nasdaq: JUPM) のニュースサイト internet.com や EarthWeb.c...

バックナンバー

連載:japan.internet.com翻訳記事

もっと読む

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5