mssql2000游标循环次数对不上

Question

CREATE proc usp_ins_requisite_sc @bill char(16),@stateman varchar(50)  
as  
begin  
  
declare @rowno char(8),
    @row_id char(8),
    @bombill varchar(50)

   declare temp_cursor cursor for   
    select distinct(y.bombill),isnull(y.row_id,''),isnull(y.rowno,'') from vw_salebill x,t_bombill y 
    where x.bill=@bill and x.itemcode=substring(y.fatherbill,1,10) and y.workname is not null   and y.itemcode like '7%'  and y.workname not in ('06','01')  and isnull(y.type,'1') <> '0'

    open temp_cursor  
    fetch next from temp_cursor into @bombill,@row_id,@rowno 
 --   while @@fetch_status = 0  
    begin
    
--    select @fatherbill = x.itemcode from vw_salebill x,t_bombill y 
--    where x.bill=@bill and x.itemcode=y.fatherbill and y.itemtype='1'   

    if @row_id = ''
begin
    
  insert into t_requisite_sc 
   (salebill,orderbill,custcode,custname,rundate,senddate,  
    prodcode,oldprodcode,prodname,prodmodel,  
    prodstandards,prodnum,wdep,odep,cdep,  
    fnum,sfanum,prepnum,itemcode,oldcode,  
    itemnum,workstep,applynum,plannum,type,state,billman,billdate,remark,cremark,bomunit,bomnum,color,cloth,wstuff,gild,workname,stuff,outdate,unitcode)  
  select  
    x.bill+x.rowno,x.orderbill,x.cust_code,x.cust_name,x.statedate,x.senddate1,  
    x.itemcode,x.oldcode,x.itemname,x.itemmodel,  
    x.standards,x.itemnum,x.wdep,x.odep,x.cdep,  
    x.fnum,x.sfanum,x.prepnum,y.itemid,y.itemcode,  
    x.itemnum*y.itemnum,y.workstep,x.itemnum*y.itemnum,0,'1','0',@stateman,getdate(),x.remark,dbo.uf_getcremark(itemid) ,
    y.bomunit,y.bomnum,dbo.uf_getcolor(y.itemid),dbo.uf_getcloth(y.itemid),x.wstuff,x.gild, 
    CASE y.workname WHEN '04'  THEN 'D021' WHEN '05'  THEN 'D022'  WHEN '06'  THEN 'D017'  WHEN '16'  THEN 'D018'  WHEN '18'  THEN 'D019'   WHEN '20'  THEN 'D020'   WHEN '25'  THEN 'D043'   WHEN '30'  THEN 'D024'   WHEN '01'  THEN '0000'   WHEN '54'  THEN 'D054'    WHEN '55'  THEN 'D055'    WHEN '56'  THEN 'D056'    WHEN '57'  THEN 'D057'   end,stuff,outdate,y.unitcode
    from vw_salebill x,t_bombill y  
    where x.bill=@bill
    and x.itemcode=substring(y.fatherbill,1,10)  and y.workname is not null   and y.itemcode like '7%'    and y.workname not in ('06')  and isnull(y.type,'1') <> '0'
 --   and substring(bombill,1,13) in (select  y.bombill from vw_salebill x,t_bombill y where x.bill=@bill and substring(y.fatherbill,1,10) like x.itemcode and produce_type='2'  and y.itemcode like '7%' )

 end

    if @rowno = ''
begin

  insert into t_requisite_sc
   (salebill,orderbill,custcode,custname,rundate,senddate,  
    prodcode,oldprodcode,prodname,prodmodel,  
    prodstandards,prodnum,wdep,odep,cdep,  
    fnum,sfanum,prepnum,itemcode,oldcode,  
    itemnum,workstep,applynum,plannum,type,state,billman,billdate,remark,cremark,bomunit,bomnum,color,cloth,wstuff,gild,workname,stuff,outdate,unitcode)  
  select  
    x.bill+x.rowno,x.orderbill,x.cust_code,x.cust_name,x.statedate,x.senddate1,  
    x.itemcode,x.oldcode,x.itemname,x.itemmodel,  
    x.standards,x.itemnum,x.wdep,x.odep,x.cdep,  
    x.fnum,x.sfanum,x.prepnum,y.itemid,y.itemcode,  
    x.itemnum*y.itemnum,y.workstep,x.itemnum*y.itemnum,0,'1','0',@stateman,getdate(),x.remark,dbo.uf_getcremark(itemid) ,
    y.bomunit,y.bomnum,dbo.uf_getcolor_p(y.itemid),dbo.uf_getcloth(y.itemid),x.wstuff,x.gild, 
    CASE y.workname WHEN '04'  THEN 'D021' WHEN '05'  THEN 'D022'  WHEN '06'  THEN 'D017'  WHEN '16'  THEN 'D018'  WHEN '18'  THEN 'D019'   WHEN '20'  THEN 'D020'   WHEN '25'  THEN 'D043'   WHEN '30'  THEN 'D024'   WHEN '01'  
THEN '0000'   WHEN '54'  THEN 'D054'    WHEN '55'  THEN 'D055'    WHEN '56'  THEN 'D056'    WHEN '57'  THEN 'D057'  end,stuff,outdate,y.unitcode
    from vw_salebill x,t_bombill y  
    where x.bill=@bill  
    and x.itemcode=substring(y.fatherbill,1,10)    and y.workname is not null  and y.itemcode like '7%'   and y.workname not in ('06')  and isnull(y.type,'1') <> '0'-- and produce_type<>'2'
--        and substring(bombill,1,13) in (select  y.bombill from vw_salebill x,t_bombill y where x.bill=@bill and substring(y.fatherbill,1,10) like x.itemcode and produce_type='2'  and y.itemcode like '7%' )

end

    fetch next from temp_cursor into @bombill,@row_id,@rowno 
    end  
    close temp_cursor  
    deallocate temp_cursor     
end  

祖传存储过程，mssql2000，这玩意游标实际查询的时候90条以上的数据，@row_id全部为null，@rowno全部不为null，但是游标实际只执行了一次，是不是我哪里看漏了，不然90条的记录游标只循环一次我无法理解啊。

Answer 1

🕒下午好，题主！目前：🟢[在线]
📢参考通义千问、由郭老师整理提供！
📣今日诗号：有一美人兮，见之不忘。 一日不见兮，思之如狂。

你的问题在于 while 循环条件写错了，导致只循环了一次。

问题所在:

你当前的代码中，while 循环的条件是注释掉的 -- while @@fetch_status = 0，这意味着循环体内的代码只会执行一次，然后就跳出循环了。

解决方案:

1. 删除注释: 将 -- while @@fetch_status = 0 前面的注释符号 -- 去掉，让 while 循环生效。
2. 移动 fetch next: 将 fetch next from temp_cursor into @bombill,@row_id,@rowno 这行代码移动到 while 循环的末尾。

修改后的代码:

CREATE proc usp_ins_requisite_sc @bill char(16),@stateman varchar(50)  as  
begin    
  declare @rowno char(8),
          @row_id char(8),
          @bombill varchar(50)  

  declare temp_cursor cursor for       
  select distinct(y.bombill),isnull(y.row_id,''),isnull(y.rowno,'') 
  from vw_salebill x,t_bombill y     
  where x.bill=@bill 
    and x.itemcode=substring(y.fatherbill,1,10) 
    and y.workname is not null   
    and y.itemcode like '7%'  
    and y.workname not in ('06','01')  
    and isnull(y.type,'1') <> '0'    

  open temp_cursor      
  fetch next from temp_cursor into @bombill,@row_id,@rowno  
  
  while @@fetch_status = 0     
  begin    
    -- ... 循环体内的代码 ...

    fetch next from temp_cursor into @bombill,@row_id,@rowno     
  end      

  close temp_cursor      
  deallocate temp_cursor     
end

解释:

• @@fetch_status 是一个系统变量，用于指示 fetch 语句的执行状态。当 fetch 成功获取下一行数据时，@@fetch_status 的值为 0；否则，它将返回一个非零值。
• 将 fetch next 语句放在循环末尾，可以确保每次循环迭代都获取游标的下一行数据，从而正确地遍历所有结果集。

注意:

• 尽量避免在 SQL Server 中使用游标，因为它们会影响性能。如果可能的话，尝试使用基于集合的操作来代替游标。
• 以上代码仅供参考，请根据实际情况进行调整。