SQL2005使用游標的實例(SBO中計算到期應收賬款)

??? 我的SQL語言的查詢做得并不好。但我可以用游標來實現之。實現之后才發現，無論多么復雜的查詢，利用游標也都會更容易理解。雖然使用游標可能會犧牲一定的性能，但在硬件快速發展的今天，犧牲那么一點半點也未為不可。
??? 以下是我使用游標進行查詢的一個實例，其中應該使用臨時表的地方因為沒有創建成功，只好使用事先創建好的表，顯得此段代碼有些不足，希望能給大家作拋磚引玉之用。
??? 這是一個在SBO中計算到期應收賬款的應用，由于時間倉促，代碼中沒有太多注釋，日后我會慢慢補上的。
代碼：

?1CREATE?PROCEDURE?TS_GetPayDay
?2AS
?3BEGIN?
?4????DECLARE?@DocNum?NVARCHAR(100)??????--訂單號
?5????DECLARE?@NumAtCard?NVARCHAR(100)???--合同號
?6????DECLARE?@CardName?NVARCHAR(100)????--客戶
?7????DECLARE?@PayTimes?INT??????????????--總付款次數
?8????DECLARE?@PayTime?INT???????????????--付款次序
?9????DECLARE?@Prcnt?INT?????????????????--付款百分比
10????DECLARE?@DocTotal?NUMERIC??????????--訂單總額
11
12????DECLARE?@DocDate?DATETIME??????????--單據日期
13????DECLARE?@PayDay?DATETIME???????????--到期付款日期
14????DECLARE?@PayDay1?DATETIME??????????--
15????DECLARE?@Months?INT????????????????--間隔月份
16????DECLARE?@Days?INT??????????????????--間隔天數
17????DECLARE?@CurRow?INT????????????????--當前行
18????DECLARE?@CurCtg?INT????????????????--當前付款條款
19
20????DELETE?FROM?[TS_Payment]
21????DELETE?FROM?[TS_Payment1]
22????DELETE?FROM?[TS_Payment2]
23
24????DECLARE?Rows_cursor?CURSOR
25????FOR
26????????SELECT?T0.DocEntry
27????????FROM?ORDR?T0?ORDER?BY?T0.DocEntry
28????OPEN?Rows_cursor;
29????FETCH?NEXT?FROM?Rows_cursor?INTO?@CurRow;
30????WHILE(@@FETCH_STATUS<>-1)
31????BEGIN
32????????IF(@@FETCH_STATUS<>-2)
33????????BEGIN
34????????????SET?@DocNum=(SELECT?DocNum?FROM?ORDR?WHERE?DocEntry=@CurRow);
35????????????SET?@NumAtCard=(SELECT?NumAtCard?FROM?ORDR?WHERE?DocEntry=@CurRow);
36????????????SET?@DocDate=(SELECT?DocDate?FROM?ORDR?WHERE?DocEntry=@CurRow);
37????????????SET?@CardName=(SELECT?CardName?FROM?ORDR?WHERE?DocEntry=@CurRow);
38????????????SET?@DocTotal=(SELECT?DocTotal?FROM?ORDR?WHERE?DocEntry=@CurRow);
39
40????????????INSERT?INTO?[TS_Payment1](DocNum,NumAtCard,CardName,DocTotal)
41????????????????VALUES(@DocNum,@NumAtCard,@CardName,@DocTotal)
42????????????????
43????????????SET?@PayTimes=(SELECT?InstNum?FROM?OCTG?WHERE?GroupNum=
44????????????????(SELECT?GroupNum?FROM?ORDR?WHERE?DocEntry=@CurRow));
45????????????DECLARE?Ctg_cursor?CURSOR
46????????????FOR?
47????????????????SELECT?T1.IntsNo?
48????????????????FROM?CTG1?T1
49????????????????WHERE?T1.CTGCode=(SELECT?GroupNum?FROM?ORDR?WHERE?DocEntry=@CurRow)
50????????????OPEN?Ctg_cursor;
51????????????FETCH?NEXT?FROM?Ctg_cursor?INTO?@CurCtg;
52????????????WHILE(@@FETCH_STATUS<>-1)
53????????????BEGIN
54????????????????IF(@@FETCH_STATUS<>-2)
55????????????????BEGIN
56????????????????????--SELECT?@CurCtg
57????????????????????SET?@PayTime=(SELECT?IntsNo?FROM?CTG1?WHERE?IntsNo=@CurCtg?
58????????????????????????AND?CTGCode
59????????????????????????=(SELECT?GroupNum?FROM?ORDR?WHERE?DocEntry=@CurRow));?
60????????????????????SET?@Prcnt=(SELECT?InstPrcnt?FROM?CTG1?WHERE?IntsNo=@CurCtg
61????????????????????????AND?CTGCode
62????????????????????????=(SELECT?GroupNum?FROM?ORDR?WHERE?DocEntry=@CurRow));?
63????????????????????SET?@Months=(SELECT?InstMonth?FROM?CTG1?WHERE?IntsNo=@CurCtg?
64????????????????????????AND?CTGCode
65????????????????????????=(SELECT?GroupNum?FROM?ORDR?WHERE?DocEntry=@CurRow));
66????????????????????SET?@Days=(SELECT?InstDays?FROM?CTG1?WHERE?IntsNo=@CurCtg?
67????????????????????????AND?CTGCode
68????????????????????????=(SELECT?GroupNum?FROM?ORDR?WHERE?DocEntry=@CurRow));
69
70????????????????????SET?@PayDay1=DATEADD(MONTH,@Months,@DocDate);
71????????????????????SET?@PayDay=DATEADD(DAY,@Days,@PayDay1);
72????????????????????INSERT?INTO?[TS_Payment2](DocNum,NumAtCard,CardName,
73????????????????????????DocTotal,PayTimes,PayTime,Prcnt,PayDay)
74????????????????????????VALUES(@DocNum,@NumAtCard,@CardName,
75????????????????????????@DocTotal,@PayTimes,@PayTime,@Prcnt,@PayDay)
76????????????????END
77????????????????FETCH?NEXT?FROM?Ctg_cursor?INTO?@CurCtg;
78????????????END
79????????????CLOSE?Ctg_cursor;
80????????????DEALLOCATE?Ctg_cursor;
81????????END
82????????FETCH?NEXT?FROM?Rows_cursor?INTO?@CurRow;
83????END
84????CLOSE?Rows_cursor;
85????DEALLOCATE?Rows_cursor;
86
87????DELETE?FROM?[TS_Payment1]?WHERE?DocNum?IN(SELECT?DocNum?FROM?[TS_Payment2])
88
89????INSERT?INTO?[TS_Payment]
90????SELECT?*?FROM?[TS_Payment1]
91????????UNION?ALL?
92????SELECT?*?FROM?[TS_Payment2]
93END posted on 2008-04-25 09:17 guofu 閱讀(...) 評論(...) 編輯 收藏

轉載于:https://www.cnblogs.com/saptechnique/archive/2008/04/25/1170407.html

總結

以上是生活随笔為你收集整理的SQL2005使用游标的实例(SBO中计算到期应收账款)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。