Heim >Datenbank >MySQL-Tutorial >用程序集编写clr表值函数:把正则表达式引入数据库中

用程序集编写clr表值函数:把正则表达式引入数据库中

WBOY
WBOYOriginal
2016-06-07 15:05:291068Durchsuche

正则表达式非常好,但在数据库中就是没有,但可以通过程序集方式扩展 先编写一个dll,标量函数很好写,表值函数麻烦一点 下面是C#代码 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServ

正则表达式非常好,但在数据库中就是没有,但可以通过程序集方式扩展

先编写一个dll,标量函数很好写,表值函数麻烦一点

下面是C#代码

<span>using</span><span> System;
</span><span>using</span><span> System.Data;
</span><span>using</span><span> System.Data.SqlClient;
</span><span>using</span><span> System.Data.SqlTypes;
</span><span>using</span><span> Microsoft.SqlServer.Server;
</span><span>using</span><span> System.Text.RegularExpressions;
</span><span>using</span><span> System.Collections;
</span><span>public</span> <span>partial</span> <span>class</span><span> RegExpFunctions
{</span>

    [SqlFunction(
      DataAccess = DataAccessKind.Read,
      FillRowMethodName = "MatchsFun_FillRow",
      TableDefinition = "pos int,match NVARCHAR(500)")]
    public static IEnumerable MatchsFun(string input, string patten)
    {
      MatchCollection mc;
      Regex r = new Regex(patten);
      mc = r.Matches(input);
      return mc;
    }


    public static void MatchsFun_FillRow(object mc,out int pos,out SqlString sqlmatch)
    {
      Match it = (Match)mc;
      pos = it.Index;
      sqlmatch = it.Value;
    }

};

程序集名称是RegulerExp2

代码中有几点解释一下:

(1)表值函数必须是IEnumerable,简单讲是必须有这个接口的类,MatchCollection就具有这个接口;

(2)必须提供一个回调函数,在函数属性FillRowMethodName = "MatchsFun_FillRow"中指明,这个函数负责填充数据,

    public static void MatchsFun_FillRow(object mc,out int pos,out SqlString sqlmatch)
    {
      Match it = (Match)mc;
      pos = it.Index;
      sqlmatch = it.Value;
    }

这里的object mc是什么呢?

我们可以想象一下遍历

foreach (Match it in mc)
{  
}

这里的object mc就是foreach里的Match it。

然后数据库把out int pos,out SqlString sqlmatch这两个量取出去放进表里。

 

下一步是添加程序集

第一步是把数据库的clr打开,不细说,自己网上查

第二步添加程序集

用程序集编写clr表值函数:把正则表达式引入数据库中

第三步,写一个数据库表值函数包装

<span>create</span> <span>FUNCTION</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>MatchList</span><span>]</span>(<span>@input</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>1000</span>), <span>@patten</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>1000</span><span>))
</span><span>RETURNS</span>  <span>TABLE</span><span> (
    pos </span><span>int</span>,<span>[</span><span>match</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>500</span>) <span>NULL</span><span>
) </span><span>WITH</span> <span>EXECUTE</span> <span>AS</span><span> CALLER
</span><span>AS</span><span> 
EXTERNAL NAME </span><span>[</span><span>RegulerExp2</span><span>]</span>.<span>[</span><span>RegExpFunctions</span><span>]</span>.<span>[</span><span>MatchsFun</span><span>]</span>

 

可以了。

前面写了一个小示例,大家好像没兴趣,写一个实用点的例子吧。数据库中没有split函数,用正则表达式就很容易了

<span>select</span> match <span>from</span> dbo.MatchList(<span>'</span><span>1,2,4,12,24,41</span><span>'</span>,<span>'</span><span>(?<span>'</span>)</span>

 

结果为

用程序集编写clr表值函数:把正则表达式引入数据库中

 

如果以一个存储过程方式

C#代码为

<span>[Microsoft.SqlServer.Server.SqlProcedure]
    </span><span>public</span> <span>static</span> <span>void</span> Matches(<span>string</span> input, <span>string</span><span> patten)
    {
        </span><span>//</span><span>像构造Table一样来构造SqlDataRecord,其中SqlMetaData类似DataColumn</span>
        SqlDataRecord dataRecord = <span>new</span> SqlDataRecord(<span>new</span><span> SqlMetaData[] {                
                </span><span>new</span> SqlMetaData(<span>"</span><span>ID</span><span>"</span><span>, SqlDbType.Int),
                </span><span>new</span> SqlMetaData(<span>"</span><span>index</span><span>"</span><span>, SqlDbType.Int),
                </span><span>new</span> SqlMetaData(<span>"</span><span>match</span><span>"</span>, SqlDbType.NVarChar,<span>100</span><span>)
                    });
        </span><span>//</span><span>开始填充</span>
<span>        SqlContext.Pipe.SendResultsStart(dataRecord);

        MatchCollection mc;
        Regex r </span>= <span>new</span><span> Regex(patten);
        mc </span>=<span> r.Matches(input);
        </span><span>for</span> (<span>int</span> i = <span>0</span>; i )
        {            
            <span>//</span><span>SqlDataRecord.SetString类似DataRow的功能,像Table中填充值</span>
            dataRecord.SetInt32(<span>0</span><span>, i);
            dataRecord.SetInt32(</span><span>1</span><span>, mc[i].Index);
            dataRecord.SetString(</span><span>2</span><span>,  mc[i].Value);
            </span><span>//</span><span>通过SendResultsRow把数据填充到Table,相关于Table.Rows.Add(DataRow);</span>
<span>            SqlContext.Pipe.SendResultsRow(dataRecord);
        }

        
        </span><span>//</span><span>填充结束,返回结果集</span>
<span>        SqlContext.Pipe.SendResultsEnd();
    }</span>

数据库端写一个存储过程包装

<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Macths</span><span>]</span>
    <span>@input</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>1000</span><span>),
    </span><span>@patten</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>1000</span><span>)
</span><span>WITH</span> <span>EXECUTE</span> <span>AS</span><span> CALLER
</span><span>AS</span><span>
EXTERNAL NAME </span><span>[</span><span>RegulerExp</span><span>]</span>.<span>[</span><span>RegulerExp</span><span>]</span>.<span>[</span><span>Matches</span><span>]</span>

别的一样

运行

<span>exec</span> dbo.Macths <span>'</span><span>1,2,4,12,24,41</span><span>'</span>,<span>'</span><span>(?<span>'</span></span>

 

结果为

用程序集编写clr表值函数:把正则表达式引入数据库中

 

其他标量函数很简单,自己百度,类似

 

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn