MS SQL Server2005存储过程、游标、游标嵌套综合例子：
放在这里备忘》》》
Java代码
- 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
分类:
SQL
本文转自左正博客园博客，原文链接：http://www.cnblogs.com/soundcode/archive/2012/06/07/2540292.html
，如需转载请自行联系原作者
