SQL Server の CLR 関数を作成する

SQL Server 2005 以降では、NET Framework CLR (共通言語ランタイム) で作成したファンクションのアセンブリを、データベース側に登録して、CLR 関数として使うことができます。

今回は、C# の System.Uri クラスを使って、URL などの URI を受け取って Host 名や Port 番号を返す CLR 関数を作ってみます。

1. dll ファイルを作成する

まず、Visual Studio でクラスライブラリのプロジェクトを作ります。 CLRUdfUri というプロジェクト名にしました。

次のような GetHost、GetPort という名前のメソッドを作りました。

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Server;

namespace CLRUdfUri
{
    public class CLRUdfUri
    {
        [SqlFunction]
        public static string GetHost(string uriString)
        {
            try
            {
                if (uriString.Equals(""))
                {
                    return "";
                }
                else
                {
                    Uri uri = new Uri(uriString);
                    return uri.Host;
                }
            }
            catch
            {
                return "";
            }
        }

        [SqlFunction]
        public static int GetPort(string uriString)
        {
            try
            {
                if (uriString.Equals(""))
                {
                    return -1;
                }
                else
                {
                    Uri uri = new Uri(uriString);
                    return uri.Port;
                }
            }
            catch
            {
                return -1;
            }
        }
    }
}

ポイントは各メソッドに Microsoft.SqlServer.Server 名前空間の SqlFunction 属性を追加している点です。

これをリリースモードでビルドして、 CLRUdfUri.dll を作っておきます。

2. SQL Server のアセンブリに登録する

以下のスクリプトを実行して、 CLRUdfUri.dll を SQL Server のアセンブリに登録します。

CREATE ASSEMBLY CLRUdfUri
FROM 'C:\Temp\CLRFunction\CLRUdfUri\bin\Release\CLRUdfUri.dll'
WITH PERMISSION_SET = SAFE;

一度登録されたアセンブリは、名前違いでも登録できません。また、CLR 関数で使用されている SQL Server のアセンブリは削除できません。

3. CLR ファンクションを作成する

以下のスクリプトを実行して、先ほど登録したアセンブリを使った ufnCLRUriHostGet と ufnCLRUriPortGet という名前の CLR 関数を作成します。

CREATE FUNCTION ufnCLRUriHostGet(
    @URIString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)   
AS EXTERNAL NAME CLRUdfUri.[CLRUdfUri.CLRUdfUri].GetHost;

CREATE FUNCTION ufnCLRUriPortGet(
    @URIString NVARCHAR(MAX)
)
RETURNS INT
AS EXTERNAL NAME CLRUdfUri.[CLRUdfUri.CLRUdfUri].GetPort;

EXTERNAL NAME の後は SQL Server 側のアセンブリ名.[ネームスペース名.クラス名].メソッド名です。

SQL Server 側のアセンブリ名、ネームスペース名、クラス名、を同じにしてしまったので、わかりにくくてすみません。。。

4. 「 clr enabled 」サーバー構成オプションを有効化する

以下のスクリプトを実行して、clr enabled オプションを有効化し、SQL Server でアセンブリを実行できるようにします。

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

このオプションが無効になっていると CLR 関数を使った時に「.NET Framework でのユーザーコードの実行は無効です。"clr enabled" 構成オプションを有効にしてください。」というエラーが出ます。

5. CLR 関数を使ってみる

それでは、次のスクリプトを実行して作った CLR 関数を使ってみます。

DECLARE @URIString NVARCHAR(MAX) = 'https://sql55.com/query/bulk-insert.php';

SELECT  dbo.ufnCLRUriHostGet(@URIString) AS Host,
        dbo.ufnCLRUriPortGet(@URIString) AS Port;

[ 実行結果 ]

System.Uri が返す Host と Port の値を取得することができました。

SQL Server の CLR 関数を作成する