sql游标 使用

SQL Cursor 基本用法

 

由于这个游标 执行一下就相当于SELECT一下 其效率不敢恭维也没做深入研究。

 

 1 table1结构如下
 2 id    int
 3 name  varchar(50)
 4 
 5 declare @id int
 6 declare @name varchar(50)
 7 declare cursor1 cursor for         --定义游标cursor1
 8 select * from table1               --使用游标的对象(跟据需要填入select文)
 9 open cursor1                       --打开游标
10 
11 fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
12 
13 while @@fetch_status=0           --判断是否成功获取数据
14 begin
15 update table1 set name=name+'1'
16 where id=@id                           --进行相应处理(跟据需要填入SQL文)
17 
18 fetch next from cursor1 into @id,@name  --将游标向下移1行
19 end
20 
21 close cursor1                   --关闭游标
22 deallocate cursor1 

游标一般格式：
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
WHILE @@FETCH_STATUS=0
        BEGIN
                  SQL语句执行过程... ...
                  FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
        END
CLOSE 游标名称
DEALLOCATE 游标名称 (删除游标)

 

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

---1、临时档案优先删除
---2、无户主删除
---3、删除第一条

DECLARE
        @CoopMedCode VARCHAR(23),
        @IDNO VARCHAR(23),
        @PerRchCode Varchar(23),
        @NewPerRchCode Varchar(23),
        @HomRchCode Varchar(23),
        @I          INT
SET @I = 0

DECLARE  HRHomRchRelaCursor  CURSOR  READ_ONLY  FOR
     select CoopMedCode,IDNo from TData_PerBaseInfo
   WHERE CoopMedCode IS NOT NULL AND CoopMedCode <>''
   group by CoopMedCode, IDNo
   having COUNT(IDNo) > 1
 OPEN  HRHomRchRelaCursor 
 FETCH NEXT FROM HRHomRchRelaCursor  INTO @CoopMedCode,@IDNO
           WHILE @@FETCH_STATUS = 0                  
     BEGIN
     SET @I = @I + 1
      ----临时档案
      if exists(select 1 from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO and RchState = 6)
      begin       
        select @PerRchCode = PerRchCode from TData_PerBaseInfo where
          Perrchcode in (select PerRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO and RchState = 6)          
              
          -----判断非6的居民是否存在
        if exists(select 1 from TData_PerBaseInfo where
          Perrchcode in (select PerRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO and RchState <> 6)  )
        begin
            select @NewPerRchCode = PerRchCode from TData_PerBaseInfo where
                 Perrchcode in (select PerRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO and RchState <> 6)
         
           if @PerRchCode <> '' and @NewPerRchCode <> ''
           begin
            delete from TData_PerGenDis where PerRchCode = @PerRchCode 
      --update TData_PerGenDis set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfoPH set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfo_Surg set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfo_Trau set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfo_Tran set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_MotAntFirstFolup set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HeaChecklist set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_ElderHeaFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PVRVVaccMat set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode 
      update TData_PVRVVaccExp set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PVRVVaccDet set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode

      update TData_PVRVVaccSch set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_InfDisPCIMat set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HypFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_DMFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HolePerBaseInfo set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HoleFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_AdmiRec set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
     
      update TData_ThreeAgeHomVis set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_ThreeAgePhyExa set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
                     
                     update TData_VisHospRecord set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
                     update TData_VisClinicRecord set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
     
      update TData_ThreeAgePhyExa set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      
     ----转移到备份表
      insert into TData_PerBaseInfoBak
       select * from TData_PerBaseInfo where
      Perrchcode in (select PerRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO and RchState = 6)     
          
        delete from TData_PerBaseInfo where PerRchCode = @PerRchCode and CoopMedCode = @CoopMedCode and IDNo = @IDNO
        print('第'+Convert(varchar(23),@I)+'条' + '删除个人编码为：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
      end
        else
      begin
        print('第'+Convert(varchar(23),@I)+'条' + '新旧个人编码一样：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
      end 
      
        end
        else
        begin
            print('第'+Convert(varchar(23),@I)+'条' + '无非临时档案居民信息，无法定位新的居民编号：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
        end 
      end
      else
      begin
         IF object_id('tempdb..#tempHom') is not null
            drop Table #tempHom
         ----查询有户主的家庭                 
         select HomRchCode into #tempHom from TData_PerBaseInfo where
       HomRchCode in ( select distinct HomRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO)
       and RelaOwner = 0
     if exists(select 1 from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO
                     and HomRchCode not in (select HomRchCode from #tempHom))
         begin
            ----删除无户主的居民
            select @HomRchCode = HomRchCode,@PerRchCode = PerRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO
                     and HomRchCode not in (select HomRchCode from #tempHom)
            ----保留的居民信息        
            select @NewPerRchCode =  PerRchCode From TData_PerBaseInfo where
              HomRchCode = (select HomRchCode from #tempHom) and CoopMedCode = @CoopMedCode and IDNo = @IDNO
             
            if @PerRchCode <> '' and @NewPerRchCode <> ''
           begin 
           delete from TData_PerGenDis where PerRchCode = @PerRchCode 
            --update TData_PerGenDis set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfoPH set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfo_Surg set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfo_Trau set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PerBaseInfo_Tran set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_MotAntFirstFolup set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HeaChecklist set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_ElderHeaFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PVRVVaccMat set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode 
      update TData_PVRVVaccExp set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_PVRVVaccDet set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode

      update TData_PVRVVaccSch set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_InfDisPCIMat set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HypFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_DMFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HolePerBaseInfo set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_HoleFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_AdmiRec set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
     
      update TData_ThreeAgeHomVis set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      update TData_ThreeAgePhyExa set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
     
      update TData_VisHospRecord set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
                     update TData_VisClinicRecord set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       
     ----转移到备份表
      insert into TData_PerBaseInfoBak
       select * from TData_PerBaseInfo where
      Perrchcode = @PerRchCode and CoopMedCode = @CoopMedCode and IDNo = @IDNO  
          
        delete from TData_PerBaseInfo where Perrchcode = @PerRchCode and CoopMedCode = @CoopMedCode and IDNo = @IDNO  
        print('第'+Convert(varchar(23),@I)+'条' + '删除个人编码为：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
      end
      else
      begin
        print('第'+Convert(varchar(23),@I)+'条' + '新旧个人编码一样：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
      end
        
         end
         else
         begin
            ----均有户主 删除第一条
            select top 1 @PerRchCode = PerRchCode from TData_PerBaseInfo where CoopMedCode = @CoopMedCode and IDNo = @IDNO
           
            select @NewPerRchCode = PerRchCode from TData_PerBaseInfo where  CoopMedCode = @CoopMedCode and IDNo = @IDNO
              and PerRchCode <> @PerRchCode
             
           if @PerRchCode <> '' and @NewPerRchCode <> ''
           begin
                delete from TData_PerGenDis where PerRchCode = @PerRchCode 
               -- update TData_PerGenDis set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_PerBaseInfoPH set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_PerBaseInfo_Surg set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_PerBaseInfo_Trau set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_PerBaseInfo_Tran set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_MotAntFirstFolup set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_HeaChecklist set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_ElderHeaFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_PVRVVaccMat set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode 
       update TData_PVRVVaccExp set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_PVRVVaccDet set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode

       update TData_PVRVVaccSch set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_InfDisPCIMat set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_HypFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_DMFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_HolePerBaseInfo set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_HoleFolupVisit set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_AdmiRec set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
      
       update TData_ThreeAgeHomVis set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       update TData_ThreeAgePhyExa set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
       
      update TData_VisHospRecord set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
                        update TData_VisClinicRecord set PerRchCode = @NewPerRchCode, PerRchCodeSBak = @PerRchCode where PerRchCode = @PerRchCode
     
       
      ----转移到备份表
       insert into TData_PerBaseInfoBak
        select * from TData_PerBaseInfo where
       Perrchcode = @PerRchCode and CoopMedCode = @CoopMedCode and IDNo = @IDNO  
           
        delete from TData_PerBaseInfo where Perrchcode = @PerRchCode and CoopMedCode = @CoopMedCode and IDNo = @IDNO  
        print('第'+Convert(varchar(23),@I)+'条' + '删除个人编码为：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
           end
           else
           begin
             print('第'+Convert(varchar(23),@I)+'条' + '新旧个人编码一样：'+@PerRchCode+'^'+@CoopMedCode+'^'+@IDNO+'^'+@NewPerRchCode)
           end
      
         end
        
          
      end     
      FETCH NEXT FROM HRHomRchRelaCursor  INTO @CoopMedCode,@IDNO
     END
  CLOSE  HRHomRchRelaCursor            
  DEALLOCATE HRHomRchRelaCursor