ORACLE 游标丢数的问题

Question

按说不会存在这样的bug的，提供下程序源码吧

Answer 1

PROCEDURE Prc_my_MeiRiJYKC(Prm_Jieyurq    IN DATE) IS
              
    BEGIN
        DECLARE
            d_Jieyurq      DATE; --结余日期
            d_Kaishisj     DATE; --当天的开始时间
            d_Jieshusj     DATE; --当天的结束时间
            n_count        number;
            s_jiageiddgg   varchar2(100);
            n_Rukusl       my_Mingxizhang.Churuksl%TYPE; --入库数量
            n_Chukusl      my_Mingxizhang.Churuksl%TYPE; --出库数量
            n_Qitasl       my_Mingxizhang.Churuksl%TYPE; --其它数量
            Rec_Kucun1     my_Kucun1%ROWTYPE; --库存总量
            Rec_Meirijykc1 my_Meirijykc1%ROWTYPE; --每日结余库存
            REC_Meirijykc2 my_Meirijykc2%ROWTYPE;
            --KuCunJE
            num_ZuiXinLSJ  gy_yaopincdjg2.danjia1%Type; --最新零售价
            num_ChuKuJE    my_meirijykc1.ChuKuJE%Type;  --出库金额
            num_RuKuJE     my_meirijykc1.RuKuJE%Type;    --入库金额
            num_QiTaJE     my_meirijykc1.QiTaJE%Type;    --其他金额
            num_PiFaJia    gy_yaopincdjg2.danjia1%Type;
            num_Kucunsl    my_meirijykc1.kucunsl%Type;
            s_Maxjiageid   gy_yaopincdjg1.jiageid%Type;
            n_jiageid  varchar2(100);
            n_yingyongid  varchar2(100);
            n_zongliangkcid  varchar2(100);
            n_kucunsl number;
            n_yishouwfsl number;
            n_xukucsl number;
            n_linshikcsl number;
            n_yuanquid varchar2(100);
            n_shiyongpl number;
            prm_appcode varchar2(100);
            prm_DataBuffer varchar2(100);
      
                CURSOR Cursor_Kucun1 IS
                SELECT distinct jiageid
                 FROM my_Kucun1 a
                 where yingyongid ='0501'
                 and jiageid = '22905'
                 order by jiageid;                         BEGIN
            d_Jieyurq := Prm_Jieyurq;            --取当天的开始时间
            SELECT To_Date(To_Char(d_Jieyurq, 'yyyy-mm-dd') || ' 00:00:01','YYYY-MM-DD HH24:MI:SS')
              INTO d_Kaishisj
              FROM Dual;            --取当天结束时间
            SELECT To_Date(To_Char(d_Jieyurq, 'yyyy-mm-dd') || ' 23:59:59','YYYY-MM-DD HH24:MI:SS')
              INTO d_Jieshusj
              FROM Dual;            OPEN Cursor_Kucun1; --打开游标
            LOOP
                FETCH Cursor_Kucun1
                    INTO n_jiageid;
                EXIT WHEN Cursor_Kucun1%NOTFOUND;          --add by lxp 2013-11-14   
        
                         select count(jiageid)
                         into n_count
                         from gy_yaopincdjg2
                        where xiaoguigid = guigeid
                          and zuofeibz = 0
                          and jiageid = n_jiageid;
          
              if n_count >0 then
              
              select distinct C.jiageid as JIAGEIDDGG
                into s_jiageiddgg
                from 
                     (select *
                        from gy_yaopincdjg2
                       where xiaoguigid = guigeid
                         and zuofeibz = 0) b,
                     gy_yaopincdjg2 c
               where b.daguigid = c.guigeid
                 and c.tingyongbz = 0
                 and c.zuofeibz = 0               
                 and b.jiageid = n_jiageid
                 and rownum=1;
                                  
               select round(Nvl(SUM(sl), 0), 2)
                 INTO n_Qitasl
                 from (SELECT Nvl(SUM(x.chukusldgg), 0) sl
                         from (select a.churuksl / c.baozhuangliang as chukusldgg,
                                      a.churukfs as churukfs,
                                      a.jiageid as jiageidxgg,
                                      a.jizhangrq as jizhangrq,
                                      a.yingyongid as yingyongid
                                 from my_mingxizhang a,
                                      (select *
                                         from gy_yaopincdjg2
                                        where xiaoguigid = guigeid
                                          and zuofeibz = 0) b,
                                      gy_yaopincdjg2 c
                                where a.jiageid = b.jiageid
                                  and b.daguigid = c.guigeid
                                  and a.jiageid = n_jiageid
                                  ) x
                        WHERE x.Churukfs IN ('67', '68', '59', '61')
                          AND x.Jizhangrq > d_Kaishisj
                          And x.Jizhangrq < d_Jieshusj
                          and x.jiageidxgg = n_jiageid
                        
                       union all
                       SELECT round(Nvl(SUM(a.Churuksl), 0), 2) sl
                         FROM my_Mingxizhang a
                        WHERE a.Churukfs IN ('67', '68', '59', '61')
                          AND a.Jizhangrq > d_Kaishisj
                          And a.Jizhangrq < d_Jieshusj
                          and a.Jiageid = s_jiageiddgg
                          );
         else
                SELECT round(Nvl(SUM(l.Churuksl), 0),2) sl
                  INTO n_Qitasl
                  FROM my_Mingxizhang l
                 WHERE l.Churukfs IN  ('67', '68', '59', '61')  
                   and l.Jiageid = n_jiageid                            
                   AND l.Jizhangrq> d_Kaishisj 
                   and l.jizhangrq< d_Jieshusj;
                 
         end if;
         
         
         if n_count=0 then
           s_jiageiddgg :=n_jiageid;
         end if;
      
         Select sum(KuCunsl) Into n_Kucunsl
      from (
       Select KuCunsl 
         From my_v_yaopinzlkcdgg
        Where jiageid = S_JIAGEIDDGG
          and yingyongid in ('0501','0508')
        union all
        Select KuCunsl
         From yk_KuCun1
        Where JiaGeID = s_jiageiddgg
          and yingyongid = '0608');                --药库不需要转换规格
                --向每日结余库存表中插入记录
                Rec_Meirijykc1.Jieyurq       := d_Jieyurq;
                Rec_Meirijykc1.Zongliangkcid := 0;      
                Rec_Meirijykc1.Yingyongid    := 0;
                Rec_Meirijykc1.Jiageid        := s_jiageiddgg;               
                Rec_Meirijykc1.Kucunsl       := Nvl(n_Kucunsl,0);
                Rec_Meirijykc1.yishouwfsl    :=0;
                Rec_Meirijykc1.xukucsl       :=0;
                Rec_Meirijykc1.linshikcsl    :=0;
                Rec_Meirijykc1.Rukusl        := 0;
                Rec_Meirijykc1.Chukusl       := 0;
                Rec_Meirijykc1.Qitasl        :=n_Qitasl;
                Rec_Meirijykc1.Yuanquid      := 1;
                Rec_Meirijykc1.Shiyongpl     := 1;               /* Rec_Meirijykc1.KuCunSL      := num_Kucunsl ;*/
                Rec_Meirijykc1.KuCunJE      :=0 ;  --库存金额
                Rec_Meirijykc1.ChuKuJE      :=0 ;
                Rec_Meirijykc1.RuKuJE       := 0 ;
                Rec_Meirijykc1.QiTaJE       := 0 ;
                Rec_Meirijykc1.xiaohaosl    := 0 ;
                --插记录
                begin 
                INSERT INTO my_Meirijykc1 VALUES Rec_Meirijykc1;
                Exception
                When others Then
                n_Kucunsl:=0;
                end;   
                
            END LOOP;
            CLOSE Cursor_Kucun1;
                 commit;
                 
            PKg_yk_yewu_ex.Prc_YK_caigoujh_scxh('1','1' ,prm_AppCode => prm_AppCode,
                                            prm_DataBuffer => prm_DataBuffer);
                     If prm_AppCode <> 1 Then
                        Return;
                     End If;      
                 
           
        EXCEPTION
            WHEN OTHERS THEN
                Prm_Appcode    := -5;
                Prm_Databuffer := '每日结余库存失败，错误原因如下：' || To_Char(SQLCODE) || ':' || SQLERRM;
                RETURN;
        END;
    END Prc_my_Meirijykc;

Answer 2

发现游标定义时使用了distinct，是否存在jiageid重复的现象导致少数据呢？

Answer 3

设置一个计数器
i:=0
OPEN Cursor_Kucun1; --打开游标
LOOP
    FETCH Cursor_Kucun1 INTO n_jiageid;
    EXIT WHEN Cursor_Kucun1%NOTFOUND or i>1000;
    i:=i+1;
    --你的循环操作end loop;
close Cursor_Kucun1; 

Answer 4

建议 LZ  建立一下临时表，每一个跑一条，就把这条数据的 PK 写到临时表，看看是少了哪些数据。

Answer 5

--插记录
                begin 
                INSERT INTO my_Meirijykc1 VALUES Rec_Meirijykc1;
                Exception
                When others Then
                dbms_output.put_line('INSERT INTO '||my_Meirijykc1||' VALUES Rec_Meirijykc1  ');
                n_Kucunsl:=0;
                end;   加这个输出，看看没有插入的是什么
--看有多少条
    SELECT count(1) FROM my_Kucun1 a where a.rowid<(SELECT max(1) FROM my_Kucun1 b where a.jiageid = b.jiageid)
             and a.yingyongid ='0501' and a.jiageid = '22905';

Answer 6

 --改个地方
                begin 
                INSERT INTO my_Meirijykc1 VALUES Rec_Meirijykc1;
                Exception
                When others Then
                dbms_output.put_line('INSERT INTO my_Meirijykc1 VALUES'|| Rec_Meirijykc1  );
                n_Kucunsl:=0;
                end;  

Answer 7

                --插记录
                begin 
                INSERT INTO my_Meirijykc1 VALUES Rec_Meirijykc1;
                Exception
                When others Then
                n_Kucunsl:=0;
                end; 应该是这个地方的问题，即使发生了插入异常，你的LOOP循环还是会照常往下走的，而异常的数据时不能进到表 my_Meirijykc1里面的。这个地方的异常至少要留个记录，或者就让程序抛出来吧，这样子赋值一个变量为0然后置之不理是不合理的。

Answer 8

同意以下分析， 而且INSERT 语句失败通常意味着违反了约束， rollback是必须的。