SQL游标用法

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:        陈重煌
-- Create date: 2013-1-21
-- Description:    后台统计-->商品分时段关联管理(智能关联)
-- =============================================
CREATE PROCEDURE [dbo].[procDataAnalysisUnionHoursAuto]
AS 
    BEGIN
            DECLARE @lb_continue BIT--是否还可以循环
            DECLARE @shopID INT--临时存储变量
            DECLARE @numid BIGINT--临时存储变量

            DECLARE CURSOR1 CURSOR FOR SELECT Num_IID,Shop_ID FROM CRM_Item--定义游标
            OPEN CURSOR1
            SET @lb_continue = '1'

            WHILE @lb_continue = '1'
                BEGIN    
            --循环游标
                    FETCH NEXT FROM CURSOR1 INTO @numid,@shopid--商品ID
                    IF @@FETCH_STATUS = 0 
                        BEGIN
-------------------------------【数据开始】---------------------------------------------------------------------------
        --PRINT @numid
        --PRINT @shopID
                            DELETE  FROM CRM_Scientific WHERE   type = 2 AND shopID = @shopID AND NumId = @numid --删除所有的，商品ID，店铺ID，类型为2的数据
                            --会员数：mc:MemberCount此商品此时段会员数,amc:ALlMemberCount此时段所有商品会员数,mp:MmeberPercent会员占比
                            --销量：Num:ProductSaleNum此商品此时段销量,aNum:AllProductSaleNum此时段所有商品销量,np:ProductPercent销量占比
                            
                            INSERT  INTO dbo.CRM_Scientific
                            ( [Hours] ,
                              [Title] ,
                              [RelevanceID] ,
                              [NumberPercent] ,
                              [Number] ,
                              [CustomerNumber] ,
                              [CustomerNumberPercent] ,
                              [Type] ,
                              [ShopID] ,
                              [NumId]
                            ) SELECT [Hours] ,[Title] ,  [RelevanceID] , [np] ,[Number] , [CustomerNumber] ,[mp] ,[DataType] ,[ShopId] ,[NumId]
                                FROM  #Table#
-------------------------------【数据结束】---------------------------------------------------------------------------
                        END
                    ELSE 
                        BEGIN
                            SET @lb_continue = 0 
                        END
                END
            CLOSE CURSOR1
            DEALLOCATE CURSOR1
            
    END