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