最近使用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--注销游标