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

数据库  /  管理员 发布于 5年前   194

/************************************************************  * 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