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