create procedure c_dzh_assignOwner_Distribution(@handleby char(6))
as
begin
declare @peo_staff char(6),@peo_assigncount int,@peo_rolegroup char(6),@peo_objectivetype char(6),@midvar1 int,
@customer_guid char(36),@newguid char(36),@custlists varchar(100),@sql nvarchar(500)
declare cur_sor1 cursor for select cur_staff,assigncount,cur_rolegroup,objectivetype_id from c_dzh_assigninfo
where handleby=@handleby
open cur_sor1 fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype
while @@fetch_status=0
set @midvar1=0
begin
declare cur_sor2 cursor for select customer_guid from c_dzh_Owner_Distribution where handleby=@handleby
open cur_sor2 fetch next from cur_sor2 into @customer_guid
while @@fetch_status=0
begin
select @newguid=newid()
insert into objective
(objective_guid,objectivetype_id,handleby_id,handlegroup_id,expireddate,
customer_guid,objectivestatus,createdby,createddate,modifiedby,modifieddate)
values
(@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup,'2050-01-01 00:00:00',
@customer_guid,'OPEN',@handleby,getdate(),@handleby,getdate())
insert into oblist
(oblist_guid,objective_guid,objectivetype_id,expectstarttime,expectendtime,obstatus,max_attempt,handleby_id,handlegroup_id,createdby,createddate,modifiedby,modifieddate)
values
(newid(),@newguid,@peo_objectivetype,getdate(),'2050-01-01 00:00:00','OPEN','5000',@peo_staff,@peo_rolegroup,@handleby,getdate(),@handleby,getdate())
update c_dzh_Owner_Distribution set flag=1 where customer_guid=@customer_guid and handleby=@handleby and flag=0
update customer set owner=@peo_staff where customer_guid=@customer_guid
set @sql='update '''+@custlists+''' set flagassign=1 where customer_guid='''+@customer_guid+''''
exec sp_executesql @sql
set @midvar1=@midvar1+1
fetch next from cur_sor2 into @customer_guid
end
if(@midvar1=@peo_assigncount)
begin
fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype
end
end
close cur_sor1
deallocate cur_sor1
close cur_sor2
deallocate cur_sor2
end 针对上面的存储过程,想在第二个游标移动取数据的时候,先判断第一个游标移动获取的handleby
的上线量和现有数据量,如果现有数据量大于等于上线量则第二个游标不需要循环移动了,跳出第二个
游标,继续移动fetch第一个游标的handleby,然后再判断这个handleby的上线量和现有数据量
如果现有数据量小于上线量则按照现有循环第二个游标取数据,再插入数据。只是增加一个权限控制。
具体的handleby话务员的上线量和现有数据量的sql语句是
select @limitedcount=max(limited) 上线量,@totalownercount=count(owner) 现有数据量 from customer c left join autoright auto1
on c.owner=auto1.staff_id
where auto1.staff_id=@peo_staff
然后进行判断if(@limitedcount <=@totalownercount)则第二个游标不移动了,跳出来,去取第一个游标的下一个记录
我不知道这个怎么加在第二个游标里面。请大家帮忙,感谢大家。
as
begin
declare @peo_staff char(6),@peo_assigncount int,@peo_rolegroup char(6),@peo_objectivetype char(6),@midvar1 int,
@customer_guid char(36),@newguid char(36),@custlists varchar(100),@sql nvarchar(500)
declare cur_sor1 cursor for select cur_staff,assigncount,cur_rolegroup,objectivetype_id from c_dzh_assigninfo
where handleby=@handleby
open cur_sor1 fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype
while @@fetch_status=0
set @midvar1=0
begin
declare cur_sor2 cursor for select customer_guid from c_dzh_Owner_Distribution where handleby=@handleby
open cur_sor2 fetch next from cur_sor2 into @customer_guid
while @@fetch_status=0
begin
select @newguid=newid()
insert into objective
(objective_guid,objectivetype_id,handleby_id,handlegroup_id,expireddate,
customer_guid,objectivestatus,createdby,createddate,modifiedby,modifieddate)
values
(@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup,'2050-01-01 00:00:00',
@customer_guid,'OPEN',@handleby,getdate(),@handleby,getdate())
insert into oblist
(oblist_guid,objective_guid,objectivetype_id,expectstarttime,expectendtime,obstatus,max_attempt,handleby_id,handlegroup_id,createdby,createddate,modifiedby,modifieddate)
values
(newid(),@newguid,@peo_objectivetype,getdate(),'2050-01-01 00:00:00','OPEN','5000',@peo_staff,@peo_rolegroup,@handleby,getdate(),@handleby,getdate())
update c_dzh_Owner_Distribution set flag=1 where customer_guid=@customer_guid and handleby=@handleby and flag=0
update customer set owner=@peo_staff where customer_guid=@customer_guid
set @sql='update '''+@custlists+''' set flagassign=1 where customer_guid='''+@customer_guid+''''
exec sp_executesql @sql
set @midvar1=@midvar1+1
fetch next from cur_sor2 into @customer_guid
end
if(@midvar1=@peo_assigncount)
begin
fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype
end
end
close cur_sor1
deallocate cur_sor1
close cur_sor2
deallocate cur_sor2
end 针对上面的存储过程,想在第二个游标移动取数据的时候,先判断第一个游标移动获取的handleby
的上线量和现有数据量,如果现有数据量大于等于上线量则第二个游标不需要循环移动了,跳出第二个
游标,继续移动fetch第一个游标的handleby,然后再判断这个handleby的上线量和现有数据量
如果现有数据量小于上线量则按照现有循环第二个游标取数据,再插入数据。只是增加一个权限控制。
具体的handleby话务员的上线量和现有数据量的sql语句是
select @limitedcount=max(limited) 上线量,@totalownercount=count(owner) 现有数据量 from customer c left join autoright auto1
on c.owner=auto1.staff_id
where auto1.staff_id=@peo_staff
然后进行判断if(@limitedcount <=@totalownercount)则第二个游标不移动了,跳出来,去取第一个游标的下一个记录
我不知道这个怎么加在第二个游标里面。请大家帮忙,感谢大家。
break之后
那第一个游标会自动取下一个值吗?