SQL server CLR集成
2008-02-22 11:31 Jaypei 阅读(376) 评论(0) 编辑 收藏 举报SQL Server中导入CLR DLL的方法:
启用SQL Server的CLR:exec sp_configure 'clr enable' , 1
reconfigure with override
reconfigure with override
然后使用CREATE ASSEMBLY 导入DLL:
CREATE ASSEMBLY SQLFunctionTEST
FROM 'd:\SQLfunctionTEST.dll'
WITH permission_set = Safe;
--SAFE | EXTERNAL_ACCESS | UNSAFE
FROM 'd:\SQLfunctionTEST.dll'
WITH permission_set = Safe;
--SAFE | EXTERNAL_ACCESS | UNSAFE
若使用UNSAFE和EXTERNAL_ACCESS则需要使用如下方法开启信任模式:
alter database 数据库名称
set TRUSTWORTHY ON
set TRUSTWORTHY ON
创建好Assembly后通过如下SQL语句查看:
select * from sys.assemblies
select * from sys.assembly_files
select * from sys.assembly_files
使用此方式注册一个SQL自定义函数:
create function Regex(@input nvarchar(max), @pattern nvarchar(max), @groupIndex int)
returns nvarchar(MAX)
as
External Name TestCLR.Utility.RegexFunction
returns nvarchar(MAX)
as
External Name TestCLR.Utility.RegexFunction
此函数的代码实现如下:
using System;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class Utility
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RegexFunction(SqlString input, SqlString pattern, SqlInt32 groupIndex)
{
return Regex.Match(input.Value, pattern.Value, RegexOptions.IgnoreCase).Groups[groupIndex.Value].Value;
}
};
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class Utility
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RegexFunction(SqlString input, SqlString pattern, SqlInt32 groupIndex)
{
return Regex.Match(input.Value, pattern.Value, RegexOptions.IgnoreCase).Groups[groupIndex.Value].Value;
}
};
调用此函数:
print dbo.Regex('ghlkjsdoajwdkajslkfjalkjfaow', 'js(.*)wdka.*', 1)
结果:doaj