[T-SQL] CLR統合を使用してCLRスカラ値関数を作成するステップバイステップ編

handcraft 6/6/2009 10111 N/A SQL Server

SQL Server 2005 から使用できる CLR 統合をスカラ関数を作成しながら、作成、デプロイ、削除までの流れを説明します。

動作確認 SQL Server 2005 Developer Edition, 開発は Visual Studio 2008 Professional で行っています。

Professional からは自動でSQL Server にアセンブリを登録できますが、Standard, Express Edition でも CLR アセンブリを作成することは可能です。その場合は、手動でアセンブリやCLR関数を登録する必要があります。その方法も記載します。

1. SQL Server プロジェクトを作成する

Visual Studio を起動して、 SQL Server プロジェクトテンプレートを指定して、プロジェクトを作成します。今回は、Handcraft.CLRSQLというプロジェクト名で新規に作成します。

ExpressEdtition など、SQL Server プロジェクトテンプレートが存在しない場合は、クラスライブラリプロジェクトを指定して、プロジェクトを新規作成します。

SQL Server プロジェクトテンプレートからプロジェクトを作成した場合は、データベース参照ダイアログが表示されるので、CLR統合アセンブリを使用するデータベースへの接続設定を行います。

新しいデータベース参照を設定した後、SQL/CLRデバッグを有効にするかの確認ダイアログが表示されます。デバッグを有効にする場合は、「はい」を選択します。

プロジェクト作成後、ソリューションエクスプローラ上でプロジェクトを右クリックし、クラスを作成します。ファイル名は、ClrExtentionとします。ClrExtention で、ufn_RegexMatch メソッドをpublic static として定義します。定義したスカラ値関数は、T-SQL で.NET の正規表現のマッチを行えるようにする関数です。

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace Handcraft.CLRSQL
{
    public class ClrExtention
    {
        /// <summary>
        /// .NET の正規表現と文字列が一致しているかを調べる
        /// CLR スカラ値関数
        /// </summary>
        /// <param name="str"></param>
        /// <param name="regex"></param>
        /// <returns></returns>
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
        public static SqlBoolean ufn_RegexMatch(SqlString str, SqlString regex)
        {
            if (str.IsNull || regex.IsNull) return SqlBoolean.Null;

            return Regex.IsMatch(str.Value, regex.Value, RegexOptions.CultureInvariant);
        }
    }
}

2. デプロイ

Professional 版以上で使用できる SQL ServerプロジェクトでCLR統合用のアセンブリを作成した場合と、クラスライブラリプロジェクトで作成した場合のデプロイ方法を記載します。

2.1 SQL Server プロジェクトで作成した場合

コンパイルができれば、プロジェクトを右クリックして、配置を選択すれば、デプロイを行ってくれます。

2.2 クラスライブラリプロジェクトで作成した場合

ExpressEdition などのSQL Server プロジェクトテンプレートが存在しない場合は、手動でデプロイする必要があります。次のようにManagement Studio からクエリを発行して登録します。dllのパスは環境に合わせて修正してください。CREATE ASSEMBLY でアセンブリを登録し、 CREATE FUNCTION で、アセンブリ名、名前空間、クラス、メソッドを使用して、CLRスカラ値関数をdbo.ufn_RegexMatch という関数で作成しています。

USE Component;
GO
-- アセンブリを登録
CREATE ASSEMBLY [Handcraft.CLRSQL]
FROM 'Z:\project\SQL\Handcraft.CLRSQL\Handcraft.CLRSQL\bin\Debug\Handcraft.CLRSQL.dll'
WITH PERMISSION_SET = SAFE;
GO

-- CLRスカラ値関数登録
CREATE FUNCTION dbo.ufn_RegexMatch(@str AS nvarchar(max), @regex AS nvarchar(max))
RETURNS BIT
AS
EXTERNAL NAME [Handcraft.CLRSQL].[Handcraft.CLRSQL.ClrExtention].[ufn_RegexMatch]
GO

2.3 手動でアセンブリを削除する場合

今回作成したCLR統合アセンブリを削除するには、アセンブリを削除する前に、関数をドロップしてから、アセンブリを削除します。

DROP FUNCTION dbo.ufn_RegexMatch
GO

DROP ASSEMBLY [Handcraft.CLRSQL]
GO

3 動作確認

登録完了後、Management Studio 上で、次のように呼び出すことができます。SQL Server のclr統合オプションが1となっていない場合は、CLR統合を有効にしておきます。

-- CLR統合を有効にしておく
USE master;
EXEC sp_configure 'clr enabled', 1
RECONFIGURE;

USE Component
GO;

-- アセンブリテスト
SELECT dbo.ufn_RegexMatch('PS3','[P]')
SELECT dbo.ufn_RegexMatch('PS3','[A]')

SQL Server プロジェクトテンプレート上からデバッグ実行を行う場合は、プロジェクト作成時に、既定で作成されるTestScriptsフォルダのTest.sql に上記SQL ステートメントを記述してF5ボタンを押すと、デバッグ実行を行えます。ソースプログラムにブレークポイントを設定しておくと、処理をその場所でとめることができます。

4 アセンブリ情報の確認

SQL Server に登録されているアセンブリ情報は、sys.

USE Component;
GO
-- アセンブリを構成するファイルごとに 1 行のデータを保持する
-- カタログビュー
select *
  from sys.assembly_files 

-- アセンブリごとに 1 行のデータを格納するカタログビュー
select *
  from sys.assemblies

5. まとめ

今回の説明は以上です。今回はステップバイステップで、CLR統合を使用する方法を記載しました。

誤り指摘点などがありましたらご連絡ください。

// Net planet-es V2