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全被模式化,井号(#)替代所有的参数。


发布评论
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

maven命令package、install、deploy讲解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。