SQL CLR関数のお勉強の出発点

SQL CLR: 気長に日記が良さそうです。3種類のSQL CLR関数についてのMSDNへのリンクが網羅されているので、出発点として良いでしょう。ただし、自分でMSDNを読まない人には価値のない情報になってしまいます。

Express EditionでSQL CLR関数を作るのは確かに面倒ですが、やってやれないことはありません。古のMS-DOSバッチファイルことWindowsNTコマンドスクリプトでSQL CLR関数の入れ替え・実行を行うクエリを動的生成すれば、ビルド→再登録→動作テストまで自動で行うことは可能です。ただし、SQL CLR関数のステップ実行は出来ませんので、納期に追われることが無いホビープログラミング向けでしょう。

では、具体的な方法は？

一番簡単な、データアクセス無しのスカラ関数で説明します。"Hallo, World!"ではやる気が出ないので、正規表現を用いた文字列置換を行う関数を取り上げます。

肝心のSQL CLR関数のソース(C#)

こんな感じ。

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

public class T
{
    [SqlFunction(DataAccess=DataAccessKind.None)]
    public static SqlString RegExpReplace(SqlString input, SqlString pattern, SqlString replacement)
    {
        return new SqlString(Regex.Replace(input.ToString(), pattern.ToString(), replacement.ToString()));
    }
}

アセンブリ＆SQL CLR関数を再登録してターゲットを自動実行するクエリ

こんな感じ。

USE [gsDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RegExpReplace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[RegExpReplace]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SQLFunctions')
DROP ASSEMBLY [SQLFunctions]
GO

CREATE ASSEMBLY [SQLFunctions]
FROM 'C:\Projects\trunk\どこかのプロジェクト\SQLFunction\bin\Debug\SQLFunctions.dll'
GO

CREATE FUNCTION [dbo].[RegExpReplace](@input [nvarchar](max), @pattern [nvarchar](max), @replacement [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLFunctions].[T].[RegExpReplace]
GO

SELECT dbo.RegExpReplace('123.456円', '[^0-9\.]', '')
GO

アセンブリの場所が変わってもいいように上記のクエリを自動生成

WindowsNTコマンドスクリプトの出番です。

@echo off
type "%1registAssembly1.sql">"%1registAssembly.sql"
echo FROM '%2'>>"%1registAssembly.sql"
type "%1registAssembly2.sql">>"%1registAssembly.sql"
sqlcmd -S localhost\SQLEXPRESS -E -i "%1registAssembly.sql"

先のクエリは前半分と後ろ半分にぶった切っておきます。

USE [gsDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RegExpReplace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[RegExpReplace]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SQLFunctions')
DROP ASSEMBLY [SQLFunctions]
GO

CREATE ASSEMBLY [SQLFunctions]

GO

CREATE FUNCTION [dbo].[RegExpReplace](@input [nvarchar](max), @pattern [nvarchar](max), @replacement [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLFunctions].[T].[RegExpReplace]
GO

SELECT dbo.RegExpReplace('123.456円', '[^0-9\.]', '')
GO

お土産は？

ソースのお持ち帰りはこちらからどーぞ→Yahoo!フォトアルバム、Yahoo!フォト、Yahoo!ブリーフケース、Yahoo!ノートパッドをご利用していたお客様へ - ストレージならYahoo!ボックス