今天写了个商品采购入得存储过程，使用游标实现的，实现了多个商品库存的增加...

create procedure [dbo].[Proc_ruku]
(
    @Purchase_Id int
)
as
Begin Try
    BEGIN TRANSACTION T      
        Update Purchase Set Purchase_State=1 Where Purchase_Id=@Purchase_Id  --修改状态
    --声明一个游标
        Declare @PurchaseMate_Num int,@Product_Id int,@WareHose_Id int
        Declare Mycursor Cursor
        For    select PurchaseMate_Num,Product_Id,WareHose_Id from PurchaseMate join Purchase on Purchase.Purchase_Id=PurchaseMate.Purchase_Id where Purchase.Purchase_Id=@Purchase_Id
        --打开游标
        Open Mycursor
        --循环一个游标
        Fetch next from Mycursor into @PurchaseMate_Num,@Product_Id,@WareHose_Id
        
        while @@FETCH_STATUS=0
        begin
            update Stock set Stock_num= Stock_num+@PurchaseMate_Num where Product_Id=@Product_Id and WareHose_Id=@WareHose_Id
            
            fetch next from Mycursor into @PurchaseMate_Num,@Product_Id,@WareHose_Id--转到下一条游标，没有就会死循环
        end
        CLOSE Mycursor  --关闭游标 
        DEALLOCATE  Mycursor--释放游标    
        COMMIT TRANSACTION T
        return 1
END TRY    
    
BEGIN CATCH
    DECLARE @msg nvarchar(2000)=ERROR_MESSAGE()    --将捕捉到的错误信息存在变量@msg中             
    RAISERROR (@msg,16,1)    --此处才能抛出(好像是这样子....)
    ROLLBACK TRANSACTION T --回滚
    close Mycursor  --关闭游标 
    DEALLOCATE  Mycursor--释放游标    
    return 0
END CATCH

 

转载于:https://www.cnblogs.com/dzdrmmf/p/6067181.html