更新库存SQL算法2.0版

 

Create          Procedure  sp_ComputeStock
    @StoreroomId   INT   =   NULL , -- 库房编号
   @EquipmentIds   VARCHAR ( 5000 )  =   NULL , -- 设备编号,各设备编号间用逗号隔开,例如:1,2,3,4
   @EquipmentMount   VARCHAR ( 5000 )  =   NULL , -- 设备数量,各设备数量间用逗号隔开,例如:11,15,20,5

   @Out_equipIdNotExists   VARCHAR ( 5000 ) OUTPUT,   -- 不存在的设备ID，各设备用逗号隔开
   @Out_equipNameNotExists   VARCHAR ( 5000 ) OUTPUT,   -- 不存在的设备名称，各设备用逗号隔开
   @Out_equipIdNotEnough   VARCHAR ( 5000 ) OUTPUT,    -- 库存不够的设备ID,各设备用逗号隔开
   @Out_equipNameNotEnough   VARCHAR ( 5000 ) OUTPUT,    -- 库存不够的设备名称,各设备用逗号隔开
   @Out_Stockpile   VARCHAR ( 5000 ) OUTPUT   -- 设备ID的库存量，各设备用逗号隔开
  AS

   SET  NOCOUNT  ON
   SET  CURSOR_CLOSE_ON_COMMIT  OFF      -- 设置手动关闭游标,使事务不会影响到游标的打开和关闭

   DECLARE  
     @Empty   CHAR ( 1 ),  -- 空串
     @Split   CHAR ( 1 ), -- 分隔符
     @equipName   VARCHAR ( 100 )
   SET   @Empty   =   ''
   SET   @Split   =   ' , '  
   SET   @Out_equipIdNotExists   =   @Empty    -- 初始化为空串
   SET   @Out_equipIdNotEnough   =   @Empty
   SET   @Out_equipNameNotExists   =   @Empty
   SET   @Out_equipNameNotEnough   =   @Empty
   SET   @Out_Stockpile   =   @Empty

   DECLARE  
      @tb_EquipmentIds   TABLE ( [ Id ]   INT   IDENTITY  ( 1 , 1 ),EquipmentId  INT ) -- 表变量,设备编号表
   DECLARE  
      @tb_EquipmentMount   TABLE ( [ Id ]   INT   IDENTITY  ( 1 , 1 ),EquipmentMount  INT ) -- 表变量,设备数量表

   INSERT   INTO   @tb_EquipmentIds   SELECT   *   FROM  dbo.f_splitSTR( @EquipmentIds , @Split )
       -- 拆分设备编号，并将结果插入到设备编号表(表变量)中
   INSERT   INTO   @tb_EquipmentMount   SELECT   *   FROM  dbo.f_splitSTR( @EquipmentMount , @Split )
      -- 拆分设备数量，并将结果插入到设备数量表(表变量)中

  
   DECLARE   @au_equipmentId   VARCHAR ( 10 ),  -- 设备编号
           @au_equipmentMount   INT , -- 设备数量
           @var_Mount   INT -- 库存数量
 
     -- 将设备编号表和设备数量表关联，按设备编号分组统计出各设备的出库数量
     -- 注意遍历了两次该游标，第一次用来检查设备，第二次用来更新设备
     -- （若第一次遍历设备通过后，才进行第二次，否则不进行）

   DECLARE  stockBill_cursor  CURSOR  LOCAL FAST_FORWARD  FOR   -- 声明并定义快速只进的本地游标
   SELECT                                                      
     EquipmentId,                                           
      SUM (EquipmentMount)  AS  Mount
   FROM  
     @tb_EquipmentIds   AS  a
   INNER   JOIN   
     @tb_EquipmentMount   AS  b
   ON  
    a. [ ID ]   =  b. [ ID ]
   GROUP   BY  EquipmentId
  
   OPEN  stockBill_cursor
     -- 第一次遍历:检查设备是否在库房中存在，检查设备的库存是否不足。若存在且库存够，
     -- 才进行第二次遍历(更新库存)
   FETCH   NEXT   FROM  stockBill_cursor
   INTO   @au_equipmentId ,  @au_equipmentMount
        -- 将当前设备编号保存到@au_equipmentId中，将当前设备数量保存到@au_equipmentMount中
   WHILE   @@FETCH_STATUS   =   0
     BEGIN
       SET   @var_Mount   =   NULL
        -- 根据库房号和设备编号从库存表中找出该设备的存库数量，并保存到@var_Mount中
       SELECT  
         @var_Mount   =  Mount         
       FROM  
        tb_Stock 
       WHERE  
        F_StoreroomId  =   @StoreroomId  
       AND  
        F_EquipmentId  =   @au_equipmentId
    
       SELECT   @equipName   =  EquipmentName  FROM  dbo.tb_Equipment  WHERE  P_EquipmentId  =   @au_equipmentId

       IF ( @var_Mount   IS   NULL )  -- 若@var_Mount=NULL表明不存在该设备
       BEGIN
         SET   @Out_equipIdNotExists   =   @Out_equipIdNotExists   +   @au_equipmentId   +   @Split
         SET   @Out_equipNameNotExists   =   @Out_equipNameNotExists   +   @equipName   +   @Split
       END
       ELSE
       BEGIN                   -- 否则存在该设备
         -- 所取数量与库存数量比较，若大于库存数量， 将该设备编号记录下来。
         IF  ( @au_equipmentMount   >   @var_Mount )
         BEGIN     
           SET   @Out_equipIdNotEnough   =   @Out_equipIdNotEnough   +   @au_equipmentId   +   @Split -
              - 记录库存不够的设备,用逗号隔开
           SET   @Out_equipNameNotEnough   =   @Out_equipNameNotEnough   +   @equipName   +   @Split
           SET   @Out_Stockpile   =   @Out_Stockpile   +   LTRIM ( STR ( @var_Mount )) + @Split
         END      
       END   --   IF(@var_Mount IS NULL)
      FETCH   NEXT   FROM  stockBill_cursor         -- 取下一条
      INTO   @au_equipmentId ,  @au_equipmentMount
     END   --  WHILE 
   
     -- 若@Out_equipmentNotExists和@Out_equipmentNotEnough都是空串,表明
    -- 即不存在库存不够的设备，  也没有库房中不存在的设备
    IF ( @Out_equipIdNotExists   =   @Empty   AND   @Out_equipIdNotEnough   =   @Empty )    
     BEGIN
        CLOSE  stockBill_cursor     -- 关闭游标   
        OPEN  stockBill_cursor      -- 再次打开游标
        FETCH   NEXT   FROM  stockBill_cursor  -- 第二次遍历,更新库存.
        INTO   @au_equipmentId ,  @au_equipmentMount
        BEGIN   TRANSACTION

        WHILE   @@FETCH_STATUS   =   0
        BEGIN
          SET   @var_Mount   =   NULL
            -- 根据库房号和设备编号从库存表中找出该设备的存库数量，并保存到@var_Mount中
          SELECT  
            @var_Mount   =  Mount        
          FROM  
           tb_Stock 
          WHERE  
           F_StoreroomId  =   @StoreroomId  
          AND  
           F_EquipmentId  =   @au_equipmentId

         UPDATE  
          tb_Stock
         SET  
          Mount  =  Mount  -   @au_equipmentMount
         WHERE  
          F_StoreroomId  =   @StoreroomId  
         AND  
          F_EquipmentId  =   @au_equipmentId

         IF   @@ERROR   <>   0    -- 未知错误
           BEGIN                           
             ROLLBACK   TRANSACTION    -- 回滚
             CLOSE  stockBill_cursor  -- 关闭游标
             DEALLOCATE  stockBill_cursor
             RETURN   @@ERROR   -- 返回错误代码
           END
          FETCH   NEXT   FROM  stockBill_cursor  -- 取下一条
          INTO   @au_equipmentId ,  @au_equipmentMount
        END   --  WHILE
        COMMIT   TRANSACTION         -- 提交事务
     END   -- IF(@Out_equipmentNotExists = @Empty AND @Out_equipmentNotEnough = @Empty)
 
    CLOSE  stockBill_cursor     -- 关闭游标
    DEALLOCATE  stockBill_cursor 
    RETURN   0                 -- 成功返回(执行该过程没有系统出错错误)

本算法是1.0版本的改进版。1.0版中对于多个设备库存不够或不存在的情况，执行该算法一次只能提示一个，不能将所有的库存不够或者不存在的设备都一一显示出来（设备名称）。给用户的操作带来不便，为此改进了该算法。物理表，函数详见《更新库存SQL算法1.0版》.