連載
» 2006年07月15日 00時00分 公開

SQL CLRを極める3つのコーディング・テクニックSQL Server 2005を使いこなそう(6)(1/3 ページ)

5年ぶりのメジャーバージョンアップとなったSQL Server 2005。本連載では、SQL Server 2005への移行を検討しているデータベース管理者に向け、新規に実装されたさまざまな機能の詳細を紹介していく。(編集局)

[石橋潤一,株式会社システムインテグレータ]

主な内容

--Page 1--

SQL CLRでTVFを使うメリット

TVFプログラムのサンプル作成

--Page 2--

外部データベースへの接続

--Page 3--

正規表現の利用

まとめ


 前回の「SQL CLRコーディング、最初の一歩」では、SQL CLRに関する基本的なコーディングを解説しました。コーディングの容易さや、SQL Server 2005とVisual Studio 2005の綿密な連携による生産性の高さなど、多くの利点を感じ取ることができたはずです。

 今回は、テーブルを返すためのユーザー定義関数であるテーブル値関数(以下、TVF)の解説に加えて、外部データベースとの接続、正規表現の利用などを解説します。

SQL CLRでTVFを使うメリット

 TVFは、テーブルを返すことのできるユーザー定義関数です。前回解説したスカラ値関数(SVF)が単一の値を返す関数であったのに対して、TVFではテーブル構造のデータを返すことができます。

 Transact-SQLでもTVFを作成することができましたが、その内部動作はSQL CLRとは異なります。Transact-SQL TVFでは、結果が中間テーブルに保存され、参照する際はこの中間テーブルを利用します。一方、CLR TVFでは結果がストリーミングとして提供されます。中間テーブルを利用する場合、すべてのデータが中間テーブルにセットされるまでデータを利用することができませんが、ストリーミングモデルの場合、最初の行が利用可能となった時点で直ちに結果を参照できます。従って、膨大な量の行が返される場合はCLR TVFが適しているといえるでしょう。

 TVFは次のようなシーンでの利用が想定されます。

  • 入力引数からテーブルを作成する場合(カンマ区切りの文字列を受け取り、テーブルとして返すなど)
  • 外部データからテーブルを作成する場合(イベントログを読み取り、テーブルとして返すなど)

 上記以外にも、TVFはストアドプロシージャと異なりFROM句で参照できるメリットがあるため、処理結果をより柔軟に利用することが可能です。

TVFプログラムのサンプル作成

 それでは、早速TVFのサンプルを作成してみましょう。今回のサンプルはカンマ区切りで渡された文字列を分解してテーブル値で返します。サンプル内の接続データベースはSQL Server 2005のサンプルデータベースであるAdventure Worksを利用しています。

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    //FillRowMethodName:データを返すメソッドの名前を表す
    //TableDefinition  :結果のテーブル定義を表す
    //IEnumerable      :.NET Frameworkで配列およびコレクションを表す型
    [SqlFunction(FillRowMethodName = "FillRow",
                 TableDefinition = "addr NVARCHAR(50)")]
    public static IEnumerable SampleTVF(string str)
    {
        return str.Split(',');
    }
    public static void FillRow(object row, out string str)
    {
        str = (string)row;
    }
}
リスト1 TVFプログラムのサンプル

 コードをデバッグするため、Test.SQLへ下記ステートメントを記述します。

SELECT addr FROM dbo.SampleTVF('tokyo,saitama,tokyo,oosaka')
  GROUP BY addr
リスト2 Test.SQLにステートメントを記述

 実行した結果はリスト3のとおりです。引数に従って関数よりテーブルが返され、さらに呼び出し側でGROUP BY句によるグループ化が行われていることに注目してください。

addr
-----------
oosaka
saitama
tokyo
リスト3 リスト2の実行結果

 前回解説したストアドプロシージャやスカラ値関数に比べて若干癖のあるコーディングが必要となりますが、一度動きを確認すれば、それほど苦労せず使いこなすことができるはずです。(次ページへ続く)

       1|2|3 次のページへ

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。