SQL 游标使用

　　　　　　　　　　　　

最近使用ＳＱＬ用到了游标，哈哈，总结一下
　　　　　　　　　　　　
declare @postName varchar(100);
declare @postNumDel varchar(100);
declare @postNumDe2 varchar(100);
declare @postNumDe3 varchar(100);
declare @upatePostIDY varchar(100);
declare @upatePostID varchar(100);
declare @upateID varchar(100);
declare @postNum int;
declare @postID int;
declare @No1ID varchar(100);
declare setnum cursor for

  --     查询 jc_PostName表中 name列有重复的数据
	select distinct Name from jc_PostName group by Name having count(Name)>1--声明游标
	 open setnum--打开游标
		fetch next from setnum into @postName --游标赋值给@postName
		while (@@fetch_status=0)--循环，当读取完数据时@@fetch_status为0
		  begin--执行循环
			   set @postNum=
			   (select count(*) from jc_Employee where post like '%,'+@postName+',%' or post like @postName+',%' or post in ( @postName));
                  if(@postNum=0)--员工表中没有使用的职位
					  begin--开始IF
						 declare setEm cursor for 
						  select id from jc_PostName where Name=@postName;--游标声明
						  open setEm  --打开游标
							   fetch next from setEm into @postID  --读取游标赋值给@postID
							   while (@@fetch_status=0)--循环游标
								   begin--开始执行循环
					                -- delete jc_PostName where id=@postID
                                     fetch next from setEm into @postID --读取下一条数据并赋值
			                      end--结束循环
						   close setEm
					     deallocate  setEm
                        ---直接执行删除
					  end  --结束IF
				  if (@postNum>0)--员工表中使用的职位
				   begin 
                     declare postNumDel cursor for 
                        select id, PostId from jc_Employee where post like '%,'+@postName+',%'
                        open   postNumDel 
							fetch next from postNumDel into @upateID,@upatePostIDY
							while (@@fetch_status=0)
								  begin
									  declare No1ID cursor for 
										 select id from jc_PostName where Name=@postName
											 open No1ID
											   fetch next from No1ID into @No1ID	---得到第一条数据
											 close No1ID                                                      
									   deallocate No1ID

--                                                stuff（原字符串，起始位置 a，从起始位置a 要删除长度，要插入的字符串）
                                   set @upatePostID=stuff(@upatePostIDY,charindex(','+@No1ID+',',@upatePostIDY)+1,len(','+@No1ID+',')-2,@No1ID)
                                   update jc_Employee set PostId=@upatePostID where id=@upateID
								   fetch next from postNumDel into @upateID,@upatePostIDY
								 end
                       close postNumDel
                      deallocate postNumDel
						declare postNumDe2 cursor for 
							select id,postID from jc_Employee where post like  @postName+',%'
							open   postNumDe2 
                            fetch next from postNumDe2 into @upateID,@upatePostIDY
                            while (@@fetch_status=0)
								  begin
									   declare No1ID cursor for 
										 select id from jc_PostName where Name=@postName
											 open No1ID
                                               fetch next from No1ID into @No1ID	---得到第一条数据
                                             close No1ID                                                      
									   deallocate No1ID
--                                                          将相同职位编号统一修改为该职位的一个编号
                                      set @upatePostID=stuff(@upatePostIDY,1,charindex(',',@upatePostIDY)-1,@No1ID)
                                       --把修改该条权限ID
                                      update jc_Employee set PostId=@upatePostID where id=@upateID
                                       fetch next from postNumDe2 into @upateID,@upatePostIDY
								 end
							close postNumDe2
						
						 deallocate postNumDe2
                        declare postNumDe3 cursor for 

                     ---      返回多个自动用法
                            select id , PostId from jc_Employee where  post like @postName
                            open   postNumDe3 
                            fetch next from postNumDe3 into @upateID,@upatePostIDY
                             while (@@fetch_status=0)
                              begin
							   declare No1ID cursor for 
								 select id from jc_PostName where Name=@postName
									 open No1ID 
									  fetch next from No1ID into @No1ID
                                      close No1ID
							   deallocate No1ID
                           --把修改该条权限ID
                           update jc_Employee set PostId=@No1ID where id=@upateID
                          fetch next from postNumDe3 into @upateID,@upatePostIDY
                         end
                     close postNumDe3
                   deallocate postNumDe3
			     end
              --删除多余的职位
			  delete jc_PostName where id not in (22,138,100,57,46,200,164,190)  and Name=@postName		 
			 fetch next from setnum into @postName --读取下一条数据并赋值
		  end--结束循环
  close setnum--关闭游标
deallocate  setnum--注销游标