SQL语句签名的用户自定义函数

T-SQL函数实现

 1 IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULLDROP FUNCTION dbo.fn_SQLSigTSQL;GO
 2 CREATE FUNCTION dbo.fn_SQLSigTSQL
 3 (@p1 NTEXT, @parselength INT = 4000)
 4 RETURNS NVARCHAR(4000)
 5 
 6 
 7 --
 8 --该函数以“现状”提供且没有任何担保
 9 --同时也没有授予任何权利 
10 -- 使用该函数所包含的脚本示例受下列条款约束
11 -- http://www.microsoft.com/info/cpyright.htm
12 --
13 -- 分解查询字符串
14 AS
15 BEGIN
16 DECLARE @pos AS INT;
17 DECLARE @mode AS CHAR(10);
18 DECLARE @maxlength AS INT;
19 DECLARE @p2 AS NCHAR(4000);
20 DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);
21 DECLARE @p2len AS INT;
22 
23 
24 SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));
25 SET @maxlength = CASE WHEN @maxlength > @parselength
26 THEN @parselength ELSE @maxlength END;
27 
28 
29 SET @pos = 1;
30 SET @p2 = '';
31 SET @p2len = 0;
32 SET @currchar = '';
33 set @nextchar = '';
34 SET @mode = 'command';
35 
36 
37 WHILE (@pos <= @maxlength)
38 BEGIN
39 SET @currchar = SUBSTRING(@p1,@pos,1);
40 SET @nextchar = SUBSTRING(@p1,@pos+1,1);
41 IF @mode = 'command'
42 BEGIN
43 SET @p2 = LEFT(@p2,@p2len) + @currchar;
44 SET @p2len = @p2len + 1 ;
45 IF @currchar IN (',','(',' ','=','<','>','!')
46 AND @nextchar BETWEEN '0' AND '9'
47 BEGIN
48 SET @mode = 'number';
49 SET @p2 = LEFT(@p2,@p2len) + '#';
50 SET @p2len = @p2len + 1;
51 END
52 IF @currchar = ''''
53 BEGIN
54 SET @mode = 'literal';
55 SET @p2 = LEFT(@p2,@p2len) + '#''';
56 SET @p2len = @p2len + 2;
57 END
58 END
59 ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')
60 SET @mode= 'command';
61 ELSE IF @mode = 'literal' AND @currchar = ''''
62 SET @mode= 'command';
63 
64 
65 SET @pos = @pos + 1;
66 END
67 RETURN @p2;
68 END
69 GO 

示范：

1 print dbo.fn_SQLSigTSQL('select * from Customers t
2 where t.City='''+'LonDon'+''' and t.Country='''+'UK'+'''',4000)
3 --输出
4 /*
5 
6 select * from Customers t
7 where t.City='#' and t.Country='#'   
8 
9 */

 

CLR在处理迭代/过程逻辑和字符串处理时比T-SQL效率高，下面介绍用CLR实现模式化查询

CLR方式

a. 建立C#版的Classs Libary，函数如下：

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

public partial class SQLSignature
{
    // fn_SQLSigCLR
    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlString fn_SQLSigCLR(SqlString querystring)
    {
        return (SqlString)Regex.Replace(
            querystring.Value,
            @"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?#    expression coming
             )(?:([N])?(')(?:[^']|'')*('))(?#           character
             )|(?:0x[\da-fA-F]*)(?#                     binary
             )|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?#     precise number
             )(?:[eE]?[\d]*)))(?#                       imprecise number
             )|(?:[~]?[-+]?(?:[\d]+))(?#                integer
             ))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?#   operators
             ))",
            @"$1$2$3#$4");
    }
    // fn_RegexReplace - for generic use of RegEx-based replace
    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlString fn_RegexReplace(
        SqlString input, SqlString pattern, SqlString replacement)
    {
        return (SqlString)Regex.Replace(
            input.Value, pattern.Value, replacement.Value);
    }
}

b. 加载.dll中间语言代码到DB

USE master;
CREATE ASSEMBLY SQLSignature
FROM 'C:\SQLSignature\SQLSignature\bin\Debug\SQLSignature.dll';

 

c. 注册函数fn_SQLSigCLR和fn_RegexReplace

CREATE FUNCTION dbo.fn_SQLSigCLR(@querystring AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT 
EXTERNAL NAME SQLSignature.SQLSignature.fn_SQLSigCLR;
GO

CREATE FUNCTION dbo.fn_RegexReplace(
  @input       AS NVARCHAR(MAX),
  @pattern     AS NVARCHAR(MAX),
  @replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT 
EXTERNAL NAME SQLSignature.SQLSignature.fn_RegexReplace;
GO

 

d. 注册完成之后，用下面代码测试：

SELECT
  dbo.fn_SQLSigCLR(tsql_code) AS sig_sql,
  duration
FROM dbo.Workload;

 

结果的SQL全被模式化，井号(#)替代所有的参数。

 

 

转载于:https://www.cnblogs.com/worfdream/articles/3080203.html