待研究金华的存储过程

create or replace procedure jsmxq (
 gongsi    in   int,           
 period    in   VARCHAR2  )     
is
    chbianma INTEGER;
    scl number(16,2);   
    rownum int;
      ------参数存货编码 ，生产数量
  
    cursor  loop_cursor   is   select cunhuobianma,scl
                                      from scl 
                                      where gongsi=gongsi and month=period  ;  
     -------设置一个关于存货和生产量的游标，用于计算毛需求量
  
begin            
    open loop_cursor;
     -------打开游标
    fetch  loop_cursor      into chbianma, scl ;
     
    delete from mxql where gongsi=gongsi and month=period  ;
    ------为了避免错误将以前这个公司在同一个期间内的计算数据删除掉
     for chbianma  in loop_cursor  loop
     select count(1) into rownum   from scm_chanpinjiegouhead a where gongsi=gongsi and chanchengpin = chbianma;
         IF   (rownum<>0 )
           then
              insert into mxql (gongsi,month, cunhuo,zijianbianma,mxql) 
              select a.gongsi ,a.month,cunhuobianma as cunhuo,cunhuobianma as zijianbianma,nullif(shuliang,0)*scl as mxql 
               from scm_chanpinjiegouhead a ,scm_chanpinjiegou2 b
               where a.billid=b.billid and  a.gongsi=gongsi and   a.chanchengpin = chbianma  ;
           
         ELSE
           
              insert into mxql (gongsi,month, cunhuo,zijianbianma,mxql)
               SELECT gongsi as gongsi,period as month ,cunhuobianma as cunhuo ,chbianma as zijianbianma,xcl+scl as  mxql
                from xcl a    where a.gongsi=gongsi and cunhuobianma=chbianma ;
         end if;
         
       --- fetch next from loop_cursor 
       --- into cunhuobianma, scl
        end loop;
       ------将每个有需要采购的存货的子件写入MXQL 表格
   close loop_cursor ;
       -------关闭有效
   -----deallocate loop_cursor
       -------释放游标
insert into mxql (gongsi,month,zijianbianma,jhl)  select gongsi,month,cunhuobianma,jhl from xsjhl
                                                  where gongsi=gongsi and month=period ;
       ------将销售计划插入表格
insert into mxql (gongsi,month,zijianbianma,xsl)  select gongsi,month,cunhuobianma,xsl from xsl
                                                  where gongsi=gongsi and month=period ;
       ------将销售量插入表格
insert into mxql (gongsi,month,zijianbianma,xcl)  select gongsi,month,cunhuobianma,xcl from xcl
                                                  where gongsi=gongsi and month=period ;
       ------将现存量插入表格
insert into mxql (gongsi,month,zijianbianma,scl)  select gongsi,month,cunhuobianma,scl from scl
                                                  where gongsi=gongsi and month=period ;
       ------将生产量插入表格
insert into mxql (gongsi,month,zijianbianma,cgl)  select gongsi,month,cunhuo,nullif(cgl,0)  from cgl
                                                   where gongsi=gongsi and month=period ;
       ------将采购量插入表格
                   
end jsmxq;