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

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

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

正規表現の利用

　SQL CLRはこれまでに何度か触れたとおり、パフォーマンスなどの面からTransact-SQLをすべて置き換えられるわけではありませんが、Transact-SQLでは困難だった複雑な計算処理や文字列操作などでは大きく力を発揮します。そこで、今回は.NET Frameworkで提供される正規表現ライブラリの利用を確認してみましょう。

　正規表現とは文字列のパターンを表現する表記法です。文字列の検索や置換といったシーンで利用されています。ここでは正規表現による入力値チェックをSQL CLRで実装します。

　.NET FrameworkではRegularExpressions名前空間で、正規表現を利用するための各種クラスが提供されています。基本的な利用の流れとしては、Regexクラスへ正規表現パターンを格納し、Regexクラスで実行したパターンマッチングの結果をMatchクラスへ格納します。その詳細については＠IT記事「スマートな文字列処理のための正規表現入門（前編）」を確認していただくとして、早速サンプルの確認を行いましょう。次のサンプルでは、入力値に英文字以外が含まれる場合にエラーを返しています。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void SampleStored(string FirstName)
  {
    //正規表現パターンの定義
    Regex regex = new Regex("[^A-Za-z]");
        
    //パターンマッチング結果の格納
    Match m = regex.Match(FirstName);
    //マッチする文字列が含まれる場合、True
    if (m.Success)
    {
      SqlContext.Pipe.Send("不正な入力です！");
      return;
    }
    //コンテキスト接続
    using(SqlConnection conn =
          new SqlConnection("context connection=true"))
    {
      conn.Open();
      SqlCommand cmd = new SqlCommand(
        "SELECT ContactID, FirstName,LastName FROM Person.Contact" + 
        " Where FirstName=@p1", conn);
      SqlParameter p1 = new SqlParameter("@p1", FirstName);
      cmd.Parameters.Add(p1);
      //ExecuteAndSend：コマンドを実行し、結果を直接返す
      SqlContext.Pipe.ExecuteAndSend(cmd);
    }
  }
}

リスト5　正規表現を利用したサンプル

　上記コードにて作成したストアドプロシージャの呼び出しに英文字以外が含まれていると、次のようにエラーが返ります。

EXEC SampleStored 'Robert!'
不正な入力です！
(0 行が返されました)
EXEC SampleStored 'Phillip'
ContactID   FirstName    LastName
----------- ---------------------- 
47          Phillip      Bacalzo
19349       Phillip      Suri
～中略～
(23 行が返されました)

リスト6　リスト5の実行結果

　SQL CLRでの正規表現の利用を確認できました。SQL CLRでの利用といっても、C#で書く正規表現の利用法と違いはありません。C#やVB.NETで築いたノウハウをそのまま移行できることが分かります。

　今回のサンプルでは正規表現を取り上げましたが、.NET Frameworkには正規表現以外にもさまざまなライブラリが提供されています。Transact-SQLでは困難だった処理に対して、これらのライブラリ群を活用することでSQL CLRの本領が発揮されるといえるでしょう。

まとめ

　今回は、TVFに加えて、SQL CLRの花形ともいえる外部データベースへの接続、.NET Frameworkのライブラリ利用を取り上げました。従来、これらの処理を実装するためには個別にプログラムを作成する必要がありましたが、SQL CLRによってデータベース内でこれらの処理を行うことが可能となります。また、C#やVB.NETで作成した独自ライブラリなどのコード資産を生かし、生産性を高めることもできるでしょう。

　しかしながら、万能に見えるSQL CLRですがセキュリティ面での注意点に加え、過剰なSQL CLR化はデータベースの負荷増大につながります。また、従来型のTransact-SQLが優位性を保つ処理も存在するため、開発者にはそれぞれに適した使い分けが求められます。（次回に続く）