Sqlserver存储过程及游标的使用

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

drop PROCEDURE remove_repeated_msisdn
go

-- =============================================
-- 处理重复号
-- =============================================
CREATE PROCEDURE [dcms_zhq].remove_repeated_msisdn
AS
BEGIN
	declare @num varchar(30)   
    declare @count bigint  
	
	declare @id bigint
	declare @big_box varchar(30)
	declare @little_box varchar(30)
	declare @zha varchar(30)
	declare @status bigint

	declare c1 cursor local for  
		select msisdn_,count(id_) amount_ from dcms_zhq.SIM_CARD_RESOURCE_
		where is_locked_='N' and unit_type_id_=400 and msisdn_='15119880662'
		group by msisdn_
		having count(id_)>1

	open c1   
    fetch next from c1 into @num,@count
    while (@@fetch_status=0)   
    begin
		--#####################
        declare c2 cursor local for
		select id_,big_box_id_,little_box_id_,zha_id_,status_
		from dcms_zhq.SIM_CARD_RESOURCE_ where msisdn_=@num order by input_date_time_ asc
		
		declare @count2 bigint
		set @count2 = 0
		
		open c2
		fetch next from c2 into @id,@big_box,@little_box,@zha,@status
		while (@@fetch_status=0)
		begin
			set @count2 = @count2 + 1
			if @count2<@count
			begin
				BEGIN TRANSACTION
				update dcms_zhq.SIM_CARD_RESOURCE_ set
					packer_=packer_+'_20100420_'+ltrim(rtrim(str(@count2))),
					msisdn_=msisdn_+'_20100420_'+ltrim(rtrim(str(@count2))),
					life_status_=102
				where id_=@id
				
				if @status=2 or @status=16
				begin
					if(len(@zha)>0) update dcms_zhq.SIM_CARD_RESOURCE_ set amount_=amount_-1 where packer_=@zha
					if(len(@little_box)>0) update dcms_zhq.SIM_CARD_RESOURCE_ set amount_=amount_-1 where packer_=@little_box
					if(len(@big_box)>0) update dcms_zhq.SIM_CARD_RESOURCE_ set amount_=amount_-1 where packer_=@big_box
				end
				
				IF @@ERROR>0
					ROLLBACK TRANSACTION
				else
					COMMIT TRANSACTION
			end
			
			fetch next from c2 into @id,@big_box,@little_box,@zha,@status
		end  
		close c2   
		deallocate c2 

		--#####################     
        fetch next from c1 into @num,@count
    end  
    close c1   
    deallocate c1   

END
GO