MS SQL Server2005存储过程、游标、游标嵌套综合例子-优快云博客

本文提供了一个MSSQL Server 2005的存储过程实例，演示了如何使用游标及游标嵌套来处理数据。通过查询特定条件的道路信息，并根据查询结果插入新的记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[color=blue][b]MS SQL Server2005存储过程、游标、游标嵌套综合例子：
[/b][/color]
放在这里备忘》》》


create proc decisionPathRefSchemes
as
begin
	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 pathId=@pathId
		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 mycursor
end

/* 执行存储过程*/
--exec decisionPathRefSchemes

/* 删除存储过程*/
--drop proc decisionPathRefSchemes