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

放在这里备忘》》》

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