游标使用

SET NOCOUNT ON
DECLARE @poc INT,@PurchaseOrderId NVARCHAR(40),@EBELP INT,@sql NVARCHAR(max),
@PurchaseOrderDetailId NVARCHAR(40),@ExpenseShareId NVARCHAR(40),
@DetailCursor CURSOR, --订单明细游标
@ShareCursor CURSOR--订单分摊游标    
DECLARE OrderCursor CURSOR LOCAL FOR SELECT DISTINCT PurchaseOrderCode FROM SAPMiddleDB.dbo.SAP_PurchaseOrder_Item --WHERE PurchaseOrderCode IN (1129,1130)
OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @poc
WHILE(@@FETCH_STATUS=0)
BEGIN    
    SET @PurchaseOrderId=NULL
    ------------------------------------------------------------------------------------------------------------------
    SELECT @PurchaseOrderId=PurchaseOrderId FROM purchase20150427.dbo.t_d_PurchaseOrder WHERE PurchaseOrderCode=@poc
    IF(@PurchaseOrderId IS NOT NULL)
    BEGIN    
        set @DetailCursor = CURSOR LOCAL for select PurchaseOrderDetailId from purchase20150427.dbo.t_d_PurchaseOrder_Detail    
                                            where PurchaseOrderId=@PurchaseOrderId ORDER BY DisplayIndex        
        open @DetailCursor
        SET @EBELP=10
        fetch next from @DetailCursor into @PurchaseOrderDetailId
        WHILE(@@FETCH_STATUS=0)    
        BEGIN        
            set @ShareCursor = CURSOR LOCAL for SELECT ExpenseShareId from purchase20150427.dbo.t_d_PurchaseOrder_ExpenseShare            
                                                where PurchaseOrderDetailId = @PurchaseOrderDetailId order by DisplayIndex
            open @ShareCursor
            fetch next from @ShareCursor into @ExpenseShareId
            WHILE(@@FETCH_STATUS=0)
            BEGIN
                 
                UPDATE SAPMiddleDB.dbo.SAP_PurchaseOrder_Item
                SET DetailId=@PurchaseOrderDetailId,ExpenseShareId=@ExpenseShareId
                WHERE PurchaseOrderCode=@poc AND EBELP=@EBELP
                
                /*
                SET @sql='select PurchaseOrderCode,EBELP,DetailId,ExpenseShareId from SAPMiddleDB.dbo.SAP_PurchaseOrder_Item WHERE PurchaseOrderCode='                
                +CAST(@poc AS NVARCHAR(10)) +' AND EBELP='+CAST(@EBELP AS NVARCHAR(10))
                PRINT @sql
                
                SET @sql='UPDATE SAPMiddleDB.dbo.SAP_PurchaseOrder_Item SET DetailId='''
                +@PurchaseOrderDetailId+''',ExpenseShareId='''
                +@ExpenseShareId+''' WHERE PurchaseOrderCode='
                +CAST(@poc AS NVARCHAR(10)) +' AND EBELP='
                +CAST(@EBELP AS NVARCHAR(10))                
                PRINT @sql
                */
                
                
                set @EBELP =  @EBELP + 10             
                FETCH NEXT FROM @ShareCursor into @ExpenseShareId
                
            END            
            close @ShareCursor
            deallocate @ShareCursor
            fetch next from @DetailCursor into @PurchaseOrderDetailId
        END        
        CLOSE @DetailCursor
        DEALLOCATE @DetailCursor
    END
    
    ------------------------------------------------------------------------------------------------------------------

    
    FETCH NEXT FROM OrderCursor INTO @poc
END
CLOSE OrderCursor
DEALLOCATE OrderCursor

 

转载于:https://www.cnblogs.com/daixingqing/p/4505303.html