Sql Server使用cursor处理重复数据过程详解

/************************************************************
* Code formatted by setyg
* Time: 2014/7/29 10:04:44
************************************************************/

CREATE PROC HandleEmailRepeat
AS  
DECLARE email CURSOR  
FOR
 SELECT e.email
    ,e.OrderNo
    ,e.TrackingNo
 FROM  Email20140725 AS e
 WHERE e.[status] = 0
 ORDER BY
     e.email
    ,e.OrderNo
    ,e.TrackingNo

BEGIN
 DECLARE @@email       VARCHAR(200)
     ,@firstEmail     VARCHAR(200)
     ,@FirstOrderNO    VARCHAR(300)
     ,@FirstTrackingNO   VARCHAR(300)
     ,@NextEmail      VARCHAR(200)
     ,@@orderNO      VARCHAR(300)
     ,@NextOrderNO     VARCHAR(50)
     ,@@trackingNO     VARCHAR(300)
     ,@NextTrackingNO   VARCHAR(50)

BEGIN
   OPEN email;
   FETCH NEXT FROM email INTO @firstEmail,@FirstOrderNO, @FirstTrackingNO;
   FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO;
   IF @NextEmail!=@firstEmail
   BEGIN
     INSERT INTO Email20140725Test
      (
       email
       ,OrderNo
       ,TrackingNo
      )
     VALUES
      (
       @firstEmail
       ,@FirstOrderNO
       ,@FirstTrackingNO
      );  
     SET @@email = @NextEmail;
     SET @@orderNO = @NextOrderNO;
     SET @@trackingNO = @NextTrackingNO;
   END
   ELSE
   BEGIN
     SET @@email = @NextEmail;
     SET @@orderNO = @FirstOrderNO+'、'+@NextOrderNO;
     SET @@trackingNO = @FirstTrackingNO+'、'+@NextTrackingNO;
   END

FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO,@NextTrackingNO
   WHILE @@fetch_status=0
   BEGIN
     IF @NextEmail=@@email
     BEGIN
       IF (@NextOrderNO!=@@orderNO)
         SET @@orderNO = @@orderNO+'、'+@NextOrderNO

PRINT 'orderNO:'+@@orderNO  

IF (@@trackingNO!=@NextTrackingNO)
         SET @@trackingNO = @@trackingNO+'、'+@NextTrackingNO

PRINT 'trackingNO：'+@@trackingNO
     END
     ELSE
     BEGIN
       INSERT INTO Email20140725Test
        (
         email
         ,OrderNo
         ,TrackingNo
        )
       VALUES
        (
         @@email
         ,@@orderNO
         ,@@trackingNO
        );
       SET @@email = @NextEmail;
       SET @@orderNO = @NextOrderNO;
       SET @@trackingNO = @NextTrackingNO;
     END
     FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO;
   END
   CLOSE email; --关闭游标
   DEALLOCATE email; --释放游标
 END
END