关于一个存储过程的问题，求助。

[滚球屎壳螂]

一个存储过程，主要是要查出数据来 有两个游标 C1、C2 情况一将C1的数据插入到指定表中
情况二则将C2的数据插入 但执行的时候到
“ --生成单位汇总
insert into TEMP_REP_111004”
这里报错，求助各位我是哪里写错了。

create or replace procedure P_REP_111004(p_czy      varchar2,
                                         p_unit     number,
                                         p_scale    number,

                                         p_usertype varchar2) is
  --部门预算（财政）610002
  --P_unit：0：万元，1：元
  --p_isnew：0：新科目，1:旧科目
  -- v_bmfl varchar2(20);
  -- v_i number;
  cursor c1 is
    select a.dwdm,
           a.bmfl,
           a.zcgnfl,
           a.xmfl,
           a.Zfbhmc,
           a.zfbhfs,
           a.zfbhxh,            
           xmmc,
           b.mxnr,
           b.cgfs,
           b.cgml,
           b.sl,
           b.mxxqrq,
           b.bkfs,
           b.ggxh,
           a.bz,
           a.xmbm,

           d.MXHH,
           d.ZCFL,
           d.ZCPP,          
           d.JLDW,
           b.DJ,
           a.ZJE,
           d.ZCYT,--资产用途
           decode(substr(zjly, 1, 1),
                  '1',
                  decode(p_usertype, 1, dwtbmxje, 2, zgshmxje, czshmxje)) ysn,
           decode(substr(zjly, 1, 1),
                  '2',
                  decode(p_usertype, 1, dwtbmxje, 2, zgshmxje, czshmxje),
                  0) ysw,
           decode(substr(zjly, 1, 1),
                  '1',
                  0,
                  '2',
                  0,
                  decode(p_usertype, 1, dwtbmxje, 2, zgshmxje, czshmxje)) qt
      from y_zcxm a, y_zcxm_mx b, y_zcxm_mx_zcmx d
     where a.xmbm = b.xmbm and a.xmbm = d.xmbm
       and a.xmzt = '0'
       and b.mxzt = '0'
       and a.shbz = '0'
       and b.zfcgbz = '0'
       and FN_BBSC(p_usertype, a.xmwz, a.xyshr, a.xmtblx, p_czy) = 'Y'
       and exists (select 1
              from t_report_bmfl c
             where a.bmfl = c.bmfl
               and c.czy = p_czy);
  c1_rec c1%rowtype;

  cursor c2 is
    select a.dwdm,
           a.bmfl,
           a.zcgnfl,
           a.xmfl,
           a.Zfbhmc,
           a.zfbhfs,
           a.zfbhxh,             
           xmmc,
           b.mxnr,
           b.cgfs,
           b.cgml,
           b.sl,
           b.mxxqrq,
           b.bkfs,
           b.ggxh,
           a.bz,
           a.xmbm,  

           d.MXHH,
           d.ZCFL,
           d.ZCPP,          
           d.JLDW,
           b.DJ,
           a.ZJE,
           d.ZCYT,--资产用途       
           decode(substr(zjly, 1, 1),
                  '1',
                  decode(p_usertype, 1, dwtbmxje, 2, zgshmxje, czshmxje)) ysn,
           decode(substr(zjly, 1, 1),
                  '2',
                  decode(p_usertype, 1, dwtbmxje, 2, zgshmxje, czshmxje),
                  0) ysw,
           decode(substr(zjly, 1, 1),
                  '1',
                  0,
                  '2',
                  0,
                  decode(p_usertype, 1, dwtbmxje, 2, zgshmxje, czshmxje)) qt
      from y_zcxm a, y_zcxm_mx b, y_zcxm_mx_zcmx d
     where a.xmbm = b.xmbm and a.xmbm = d.xmbm
       and a.xmzt = '0'
       and b.mxzt = '0'
       and a.shbz = '0'       
       and b.zfcgbz = '0'
       and FN_BBSC(p_usertype, a.xmwz, a.xyshr, a.xmtblx, p_czy) = 'Y'
       and exists (select 1
              from t_report_bmfl c
             where a.bmfl = c.bmfl
               and c.czy = p_czy);
  c2_rec c2%rowtype;
begin
  delete from TEMP_REP_111004 where czy = p_czy;
   Commit;
      if (P_unit = 0) then
        open c2;
        loop
          fetch c2 into c2_rec;
          exit when c2%notfound;
    
          insert into TEMP_REP_111004(ZGDW,DWDM,BMFL,ZCGNFL,MXJE,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,XMBM,pxh,JJZFBH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
          values (substr

(c2_rec.bmfl,1,3),c2_rec.dwdm,c2_rec.bmfl,c2_rec.zcgnfl,0,c2_rec.cgfs,c2_rec.cgml,c2_rec.sl,c2_rec.ysn,c2_rec.ysw,c2_rec.qt,P_czy,c2_rec.xmfl,c2_rec.bz,'',c2_rec.ggxh,

c2_rec.mxxqrq,c2_rec.bkfs,c2_rec.xmmc,c2_rec.mxnr,c2_rec.xmbm,'1',c2_rec.zfbhmc||c2_rec.zfbhfs||c2_rec.zfbhxh,c2_rec.MXHH,c2_rec.ZCFL,c2_rec.ZCPP,c2_rec.JLDW,c2_rec.DJ,c2_rec.ZJE,c2_rec.ZCYT);
    
        end loop;
        close c2;
      else
        open c1;
        loop
          fetch c1 into c1_rec;
          exit when c1%notfound;
          insert into TEMP_REP_111004(ZGDW,DWDM,BMFL,ZCGNFL,MXJE,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,XMBM,pxh,JJZFBH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
          values (substr

(c1_rec.bmfl,1,3),c1_rec.dwdm,c1_rec.bmfl,c1_rec.zcgnfl,0,c1_rec.cgfs,c1_rec.cgml,c1_rec.sl,c1_rec.ysn,c1_rec.ysw,c1_rec.qt,P_czy,c1_rec.xmfl,c1_rec.bz,'',c1_rec.ggxh,

c1_rec.mxxqrq,c1_rec.bkfs,c1_rec.xmmc,c1_rec.mxnr,c1_rec.xmbm,'1',c1_rec.zfbhmc||c1_rec.zfbhfs||c1_rec.zfbhxh,c1_rec.MXHH,c1_rec.ZCFL,c1_rec.ZCPP,c1_rec.JLDW,c1_rec.DJ,c1_rec.ZJE,c1_rec.ZCYT);
        end loop;
      close c1;
      end if;
    

  --生成单位汇总
          insert into TEMP_REP_111004
                 (ZGDW,DWDM,BMFL,ZCGNFL,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,PXH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
                     select zgdw,dwdm,bmfl,null,null,null,null,sum(ysn),sum(ysw),sum(qt),p_czy,null,null,null,null,null,null,null,null,'2',MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT from TEMP_REP_111004 

where czy=p_czy and pxh='1' group by zgdw,dwdm,bmfl;
  
  --生成主管汇总
          insert into TEMP_REP_111004
                 (ZGDW,DWDM,BMFL,ZCGNFL,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,PXH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
                     select zgdw,null,0,null,null,null,null,sum(ysn),sum(ysw),sum(qt),p_czy,null,null,null,null,null,null,null,null,'3',MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT from TEMP_REP_111004 

where czy=p_czy and pxh='1' group by zgdw;

    --生成单位汇总
          insert into TEMP_REP_111004
                 (ZGDW,DWDM,BMFL,ZCGNFL,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,PXH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
                     select null,null,null,null,null,null,null,sum(ysn),sum(ysw),sum(qt),p_czy,null,null,null,null,null,null,null,null,'4',MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT from TEMP_REP_111004 

where czy=p_czy and pxh='1';
  commit;                     
  end P_REP_111004;

[opps_zhou]

insert into TEMP_REP_111004
(ZGDW,DWDM,BMFL,ZCGNFL,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,PXH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
select zgdw,dwdm,bmfl,null,null,null,null,sum(ysn),sum(ysw),sum(qt),p_czy,null,null,null,null,null,null,null,null,'2',MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT from TEMP_REP_111004

where czy=p_czy and pxh='1' group by zgdw,dwdm,bmfl;


你明显 group by 字段少了嘛
MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT 这几个都没有

[滚球屎壳螂]

[Quote=引用 1 楼 opps_zhou 的回复:]
你明显 group by 字段少了嘛
MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT 这几个都没有
[/Quote]

这几个都是我都来加上去的 估计就是这里错了 但我没理解你说的是什么意思

能说一下具体的解决办法和问题吗 谢谢了

[opps_zhou]

insert into TEMP_REP_111004
(ZGDW,DWDM,BMFL,ZCGNFL,CGFS,CGML,SL,YSN,YSW,QT,CZY,XMFL,ZXSM,SHSM,GGXH,XMDATE,ZFLB,XMMC,MXNR,PXH,MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT)
select zgdw,dwdm,bmfl,null,null,null,null,sum(ysn),sum(ysw),sum(qt),p_czy,null,null,null,null,null,null,null,null,'2',MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT from TEMP_REP_111004

where czy=p_czy and pxh='1' group by zgdw,dwdm,bmfl, MXHH,ZCFL,ZCPP,JLDW,DJ,ZJE,ZCYT;

这样就可以了