SQL Server 遊標產生工具 技術原理和原始碼分享

經常做ERP報表，涉及預存程序中讀取資料，多個表之間關聯的資料讀取，用到遊標。經典的讀取使用者表的遊標例子

Declare @Age intDeclare @Name varchar(20)Declare Cur Cursor For Select Age,Name From T_User   Open CurFetch next From Cur Into @Age,@NameWhile @@fetch_status=0     Begin   Update T_User Set [Name]=@Name,Age=@Age   Fetch Next From Cur Into @Age,@NameEnd   Close Cur   Deallocate Cur

在實際應用時，經常需要找到這個模板，然後再根據實際的表結果，重寫一遍。經常遇到以下二個問題

1  上面的例子指令碼不知道放在哪裡了，或是有很多例子指令碼，不方便很快找出來

2  重寫遊標的例子，經常重複，又沒有技術難度可言。比如讀取工作單生產計劃，讀取使用者。

經過思考，於是寫個遊標產生工具，把上面的模板代碼，應用到代碼產生器中。

注意中的Script Cursor，這是用來產生遊標模板的。選擇一個資料庫，樹左邊選擇表名，勾選欄位值，點擊執行

 DECLARE  @UserID  NVARCHAR(10)  DECLARE  @UserName  NVARCHAR(50)  DECLARE  Cur  CURSOR  FOR  SELECT  [UserID],[UserName]  FROM  [USER]    OPEN  Cur  FETCH  next  FROM  Cur  INTO  @UserID,@UserName    WHILE  @@fetch_status=0          BEGIN      FETCH  next  FROM  Cur  INTO  @UserID,@UserName    END        CLOSE  Cur        DEALLOCATE  Cur 

 

原始碼不到50行，全文如下

 List<ColumnInfo>  fieldlist = this.GetFieldlist(); StringBuilder builder=new StringBuilder(); string typeName = string.Empty;            foreach (ColumnInfo columnInfo in fieldlist)    {                switch (columnInfo.TypeName)                {                    case "datetime":                    case "int":                    case "image":                    case "bit":                        typeName = columnInfo.TypeName;                        break;                    case "nvarchar":                    case "nchar":                    case "varchar":                    case "char":                        typeName =string.Format("{0}({1})", columnInfo.TypeName,columnInfo.Length);                        break;                }                builder.AppendLine(string.Format("Declare @{0} {1}", columnInfo.ColumnName, typeName));  }var columns = string.Join(",", (from column in fieldlist                                            select "["+column.ColumnName+"]").ToArray());string fetchNex= string.Join(",", (from column in fieldlist                                            select "@"+column.ColumnName).ToArray());string update= string.Join(",", (from column in fieldlist                                              select "@"+column.ColumnName+"=["+ column.ColumnName+"]").ToArray());builder.AppendLine(string.Format("Declare Cur Cursor For Select {0} From [{1}] ", columns, this.tablename));builder.AppendLine("Open Cur"); builder.AppendLine(string.Format("Fetch next From Cur Into {0} ", fetchNex)); builder.AppendLine("While @@fetch_status=0    "); builder.AppendLine("Begin");            //builder.AppendLine(string.Format("  Update [{0}] Set {1} ",this.tablename,update));builder.AppendLine(string.Format("  Fetch next From Cur Into {0} ", fetchNex));builder.AppendLine("End   ");builder.AppendLine("Close Cur   ");builder.AppendLine("Deallocate Cur");

有以下幾點需要注意

1  產生的指令碼中，欄位名稱，表名稱，均要加上方括弧，以避免名稱重突。

2  最後產生的SQL原始碼，還需要應用下面的方法，將SQL關鍵字大寫。

將SQL查詢語句的關鍵字大寫的方法來自CSDN下載區，全文如下

 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); public static string CapitalizeSQLClause(string source) {            //先按行劃分            Regex rowReg = new Regex("\r\n");            string[] strRows = rowReg.Split(source);            StringBuilder strBuilder = new StringBuilder();            int rowsCount = strRows.Length;            for (int i = 0; i < rowsCount; i++)            {                //去掉一行中的一個或多個空白                //strRows[i] = Regex.Replace(strRows[i], @"\s+", " ");                //按空格劃分                string[] strWords = strRows[i].Split(new char['\0']);                int wordsCount = strWords.Length;                for (int j = 0; j < wordsCount; j++)                {                    strBuilder.Append(" ");                    if (RegexSQLCapitalize.IsMatch(strWords[j]))                    {                        MatchCollection mc = RegexSQLCapitalize.Matches(strWords[j]);                        int mcCount = mc.Count;                        for (int k = 0; k < mcCount; k++)                        {                            strWords[j] = strWords[j].Replace(mc[k].Value, mc[k].Value.ToUpper());                        }                        strBuilder.Append(strWords[j]);                    }                    else                    {                        strBuilder.Append(strWords[j]);                    }                    strBuilder.Append(" ");                }                strBuilder.Append("\r\n");            }            return strBuilder.ToString().Replace("\r\n\r\n", "\r\n"); }

Regex替換字串中的關鍵字，這個方法沒有任何依賴，可拷貝到您的項目或類庫中，為SQL 指令碼增加關鍵字大寫功能。

 

3  SQL 指令碼格式化功能  如果能把產生的SQL指令碼格式化一下，產生美觀的SQL指令碼，增加可讀性。SQL Pretty Printer可以做到，但是沒有找到API可以調用這個功能。

4 多表關聯的遊標模板沒有做到。應該嘗試從多個關聯表中產生遊標。不過表與表之間的關係難以自動產生，比如像下面的母子表遊標詢語句

Declare Cur Cursor For Select r.Description,r.WorkCenter  FROM JobOrder  j, JobOrderRouting r     WHERE j.JobNo=r.JobNoOpen Cur

遊標要從2個關聯的表中讀取資料，如果2個表之間有外部索引鍵關聯，可以產生2個表的外部索引鍵關聯欄位的關係，也就是上面的SQL遊標可以自動產生，但是有的2個表之間沒有外部索引鍵關聯的，還是要手工指定，相當於是個半成品的遊標產生器，於是只好把這個功能點拿掉，只做最簡單的一種情況，產生一個表的若干個欄位的遊標查詢，沒有設計多表查詢的遊標。