大大毛 的笔记

  DDM's Note

哪怕没有办法一定有说法,
就算没有鸽子一定有乌鸦,
固执无罪 梦想有价,
让他们惊讶.

posts - 14, comments - 23, trackbacks - 0, articles - 58
   :: 首页 ::  :: 联系 ::  :: 管理

複雜SQL語句的構建以及內部的封裝調試

Posted on 2007-01-02 08:54 大大毛 阅读(665) 评论(0)  编辑  收藏 所属分类: SQL

   前言：
      這段時間還一直在努力之中，這一次在SQL的構建上遇到一個挑戰，不過這一次的挑戰是自找的。

      開發環境是VB6的DLL＋ASP，分到的活是一個報表模塊。
      需求
         我這一塊的報表，現階段是要實現兩個功能(就我看應該只是一個，需求理解錯誤可不是我的事兒)，為了與其它的模塊保持一致，工作流程如下：
         1.輸入查詢的年 / 月;
         2.查詢結果並以網頁表格的方式提供預覽;
         3.選擇發送郵件功能;
         4.選取需要發送的郵件收件人列表;
         5.查詢結果生成Excel文件並以郵件附件的方式發送。
       輸入
         參數是一個 年份值＋月份值：
         1. lngYear (0001-9999)，年份值;
         2. lngMonth (1-12)，月份值.
         由於Input參數少，JS檢驗比較簡單，因此會在客戶端JS提供第一層的校驗，而模塊裡面就不再做檢驗，捕獲異常即可。
      輸出
         根據輸入的參數，搜尋記錄，提供瀏覽以及發送Excel報表的能力(以郵件附件的方式發送，在此不做論術)。

         再來說說環境的事情，開發環境是用VB的DLL實現功能封裝，因此我的報表會是一個cls，ASP頁面只要實現View即可。數據環境是用MSSQL2000，第1個難點出現在這裡：數據環境雖說是用的MSSQL，但是卻不能使用Procedure;DB的設計也增加了難度：整個DB實現上是包含了兩個系統的數據庫，不同的結構，不同的命名規則，卻有相同含義的字段。為了便於說明，拉一個示例數據庫出來先：

          見 PDM 圖 (去掉全部無關字段)

         可以看到，DB中以 tbXXX 方式命名的表與 XXX(AS400) 的表有著很大的差異，但是它們之間又存在著相同之處。
      再來看看表間的隱式關聯關係 (沒有以FK的方式加以約束)

  • 合約租金(tbContractRent)  與 職場(tbPlace)的關聯：職場代碼相同 ，合約是職場在某一段時間的表現，即相同職場在某一時刻只會存在一份合約
  • 合約租金(tbContractRent)  與 職場單位(tbUnit)的關聯：單位的坪數生效日/坪數失效日的區間 落在一份合約租金生效/租金失效區間之內
  • 職場單位(tbUnit) 與 單位資料AS400(AAABREP)的關聯：單位代碼 + 單位序號 相同
  • 職場單位(tbUnit) 與 單位資料2AS400(DAD6CPP)的關聯：(單位代碼 + 單位序號 相同) 並且 (原職場代碼 = 職場代碼 或 新職場代碼 = 職場代碼)
  • 合約租金(tbContractRent)  與 職場資料AS400(DAC9CPP)的關聯：職場資料到期日 落在 一份合約租金生效/租金失效日區間之內
       功能描述：
  1. “閒置租約”：
    職場單位與租約相關，這裡的“閒置”狀態由單位引發，描述是指在一份合約內，單位發生過“撤銷”，然後就會從職場中“遷出”，那麼遷出日期－－合約租金失效日的區間內即是“閒置”。
    因此該功能的條件入口表是 合約租金(tbContractRent)，該功能的業務邏輯比較複雜，流程如下：
    a. 在考察期內有效的合約;
    b. 在合約中的單位發生過“撤銷”-- 通過(tbUnit)連接到(AAABREP)檢查是否存在有撤銷日期(合法日期 並且 發生在考察期之前)，另外由於存在業務邏輯，這裡的撤銷日期在使用時需要向後加一個月;
    c. 如果該單位符合撤銷條件則通過(tbUnit)連接到(DAD6CPP)獲取搬遷日期，同樣的，這裡所得到的搬遷日期由於一定的業務邏輯，還需要檢查它的有效性，只有大於撤銷日期的才會認定為是合法的搬遷日期，否則將使用(tbUnit)表中的坪數失效日來做為該單位的搬遷日期;
    d. 搬遷日期必須早於考察的結束時間;
  2. “待退職場”：
    是指一個職場的最後一份合約的失效日落在考察期內，由於相同資料的以 XXXAS400表為主，因此條件入口表是職場資料AS400(DAC9CPP)
  3. 最終的表現形式是一張2維表格的方式，即功能1 與 功能2的結果要放在一起

      約束：
         不允許使用存儲過程，只能在VB的DLL中實現。

      思考及方案選擇：
         1. 功能1是這次任務的難點，實現邏輯需要跑的表比較多，而且邏輯間也比較複雜，看上去並不能簡單的用 Inner/Left Join + Where就可以搞定那幾表。
         2. 最終的結果集會建立在指定的幾張表上，與實現 功能 所聯接的表並不相同，無法在結果表上進行直接篩選，還需要將上面所拿到的條件結果集與最終的表現表進行一次關聯。
         3. 實現這次功能的方法有兩種：
         3.1. 用ADO對象的嵌套來實現，配合上帶層次的 Function ，這是比較傳統的解決方案，完全可以解決這類問題。
            優點
            a. 適用面廣，複雜度比較低;
            b. 函數封裝，功能邏輯清晰;
            缺點
            a. 很明顯的，在這次的任務中要實現功能，需要使用3層以上的循環，這種邊循環邊查詢的方式的效率是極低的;
         3.2. 嚐試使用複雜SQL來構造邏輯實現。
            使用這種解決方法，在該問題上算是一種比較酷的解決。使用它的好處就是能提高查詢效率，當然對於我來說，這算是一種嚐試和創新，嘻嘻。
            優點
            a. 查詢效率高，因為它只會發出一條SQL;
            缺點
            a. 難於構建，複雜度高;
            b. 如果結構不好則很難調試及更改，發生需求變更時改動難度大;

      細節分析：
         最終俺決定使用複雜SQL來構建邏輯，雖說實現起來會很困難，時間也蠻緊巴的，不過這是一個挑戰，我之前也對自己的能力抱有信心。好了，切入正題，談談我的實現細節。
         對於之前所考慮到的問題，我針對性的設計一種結構來應付它。
         a. 由於這次的查詢條件表比較多，各表間無法直連，所以我在表這一層上使用封裝，以降低它們之間的瓜葛，並試圖將表這一層的變動壓抑在封裝之內，這一點至關重要，因為SQL就象是一座塔，底部會由幾個基礎表支撐，這就好比萬噸壓力全都壓在幾個支撐柱上，一旦柱子垮了塔也將不復存在;
         b. 在 a 的表封裝基礎上使用模塊化的構建，SQL語句說到底都是一句句拼湊起來的，再複雜的語句也不過如此。因此按照邏輯一步步的走下來，逐步的封成單一的模塊，最後再象積木一樣搭建起來是很重要的;
         c. 這次頁面的功能是有分成 2 個部分的，而這2功能的查詢入口表及連接順序又不相同(不要跟我說表 A 連接到 表 B 與表 B 連接到表 A 的邏輯是相同的)，不過邏輯的核心表都落在合約租金(tbContractRent) 上，因此采取將產出表與條件表切割開來，而2功能使用聯合的辦法來完成產出與邏輯的分離，SQL的結構示例成為這樣： Select * From 產出表 Join (功能1 Union 功能2) As 功能表 On 產出與邏輯間的關聯。
         d. 對於SQL的調試問題，由於成品SQL的體積會使得調試起來頗為複雜，因此我在功能模塊上增加調試用的接口，這樣就能夠將逐步形成的半成品給輸出來，有利於外步調試時的分步分析;
         e. 為便於SQL調試，在代碼的書寫上使用了一點小技巧。通常在合成SQL時語句的寫法會是這樣:
         strSQL="Select * From Table Where col1='" & p1 & "' And col2='" & p2 & "'"
         這樣的寫法會增加源代碼的檢查難度，大量的字符串連接符和變量充斥其中，現在改成這樣寫:
         strSQL = "Select * From Table Where col1=@p1 And col2=@p2"
         在 SQL 語句搭建完成後再使用 strSQL = Repalce(strSQL,"@p1","'xxx'") 的方式來代入參數，即不會影響使用，又降低了源碼的檢查難度，而且配合調試時輸出:
         declare @p1 char(8),@p2 char(8)
         select @p1='xxx',@p2='yyy'
         這樣一來，就可以很方便地將調試 SQL 語句直接 Copy 進查詢分析器，直接修改輸入參數進行調試分析了。

      實現代碼：
         第一次嚐試使用這種方法來實現，花了很長時間(約2天時間)才完成，還好調試時只遇到了一個很小的失誤，以後模塊又經歷了多次變更，後續文章中會加以討論在這種實現方式下我是如何跟隨需求變更的，當然，經過了N次的變更之後，還是....
CODE

      看這段代碼需要的是耐心，順著邏輯一步步地往下走才行。
      代碼中對基礎表的封裝，可以看 str[表名]_TB 這樣命名的變量，它將一張表封裝起來，然後重要的一點就是逐層的表命名，這裡使用了原表的名稱，雖然看起來有些混亂，但是這是調試所必需的。 

      後記：
         這一次的嚐試，在時間上消耗比較大，開始時對於結果的正確性還有著一絲懷疑，不過在完工後也就釋然了，只有一處比較小的筆誤進行過調試，算是對這次的結構上的一點肯定吧，嘻嘻。
         哎，不過測試時還是遇到了令人沮喪的事情，這次測試MM最終對我說任務很忙，俺的測試報告自己寫就好，咳咳。
         如果有仔細看完上面的SQL源碼，不難看出除了在結構上完成了表及表間邏輯的封裝外，而且將最終形成的視圖邏輯也進行了封裝，因此在ASP視圖中僅僅需要完成循環顯示即可，這除了簡化ASP視圖外，還對後面幾次的需求變動造成了很深的影響，決定了日後變動的走向。


只有注册用户登录后才能发表评论。
网站导航:
 

i am ddm