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

sql游标 使用

SQL Cursor 基本用法

sql游标使用