SQL SERVER 游标的使用

 

    ----------------------------------------------------

  -------------------------------------------------------

  --定义两个变量
declare  @name varchar(150),@CustomerID int

--声明一个游标

 

   DECLARE Contract_cursor CURSOR FOR 

 

--此为需要查询的主表

     select CustomerName from V_DE_ClientProtect  group by CustomerName having count(CustomerName)>1

 

--打开游标

OPEN Contract_cursor

  --循环并提取记录
  FETCH NEXT FROM Contract_cursor INTO @name 
  While ( @@Fetch_Status=0 )   
        begin
   select @CustomerID=max(CustomerID) from V_DE_ClientProtect where CustomerName=@name;

 

 

delete DE_ClientProtect where CustomerID in 

(select CustomerID from V_DE_ClientProtect where CustomerName=@name and CustomerID<>@CustomerID)

        Fetch Next From Contract_cursor  Into @name 
      end

   --关闭游标   
  Close Contract_cursor 

   --释放游标
  Deallocate Contract_cursor