MS SQL Server2005存储过程、游标、游标嵌套综合事例

create proc decisionPathRefSchemesasbegin	DECLARE @pathId varchar(64)	DECLARE @schemeId varchar(64)	DECLARE @flag integer	DECLARE @seq integer	DECLARE mycursor cursor  		for	select id from imps_pd_decision_path where surfaceType = '沥青路面' and roadGrade = '高速、一级、二级'	DECLARE mycursor2 cursor  		for	select id from imps_pd_decision_scheme 			set @seq = 10000		open mycursor 	fetch next from mycursor into @pathId	while @@fetch_status=0	begin 		set @flag = 0		select @flag=1 from imps_pd_path_ref_scheme where [email protected]		print @pathId		print @flag		if @flag = 0 		begin			/*使用游标嵌套*/			open mycursor2			fetch next from mycursor2 into @schemeId			while @@fetch_status=0			begin				insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,@schemeId)				set @seq = @seq + 1					fetch next from mycursor2 into @schemeId			end			close mycursor2						/*			不用游标时：			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6cd457b0001')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6ce234a0003')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6cf087c0005')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6cf4b230006')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6cf95aa0007')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6cfd5920008')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d023160009')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d06b4b000a')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d2c87a000b')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d2f900000c')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d32cf1000d')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d36bef000e')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d3ce44000f')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d419b50010')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52ad6a9c0012ad6d541bc0013')			set @seq = @seq + 1			insert into imps_pd_path_ref_scheme (id,pathId,schemeId) values (convert(varchar(64), @seq),@pathId,'8a819be52afa3280012afae7baaa0001')			set @seq = @seq + 1			*/		end		fetch next from mycursor into @pathId	end	/* 嵌套的游标先销毁*/	deallocate mycursor2	close mycursor	/* 外层游标后销毁*/	deallocate mycursorend/* 执行存储过程*/--exec decisionPathRefSchemes/* 删除存储过程*/--drop proc decisionPathRefSchemes