sqlserver函數(shù)存儲過程游標以及while和if語句的嵌套使用

1、sqlserver函數(shù)，存儲過程，游標以及whileif語句的嵌套使編寫這個存儲過程的主要原因是太懶，不愿意每天往表做數(shù)據(jù)，所以就寫了他。不僅僅是學(xué)習(xí)，也是的鍛煉。USE oaerpGO/* Object: StoredProcedure dbo.s_Get_AutoYh Script Date: 02/11/2015 17:17:35 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO- =- Author: - Create date: - Description: - =ALTER PROCEDURE dbo.s_Get_AutoYhsdtm

2、datetime=null,-開始的時間edtm datetime=null,-結(jié)束的時間DepoID char(10)=null,UserID char(20)=null,type varchar(30)=null,- 影響的數(shù)num int=null outputAS-般藥品養(yǎng)護記錄（ybyh）if (type=ybyh)BEGIN-創(chuàng)建張臨時表select a.* into #tmpyh from dbo.GetGoodsBatchListUnReal(DepoID,) a inner join ggoods b on a.goodsid=b.goodsidleft join gGood

3、sExt c on b.goodsid =c.goodsidwhere a.amount 0and b.catid like 01%and c.storeid !=07order by a.effdatedeclare days int -計算天數(shù)declare count int -統(tǒng)計要養(yǎng)護的數(shù)據(jù)declare Idx intdeclare n int -記錄每天需要插的數(shù)據(jù)declare m int -記錄執(zhí)的天數(shù)declare Doccode varchar(30)declare GoodsID char(10)declare Amount decimal(18,6)declare B

4、atchNo varchar(40)declare EffDate datetimedeclare StockArea char(2)declare Station char(2)declare CheckResult char(2)declare DoMethod char(2)declare MakID char(10)declare MakDtm datetimedeclare MakDtm datetime- 開始和結(jié)束時間的差就是本次需要添加養(yǎng)護記錄天數(shù)set days = datediff(day,sdtm,edtm)if days = 0beginset days = 1end-

5、得到總的記錄數(shù)，即當(dāng)前店所有的品種select count = count(*) from #tmpyh-設(shè)置每天的記錄條數(shù)，向下取整。set n=floor(count/30)set m=1-開始事務(wù)-begin tranbegin tryset makdtmwhile(days0)begindeclare ybyh cursor global- 定義個游標read_onlyfor select goodsid,batchno,effdate,amount from #tmpyhopen ybyh-如果執(zhí)的天數(shù)在輸?shù)臅r間范圍內(nèi)-while(m0)-begin-if not exists(se

6、lect * from zGspyhRecord where goodsid and isnull(batchno,)=isnull(batchno,) and isnull(effdate,)=isnull(effdate,)-beginselect stockarea = case a.otcidwhen 01 then 00when 01 then 00when 02 then 01when 03 then 02endfrom ggoods a where goodsidselect Idx=isnull(max(Idx),0)+1 from zGspyhRecord where-設(shè)置時

7、間和養(yǎng)護員信息set MakID=UserID-select DATEADD(dd,5550,2015-02-12 11:11:13)insert into zGspyhRecord(DocCode, DepoID, Idx, GoodsID, Amount,BatchNo, EffDate, StockArea, Station, CheckResult,DoMethod, MakID, MakDtm,CheID, CheDtm)values(DocCode,DepoID,Idx,GoodsID,Amount,BatchNo,EffDate,stockarea,00,00,03,MakID,

8、MakDtm,null,nset n=n-1-在秒上加上idx序號，作為新的時間的秒set Idx=Idx+1set MakDtm=DATEADD(SS,Idx,MakDtm)print(today had +str(n) )if n=0beginexec s_Get_DocCodeSerial_ByTradeID TradeID=60,depoID=DepoID,DocCode=DocCode outputset MakDtm=DATEADD(DD,1,MakDtm)set days -1set n=floor(count/30)if(days%30=0)beginprint(days:+str(days)breakendend-end- 執(zhí)游標，獲得數(shù)據(jù)，直到讀取所有的數(shù)據(jù)集合fetch next from ybyh into goodsid,batchno,effdate,amount-end-END-set m=m+1 -設(shè)置天數(shù)增加1天-set MakDtm=DATEADD(DD,1,MakDtm)ENDclose ybyh - 關(guān)閉游標deall