sql局部游标

delare my_cursor cursor local for select * from table

fetch next from my_cursor into @test,@test2 

 

//for example

ALTER              procedure  Test
as

declare my_cursor  cursor local --scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）
for
select workerid,count(1) tep from worker group by workerid
order by count(1)
 open my_cursor-----全部大客户电话号码
declare @workerid sysname
declare @mycount int
declare @count int
declare @more int
declare @sql varchar(2000)
set @count=(select count(*) from powercustomer)/(select count(distinct workerid) from worker)
set @more=(select count(*) from powercustomer)%(select count(distinct workerid) from worker)

begin
truncate table temptable
insert into temptable(phonenum) select distinct phonenum from powercustomer where phonenum not in (select distinct phonenum from worker)

end
begin
fetch next from my_cursor into @workerid,@mycount
while(@@fetch_status=0)
begin
print @more
if @more>0
begin
   set @sql='update temptable set workerid='+@workerid+' where phonenum in (select top '+Convert(varchar,(@count-@mycount)+1)+' phonenum from temptable where workerid is  null)'
   set @more=@more-1
end

else if @more=0
begin
   set @sql='update temptable set workerid='+@workerid+' where phonenum in (select top '+Convert(varchar,@count-@mycount)+' phonenum from temptable where  workerid is  null)'
end
exec(@sql)
--commit
fetch next from my_cursor into @workerid,@mycount
end
close my_cursor
end