sql server 函數，預存程序，遊標以及while和if語句的嵌套使用，while嵌套

sql server 函數，預存程序，遊標以及while和if語句的嵌套使用，while嵌套

編寫這個預存程序的主要原因是自己太懶，不願意每天往表裡面做資料，所以就寫了他。不僅僅是學習，也是自己的鍛煉。

USE [oaerp]
GO
/****** Object:  StoredProcedure [dbo].[s_Get_AutoYh]    Script Date: 02/11/2015 17:17:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Author,,ljq>
-- Create date: <Create Date,,2015-2-1>
-- Description: <Description,,自動產生一個門店指定時間範圍的所有需要養護商品的養護記錄>

-- =============================================
ALTER PROCEDURE [dbo].[s_Get_AutoYh]

 @sdtm datetime=null,--開始的時間
 @edtm datetime=null,--結束的時間
 @DepoID char(10)=null,
 @UserID char(20)=null,
 @type varchar(30)=null,
 -- 影響的行數
 @num int=null output
AS
--一般藥品養護記錄（ybyh）
if (@type='ybyh')
BEGIN
 
      --建立一張暫存資料表
    select a.* into #tmpyh from dbo.GetGoodsBatchListUnReal(@DepoID,'') a inner join ggoods b on a.goodsid=b.goodsid
    left join gGoodsExt c on b.goodsid =c.goodsid
    where a.amount >0
    and b.catid like '01%'
    and c.storeid !='07'
    order by a.effdate
   
   
    declare @days int --計算天數
    declare @count int  --統計要養護的資料
    declare @Idx int
    declare @n int --記錄每天需要插入的資料
    declare @m int --記錄執行的天數
    declare @Doccode varchar(30)
    declare @GoodsID char(10)
    declare @Amount decimal(18,6)
    declare @BatchNo varchar(40)
    declare @EffDate datetime
    declare @StockArea char(2)
    declare @Station char(2)
    declare @CheckResult char(2)
    declare @DoMethod char(2)
    declare @MakID char(10)
    declare @MakDtm datetime
 
    -- 開始和結束時間的差就是本次需要添加養護記錄天數
    set @days = datediff(day,@sdtm,@edtm)
    if @days = 0
    begin
    set @days = 1
    end
    --得到總的記錄數，即當前店所有的品種
    select @count = count(*) from #tmpyh
    --設定每天的記錄條數，向下取整。
    set @n=floor(@count/30)
   
    set @m=1
   
    --開始事務
    --begin tran
    begin try
   
  
   
   set @makdtm =@sdtm
   while(@days>0)
   begin
    
    
     declare ybyh cursor global-- 定義一個遊標
     read_only
     for select goodsid,batchno,effdate,amount from #tmpyh
     open ybyh 
    
    ----如果執行的天數在輸入的時間範圍內
    --while(@m<=@days)
    --begin
    --  --每執行三十天，結束一次迴圈。
    --  if(@m%30!=0)
    --  BEGIN
      -- 開啟遊標
      --給參數賦值@goodsid,@batchno,@effdate,@amount
      fetch next from ybyh into @goodsid,@batchno,@effdate,@amount 
      --執行遊標第一條記錄
      --=fetch next from ybyh
      
      exec [s_Get_DocCodeSerial_ByTradeID] @TradeID='60',@depoID=@DepoID,@DocCode=@DocCode output
      while (@@fetch_status = 0)
      BEGIN
      --執行每天需要插入的資料，當@n小於零的時候跳出迴圈
      --while(@n>0)
      --begin
      
        --if not exists(select * from zGspyhRecord where goodsid =@goodsid and isnull(batchno,'')=isnull(@batchno,'') and isnull(effdate,'') =isnull(@effdate,''))
        --begin
         
         select @stockarea = case a.otcid 
         when '01' then '00'
         when '02' then '01'
         when '03' then '02'
         end 
         from ggoods a where goodsid =@goodsid
         select @Idx=isnull(max(Idx),0)+1 from zGspyhRecord where DocCode=@DocCode
        
         --設定時間和養護員資訊
         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,@MakDtm,null,null)

          set @n=@n-1
          --在秒上面加上idx序號，作為新的時間的秒
          set @Idx=@Idx+1
          set @MakDtm=DATEADD(SS,@Idx,@MakDtm)
          
         print('today had '+str(@n) )
         if @n<=0
         begin
          exec [s_Get_DocCodeSerial_ByTradeID] @TradeID='60',@depoID=@DepoID,@DocCode=@DocCode output
          set @MakDtm=DATEADD(DD,1,@MakDtm)
          set @days =@days -1
          set @n=floor(@count/30)
          
          if(@days%30=0)
          begin
           print('days:'+str(@days))
           break
          end
         end
        --end
        -- 執行遊標，獲得資料，直到讀取所有的資料集合
        fetch next from ybyh into @goodsid,@batchno,@effdate,@amount
       
     --end
     --END
     
     --set @m=@m+1  --設定天數增加1天
     --set @MakDtm=DATEADD(DD,1,@MakDtm)
     END
     
     close ybyh -- 關閉遊標
     deallocate ybyh -- 刪除遊標
 
   end

  --返回執行的記錄，如果大於0，表示執行成功
    set @num =@@rowcount
    select @num
   --commit tran
   --結束事務
   end try 
    
   begin catch
    print error_message()
    --rollback tran
   end catch
END