游标实例

USE [NewFp_ZhenZhou]
GO

/****** Object:  StoredProcedure [dbo].[code_contrastBycm3andicd]    Script Date: 2018/05/30 10:51:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[code_contrastBycm3andicd]
AS
BEGIN
declare @errormsg nvarchar(max)
declare @id int
declare @name nvarchar(500)
declare @code nvarchar(50)
declare @lccode nvarchar(50)
declare @lcname nvarchar(500)
declare @SourceSql1 nvarchar(50)--分割‘+’之前的字符串
declare @SourceSql2 nvarchar(50)--分割‘+’之后的字符串
BEGIN TRY
	Begin TRAN 
----处理手术编码名称不一致
     print '开始处理手术编码名称不一致' 
       DECLARE contact_cursor  CURSOR FOR
			--select vb.bingah,vb.ZHUYCSH,vb.HIS_KEYNO from  tmp_brzyxx vb 
			     select * from cm3dz_code
          open contact_cursor 
         FETCH NEXT FROM contact_cursor into @id,@code,@name,@lccode,@lcname

         print '开始处理:' 
         while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
           begin
             set @errormsg='update bingrenshoushuxinxi set d_cm3_code='''+@lccode+''',d_cm3_name='''+@lcname+''' where d_cm3_code='''+@code+''' and d_cm3_name='+@name+''''
             update bingrenshoushuxinxi set d_cm3_code=@lccode,d_cm3_name=@lcname where d_cm3_code=@code and d_cm3_name=@name
             fetch next from contact_cursor   into @id,@code,@name,@lccode,@lcname --移动游标
           end 

	     CLOSE contact_cursor  
	     DEALLOCATE contact_cursor  

----处理手术编码一致名称不一致
     print '开始处理手术编码一致名称不一致' 
       DECLARE contact_cursor_cm3code  CURSOR FOR
			--排出在 手术编码名称不一致 处理过的数据
			     select * from cm3dz_name where id not in(select id from cm3dz_name where lccode+lcname in(select lccode+lcname from cm3dz_code))
          open contact_cursor_cm3code
         FETCH NEXT FROM contact_cursor_cm3code into @id,@code,@name,@lccode,@lcname

         print '开始处理:'
         while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
           begin
             set @errormsg='update bingrenshoushuxinxi set d_cm3_code='''+@lccode+''',d_cm3_name='''+@lcname+''' where d_cm3_code='''+@code+''' and d_cm3_name='''+@name+''''
             update bingrenshoushuxinxi set d_cm3_code=@lccode,d_cm3_name=@lcname where d_cm3_code=@code and d_cm3_name=@name
             fetch  next from contact_cursor_cm3code   into @id,@code,@name,@lccode,@lcname --移动游标
           end 

	     CLOSE contact_cursor_cm3code  
	     DEALLOCATE contact_cursor_cm3code
  
--处理手术编码不一致名称一致
     print '开始处理手术编码不一致名称一致' 
       DECLARE contact_cursor_1  CURSOR FOR
			--
			     select * from cm3dz
          open contact_cursor_1
         FETCH NEXT FROM contact_cursor_1 into @id,@code,@name

         print '开始处理:'
         while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
           begin
             select @lccode=code from d_cm3 where name=@name
             set @errormsg='update bingrenshoushuxinxi set d_cm3_code='''+@lccode+''' where d_cm3_name='''+@name+''''
			 update bingrenshoushuxinxi set d_cm3_code=@lccode where d_cm3_name=@name
             fetch  next from contact_cursor_1   into @id,@code,@name --移动游标
           end 
	     CLOSE contact_cursor_1  
	     DEALLOCATE contact_cursor_1  

----处理疾病编码一致名称不一致
      print '开始处理疾病编码一致名称不一致' 
       DECLARE contact_cursor_icdcode  CURSOR FOR
			--select vb.bingah,vb.ZHUYCSH,vb.HIS_KEYNO from  tmp_brzyxx vb 
			     select * from icddz_code
          open contact_cursor_icdcode 
         FETCH NEXT FROM contact_cursor_icdcode into @id,@code,@name,@lccode,@lcname

         print '开始处理:'
         while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
		  begin
		      set @errormsg='update BingRenMenZhenZhenDuan set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
         
		     if(CHARINDEX('+',@lccode)>0)
			   begin
                  SET @SourceSql1 = substring(@lccode,1,charindex('+',@lccode))
                  SET @SourceSql2 = substring(@lccode,charindex('+',@lccode)+1,LEN(@lccode))
                  set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@SourceSql1+''',d_icd_icd_s='''+@SourceSql2+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@SourceSql1,d_icd_icd_s=@SourceSql2,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end 
			 else
			   begin
			     set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
			   end
            update BingRenMenZhenZhenDuan set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
            fetch  next from contact_cursor_icdcode   into @id,@code,@name,@lccode,@lcname --移动游标


			coloe next from contact_cursor_icdcode into 
		   end 
	     CLOSE contact_cursor_icdcode  
	     DEALLOCATE contact_cursor_icdcode 

----处理疾病编码名称不一致
      print '开始处理疾病编码名称不一致' 
       DECLARE contact_cursor_icdname  CURSOR FOR
			--排出在 手术编码名称不一致 处理过的数据
			     select * from icddz_name where id not in(select id from icddz_name where lccode+lcname in(select lccode+lcname from cm3dz_code))
          open contact_cursor_icdname 
         FETCH NEXT FROM contact_cursor_icdname into @id,@code,@name,@lccode,@lcname

         print '开始处理:'
         while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
           begin
		     set @errormsg='update BingRenMenZhenZhenDuan set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
             if(CHARINDEX('+',@lccode)>0)
			   begin
                  SET @SourceSql1 = substring(@lccode,1,charindex('+',@lccode))
                  SET @SourceSql2 = substring(@lccode,charindex('+',@lccode)+1,LEN(@lccode))
                  set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@SourceSql1+''',d_icd_icd_s='''+@SourceSql2+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@SourceSql1,d_icd_icd_s=@SourceSql2,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end 
			 else
			   begin
			     set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
			   end
             update BingRenMenZhenZhenDuan set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
             fetch  next from contact_cursor_icdname   into @id,@code,@name,@lccode,@lcname --移动游标
           end 
	     CLOSE contact_cursor_icdname  
	     DEALLOCATE contact_cursor_icdname 
--处理编码不一致名称一致
      print '开始处理编码不一致名称一致' 
       DECLARE contact_cursor_2  CURSOR FOR
			--
			     select * from icddz
          open contact_cursor_2 
         FETCH NEXT FROM contact_cursor_2 into @id,@code,@name,@lccode,@lcname

         print '开始处理:' 
         while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
           begin
		     set @errormsg='update BingRenMenZhenZhenDuan set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
             if(CHARINDEX('+',@lccode)>0)
			   begin
                  SET @SourceSql1 = substring(@lccode,1,charindex('+',@lccode))
                  SET @SourceSql2 = substring(@lccode,charindex('+',@lccode)+1,LEN(@lccode))
                  set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@SourceSql1+''',d_icd_icd_s='''+@SourceSql2+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@SourceSql1,d_icd_icd_s=@SourceSql2,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end 
			 else
			   begin
			     set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
			   end
             update BingRenMenZhenZhenDuan set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
             fetch  next from contact_cursor_2   into @id,@code,@name,@lccode,@lcname --移动游标
           end 
	     CLOSE contact_cursor_2  
	     DEALLOCATE contact_cursor_2 

        commit TRAN
     PRINT '处理成功';
    END TRY
	BEGIN CATCH    
		rollback TRAN
		PRINT '处理ERROR:'+@errormsg; 		                  
	END CATCH
END
GO