SQL Server游标生成工具

数据库 SQL Server 数据库运维
经常做ERP报表,涉及存储过程中读取数据,多个表之间关联的数据读取,用到游标。经典的读取用户表的游标例子
  1. Declare @Age int 
  2. Declare @Name varchar(20) 
  3. Declare Cur Cursor For Select Age,Name From T_User    
  4. Open Cur 
  5. Fetch next From Cur Into @Age,@Name 
  6. While @@fetch_status=0      
  7. Begin 
  8.    Update T_User Set [Name]=@Name,Age=@Age 
  9.    Fetch Next From Cur Into @Age,@Name 
  10. End    
  11. Close Cur    
  12. Deallocate Cur 

在实际应用时,经常需要找到这个模板,然后再根据实际的表结果,重写一遍。经常遇到以下二个问题

1  上面的例子脚本不知道放在哪里了,或是有很多例子脚本,不方便很快找出来

2  重写游标的例子,经常重复,又没有技术难度可言。比如读取工作单生产计划,读取用户。

经过思考,于是写个游标生成工具,把上面的模板代码,应用到代码生成器中。

image

注意上图中的Script Cursor,这是用来生成游标模板的。选择一个数据库,树左边选择表名,勾选字段值,点击执行

  1. DECLARE  @UserID  NVARCHAR(10)  
  2.  DECLARE  @UserName  NVARCHAR(50)  
  3.  DECLARE  Cur  CURSOR  FOR  SELECT  [UserID],[UserName]  FROM  [USER]    
  4.  OPEN  Cur  
  5.  FETCH  next  FROM  Cur  INTO  @UserID,@UserName    
  6.  WHILE  @@fetch_status=0          
  7.  BEGIN  
  8.      FETCH  next  FROM  Cur  INTO  @UserID,@UserName    
  9.  END        
  10.  CLOSE  Cur        
  11.  DEALLOCATE  Cur  

源代码不到50行,全文如下

  1.  List<ColumnInfo>  fieldlist = this.GetFieldlist(); 
  2.  StringBuilder builder=new StringBuilder(); 
  3.  string typeName = string.Empty; 
  4.             
  5.  foreach (ColumnInfo columnInfo in fieldlist) 
  6.     { 
  7.                 switch (columnInfo.TypeName) 
  8.                 { 
  9.                     case "datetime": 
  10.                     case "int": 
  11.                     case "image": 
  12.                     case "bit": 
  13.                         typeName = columnInfo.TypeName; 
  14.                         break; 
  15.                     case "nvarchar": 
  16.                     case "nchar": 
  17.                     case "varchar": 
  18.                     case "char": 
  19.                         typeName =string.Format("{0}({1})", columnInfo.TypeName,columnInfo.Length); 
  20.                         break; 
  21.                 } 
  22.                 builder.AppendLine(string.Format("Declare @{0} {1}", columnInfo.ColumnName, typeName)); 
  23.   } 
  24. var columns = string.Join(",", (from column in fieldlist 
  25.                                             select "["+column.ColumnName+"]").ToArray()); 
  26.  
  27. string fetchNexstring.Join(",", (from column in fieldlist 
  28.                                             select "@"+column.ColumnName).ToArray()); 
  29.  
  30. string updatestring.Join(",", (from column in fieldlist 
  31.                                               select "@"+column.ColumnName+"=["+ column.ColumnName+"]").ToArray()); 
  32.  
  33. builder.AppendLine(string.Format("Declare Cur Cursor For Select {0} From [{1}] ", columns, this.tablename)); 
  34. builder.AppendLine("Open Cur"); 
  35.  builder.AppendLine(string.Format("Fetch next From Cur Into {0} ", fetchNex)); 
  36.  builder.AppendLine("While @@fetch_status=0    "); 
  37.  builder.AppendLine("Begin"); 
  38.              
  39. //builder.AppendLine(string.Format("  Update [{0}] Set {1} ",this.tablename,update)); 
  40. builder.AppendLine(string.Format("  Fetch next From Cur Into {0} ", fetchNex)); 
  41.  
  42. builder.AppendLine("End   "); 
  43. builder.AppendLine("Close Cur   "); 
  44. builder.AppendLine("Deallocate Cur"); 

有以下几点需要注意

1  生成的脚本中,字段名称,表名称,均要加上方括号,以避免名称重突。

2  最后生成的SQL源代码,还需要应用下面的方法,将SQL关键字大写。

将SQL查询语句的关键字大写的方法来自CSDN下载区,全文如下

  1. private static Regex RegexSQLCapitalize = new Regex("\\badd\\b|\\baggregate\\b|\\baction\\b|\\balter\\b|\\bas\\b|\\basc\\b|\\basymmetric\\b|\\bauthorization\\b|\\bbegin\\b|\\bbinary\\b|\\bbit\\b|\\bby\\b|\\bcascade\\b|\\bcase\\b|\\bcatalog\\b|\\bcharacter\\b|\\bchar\\b|\\bcheck\\b|\\bcheckpoint\\b|\\bclose\\b|\\bclustered\\b|\\bconstraint\\b|\\bcollate\\b|\\bcolumn\\b|\\bcommit\\b|\\bcontains\\b|\\bcontinue\\b|\\bcreate\\b|\\bcross\\b|\\bcursor\\b|\\bdatabase\\b|\\bdeallocate\\b|\\bdesc\\b|\\bdecimal\\b|\\bdeclare\\b|\\bdefault\\b|\\bdelete\\b|\\bdesc\\b|\\bdistinct\\b|\\bdouble\\b|\\bdrop\\b|\\belse\\b|\\bend\\b|\\bescape\\b|\\bexcept\\b|\\bexec\\b|\\bexecute\\b|\\bexternal\\b|\\bfetch\\b|\\bfloat\\b|\\bforeign\\b|\\bfor\\b|\\bfrom\\b|\\bfunction\\b|\\bget\\b|\\bgroup\\b|\\bgoto\\b|\\bgrant\\b|\\bhaving\\b|\\bidentity\\b|\\binto\\b|\\bindex\\b|\\binsert\\b|\\binstead\\b|\\bint\\b|\\bkey\\b|\\bname\\b|\\bof\\b|\\bon\\b|\\bopen\\b|\\boption\\b|\\border\\b|\\boutput\\b|\\bprimary\\b|\\breturn\\b|\\brollback\\b|\\bschema\\b|\\bselect\\b|\\bsize\\b|\\bsymmetric\\b|\\bset\\b|\\bserver\\b|(\\btable\\b)|\\bthen\\b|\\btop\\b|\\btime\\b|\\btimestamp\\b|\\bto\\b|\\btrigger\\b|\\bprocedure\\b|\\btype\\b|\\bunion\\b|\\bunique\\b|\\bupdate\\b|\\buse\\b|\\bvalues\\b|\\bvalue\\b|\\bvarchar\\b|\\bview\\b|\\bwhen\\b|\\bwhile\\b|\\bwhere\\b|\\bwith\\b|\\bnvarchar\\b|\\bnchar\\b|\\bdatetime\\b|\\bfloat\\b|\\bdate\\b|\\bdatediff\\b|\\bdateadd\\b|\\bdatename\\b|\\bdatepart\\b|getdate|\\breferences\\b|\\babs\\b|\\bavg\\b|\\bcast\\b|\\bconvert\\b|\\bcount\\b|\\bday\\b|\\bisnull\\b|\\blen\\b|\\bmax\\b|\\bmin\\b|\\bmonth\\b|\\byear\\b|\\breplace\\b|\\bsubstring\\b|\\bsum\\b|\\bupper\\b|\\buser\\b|\\ball\\b|\\bany\\b|\\band\\b|\\bbetween\\b|\\bexists\\b|\\bin\\b|\\binner\\b|\\bis\\b|\\bjoin\\b|\\bleft\\b|\\blike\\b|\\bnot\\b|\\bnull\\b|\\bor\\b|\\bright\\b|\\btry\\b|\\bcatch\\b", RegexOptions.IgnoreCase); 
  2.  
  3. public static string CapitalizeSQLClause(string source) 
  4.  
  5.            //先按行划分 
  6.            Regex rowReg = new Regex("\r\n"); 
  7.            string[] strRows = rowReg.Split(source); 
  8.  
  9.            StringBuilder strBuilder = new StringBuilder(); 
  10.            int rowsCount = strRows.Length; 
  11.  
  12.            for (int i = 0; i < rowsCount; i++) 
  13.            { 
  14.                //去掉一行中的一个或多个空白 
  15.                //strRows[i] = Regex.Replace(strRows[i], @"\s+", " "); 
  16.  
  17.                //按空格划分 
  18.                string[] strWords = strRows[i].Split(new char['\0']); 
  19.                int wordsCount = strWords.Length; 
  20.  
  21.                for (int j = 0; j < wordsCount; j++) 
  22.                { 
  23.                    strBuilder.Append(" "); 
  24.                    if (RegexSQLCapitalize.IsMatch(strWords[j])) 
  25.                    { 
  26.  
  27.                        MatchCollection mc = RegexSQLCapitalize.Matches(strWords[j]); 
  28.                        int mcmcCount = mc.Count; 
  29.                        for (int k = 0; k < mcCount; k++) 
  30.                        { 
  31.                            strWords[j] = strWords[j].Replace(mc[k].Value, mc[k].Value.ToUpper()); 
  32.                        } 
  33.                        strBuilder.Append(strWords[j]); 
  34.                    } 
  35.                    else 
  36.                    { 
  37.                        strBuilder.Append(strWords[j]); 
  38.                    } 
  39.                    strBuilder.Append(" "); 
  40.                } 
  41.  
  42.                strBuilder.Append("\r\n"); 
  43.            } 
  44.            return strBuilder.ToString().Replace("\r\n\r\n", "\r\n"); 

正则表达式替换字符串中的关键字,这个方法没有任何依赖,可拷贝到您的项目或类库中,为SQL 脚本增加关键字大写功能。

3  SQL 脚本格式化功能  如果能把生成的SQL脚本格式化一下,生成美观的SQL脚本,增加可读性。SQL Pretty Printer可以做到,但是没有找到API可以调用这个功能。

4 多表关联的游标模板没有做到。应该尝试从多个关联表中生成游标。不过表与表之间的关系难以自动生成,比如像下面的母子表游标询语句

  1. Declare Cur Cursor For Select r.Description,r.WorkCenter  FROM JobOrder  j, JobOrderRouting r 
  2.      WHERE j.JobNo=r.JobNo 
  3. Open Cur 

游标要从2个关联的表中读取数据,如果2个表之间有外键关联,可以生成2个表的外键关联字段的关系,也就是上面的SQL游标可以自动生成,但是有的2个表之间没有外键关联的,还是要手工指定,相当于是个半成品的游标生成器,于是只好把这个功能点拿掉,只做最简单的一种情况,生成一个表的若干个字段的游标查询,没有设计多表查询的游标。

原文链接:http://www.cnblogs.com/JamesLi2015/archive/2013/05/20/3088024.html

【编辑推荐】

责任编辑:彭凡 来源: 博客园
相关推荐

2010-07-26 11:27:43

SQL Server打

2010-07-23 18:33:57

SQL Server游

2010-10-22 13:34:49

SQL Server游

2010-07-26 09:06:09

SQL Server游

2010-10-21 14:06:22

定义SQL Serve

2010-07-14 15:32:28

SQL Server

2010-07-26 08:49:58

SQL Server游

2010-07-13 17:25:55

SQL Server

2010-07-01 14:36:34

SQL Server动

2010-11-12 10:31:25

SQL Server游

2010-07-23 18:39:52

SQL Server游

2010-09-27 16:10:42

SQL Server游

2010-07-26 10:59:59

SQL Server游

2011-04-15 11:43:24

SQL Server

2010-04-02 09:51:37

SQL Server

2011-08-22 14:00:13

SQL Server 游标调用函数

2010-11-11 10:41:03

sql server遍

2010-10-21 14:12:07

SQL Server游

2022-02-09 10:07:03

LinuxSQL Server

2011-07-06 15:47:29

SQL Server分区
点赞
收藏

51CTO技术栈公众号