请求大家SQL游标的问题

Question

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 
begin 
set @midvar1=0 
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, 
customer_guid)    
  values    
  (@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup, 
@customer_guid)    
insert into oblist    
  (oblist_guid,objective_guid,objectivetype_id,handleby_id,handlegroup_id)    
  values    
  (newid(),@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup)    
    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_sor2 
deallocate cur_sor2 
close cur_sor1 
deallocate cur_sor1 
end 那请问这样写对吗？

Answer 1

貌似关闭游标的位置不对（未测试）
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 
begin 
set @midvar1=0 
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, 
customer_guid)    
  values    
  (@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup, 
@customer_guid)    
insert into oblist    
  (oblist_guid,objective_guid,objectivetype_id,handleby_id,handlegroup_id)    
  values    
  (newid(),@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup)    
    set @midvar1=@midvar1+1 
fetch next from cur_sor2 into @customer_guid 
  end 
  --提到这里
  close cur_sor2 
  deallocate cur_sor2 
  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 
end  
 

Answer 2

谁人都说在CSDN上有众多的高手在，有什么问题都能得到解决，看来并不是这样的啊~ 
CSDN真实让人失望！！

Answer 3

create table c_dzh_assigninfo(cur_staff varchar(6), assigncount int,handleby varchar(6),cur_rolegroup varchar(6),objectivetype_id int,custlists int)
insert into c_dzh_assigninfo select '006999',4,'SELITE',NULL,NULL,NULL union all select
'5BD070',5,'SELITE',NULL,NULL,NULL  union all select
'85612B',3,'SELITE',NULL,NULL,NULL  union all select
'9248EC',3,'SELITE',NULL,NULL,NULL
create table c_dzh_owner_distribution(handleby varchar(6),customer_guid uniqueidentifier,flag int)
insert into c_dzh_owner_distribution select 'SELITE','05668446-F343-F443-9EBC-0F746BD8A07D',0 union all select  
'SELITE','30625BE1-F36A-A84F-A654-615BE2810B66',0 union all select  
'SELITE','38810E94-3995-794D-A60C-B80F7AC67DB3',0 union all select  
'SELITE','39AFB4E3-8003-AE49-83CE-EA5403327A73',0 union all select  
'SELITE','59CD81B4-4CF5-B145-A3C0-01993E4D6A3D',0 union all select  
'SELITE','67CD7B21-FFB5-D748-8139-D7A39B73726D',0 union all select  
'SELITE','6D62A0A3-EE2B-0A44-9B13-95F42511AFE3',0 union all select  
'SELITE','99FC881C-D907-684C-95A9-1898345C92F2',0 union all select  
'SELITE','9DF50072-C859-9449-9659-3F631FD81D32',0 union all select  
'SELITE','AA65D1B3-60BB-B946-929A-4F61A63E9458',0 union all select  
'SELITE','72ECAF9F-A00C-924E-AEE8-36CCF5833290',0 union all select  
'SELITE','B3FB8BC8-C44D-2C48-AEF2-AAF782D35C30',0 union all select  
'SELITE','821E74C3-7DE2-5642-A298-0DCAB85A36A2',0 union all select  
'SELITE','DF1816E6-B9A3-7349-B116-9E5D177463C6',0
create table objective(objective_guid char(36),objectivetype_id char(6),handleby_id char(6),handlegroup_id char(6),customer_guid char(36))
create table oblist(oblist_guid char(36),objective_guid char(36),objectivetype_id char(6),handleby_id char(36),handlegroup_id char(36))
--objective_guid,oblist_guid-----newid()
--handleby_id-----cur_staff
--handlegroup_id-----cur_rolegroup
--objectivetype_id------objectivetype_id
--customer_guid-------customer_guid
go
--直接写成查询的方式,楼主应该能改为存储过程
declare @handleby char(6),@peo_staff varchar(6),@peo_assigncount int,@peo_rolegroup varchar(20),@peo_objectivetype int
declare @customer_guid char(36),@newguid char(36),@custlists varchar(100),@sql nvarchar(500),@midvar1 intset @handleby='SELITE'declare cur_sor1 cursor for select cur_staff,assigncount,cur_rolegroup,objectivetype_id from c_dzh_assigninfo where handleby=@handleby
declare cur_sor2 cursor for select customer_guid from c_dzh_Owner_Distribution where handleby=@handleby 
open  cur_sor1
open cur_sor2
fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
while @@fetch_status=0 
begin 
set @midvar1=0
while @midvar1<@peo_assigncount
begin 
fetch next from cur_sor2 into @customer_guid
if @@fetch_status=0
begin
select @newguid=newid()
insert into objective select @newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup,@customer_guid
insert into oblist select newid(),@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup
end
else
set @midvar1=@peo_assigncount
set @midvar1=@midvar1+1
end
fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
end
close cur_sor2 
deallocate cur_sor2 
close cur_sor1 
deallocate cur_sor1 
select * from objective
select * from oblist
go
drop table c_dzh_assigninfo,c_dzh_owner_distribution,objective,oblist
/*
objective_guid                       objectivetype_id handleby_id handlegroup_id customer_guid
------------------------------------ ---------------- ----------- -------------- ------------------------------------
BE609B09-7326-4407-8926-C3636BCECF4D NULL             006999      NULL           05668446-F343-F443-9EBC-0F746BD8A07D
F16086EF-46A8-4869-871A-67FC8351BDD4 NULL             006999      NULL           30625BE1-F36A-A84F-A654-615BE2810B66
60363B65-BD92-4BC8-92D3-D711AAAD64E0 NULL             006999      NULL           38810E94-3995-794D-A60C-B80F7AC67DB3
86B90D75-61DD-47B7-A7AE-B7BF2864BCE5 NULL             006999      NULL           39AFB4E3-8003-AE49-83CE-EA5403327A73
9A1DB27C-DEDC-4D8A-AE9D-249610C65A03 NULL             5BD070      NULL           59CD81B4-4CF5-B145-A3C0-01993E4D6A3D
DE514BA6-60CB-4DF2-B26A-C719629B277A NULL             5BD070      NULL           67CD7B21-FFB5-D748-8139-D7A39B73726D
243AE852-33BB-4DD9-BB0D-90BE170A8005 NULL             5BD070      NULL           6D62A0A3-EE2B-0A44-9B13-95F42511AFE3
5BDCF531-DC40-44AB-A41D-EEDB8CCCB996 NULL             5BD070      NULL           99FC881C-D907-684C-95A9-1898345C92F2
35877907-2B5C-4876-9C3F-1749CCACA09F NULL             5BD070      NULL           9DF50072-C859-9449-9659-3F631FD81D32
E2D3D858-5CBF-4486-AF4B-6388A0D65FF1 NULL             85612B      NULL           AA65D1B3-60BB-B946-929A-4F61A63E9458
7DFDDA69-90D7-4FA0-BADC-BE4A0CA9F096 NULL             85612B      NULL           72ECAF9F-A00C-924E-AEE8-36CCF5833290
DAA5E9DB-1431-4687-B79B-431DC2B05AA4 NULL             85612B      NULL           B3FB8BC8-C44D-2C48-AEF2-AAF782D35C30
38AD7CAA-1D1A-4CA3-B064-DE959EB30CEE NULL             9248EC      NULL           821E74C3-7DE2-5642-A298-0DCAB85A36A2
75CABA4B-C929-4C03-A508-3575985369BB NULL             9248EC      NULL           DF1816E6-B9A3-7349-B116-9E5D177463C6(14 行受影响)  -----第二个游标只能取到14条记录.oblist_guid                          objective_guid                       objectivetype_id handleby_id                          handlegroup_id
------------------------------------ ------------------------------------ ---------------- ------------------------------------ ------------------------------------
3EFB3342-5331-42D3-8DAB-7031A4BE3865 BE609B09-7326-4407-8926-C3636BCECF4D NULL             006999                               NULL
D0C1FCD3-772B-478B-AE7E-441C8EEFF135 F16086EF-46A8-4869-871A-67FC8351BDD4 NULL             006999                               NULL
BA5CA1CC-EA48-4721-A050-99B47FC12F22 60363B65-BD92-4BC8-92D3-D711AAAD64E0 NULL             006999                               NULL
0D15A2F9-89E8-4255-B9BD-7229203EC5AE 86B90D75-61DD-47B7-A7AE-B7BF2864BCE5 NULL             006999                               NULL
CF98CD74-C576-49D2-8C88-D2761A0A7EB0 9A1DB27C-DEDC-4D8A-AE9D-249610C65A03 NULL             5BD070                               NULL
CD2BF720-6F57-4E8E-81CB-EA8D0E2CDE94 DE514BA6-60CB-4DF2-B26A-C719629B277A NULL             5BD070                               NULL
5D2ADBF6-B39B-4E90-8759-1EDF8B5DA73C 243AE852-33BB-4DD9-BB0D-90BE170A8005 NULL             5BD070                               NULL
F765438A-5510-4B18-A185-786DB21CC6E9 5BDCF531-DC40-44AB-A41D-EEDB8CCCB996 NULL             5BD070                               NULL
1D2B3610-8FE1-4273-B53F-BC954453FB4C 35877907-2B5C-4876-9C3F-1749CCACA09F NULL             5BD070                               NULL
418C7DC7-200E-4325-8C51-1F88C982F749 E2D3D858-5CBF-4486-AF4B-6388A0D65FF1 NULL             85612B                               NULL
D86D262E-418A-41E6-89BD-C3D84DC1FA51 7DFDDA69-90D7-4FA0-BADC-BE4A0CA9F096 NULL             85612B                               NULL
7E3E148B-6A7C-419A-B783-8A8E401BD162 DAA5E9DB-1431-4687-B79B-431DC2B05AA4 NULL             85612B                               NULL
10726629-1AB0-4F32-82E0-DDBEB6907D40 38AD7CAA-1D1A-4CA3-B064-DE959EB30CEE NULL             9248EC                               NULL
4C2108FB-20D3-4FAF-BB82-52DA59D3B05C 75CABA4B-C929-4C03-A508-3575985369BB NULL             9248EC                               NULL(14 行受影响)
*/