ORACLE PROCEDURE存储过程学习

定期更新数据，以便统计；定期设置使用job

create or replace procedure CSHCYBQ(v_CSNF in varchar2) as

       v_DQNY varchar2(6);--当前年月
       v_TJNY varchar2(6);--统计年月
       v_SCNY varchar2(6);--上一次统计年月
       v_TJNJ varchar2(6);--统计年季
       v_TJNF varchar2(4);--统计年份
       v_TJYF number(2);--统计月份
       v_TJJD varchar2(1);--统计季度
begin
     delete dq_cybq;       

     v_DQNY:=to_char(sysdate,'YYYYMM');--初始化系统年月
     v_SCNY:=(v_CSNF-1)||'12';--上一次统计年月置为起始统计季度的前一个季度
     v_TJNF:=v_CSNF;
     v_TJYF:=3;
     v_TJNY:=v_TJNF||'03';
     v_TJJD:='1';
     v_TJNJ:=v_TJNF||'Q'||v_TJJD;

     while v_TJNY < v_DQNY loop --统计时间要小于系统年月

           insert into dq_cybq(id, tjqb, hyml, xzqh, cri, zczbzb)

           with info as
           (select q.hyml,q.xzqh,
            nvl(sum(q.zczb),0) BQZCZB,--本期注册资本总和
            (select nvl(sum(q2.zczb),0) from frk_gsj_qyjgdjxx q2 
                    where q2.hyml=q.hyml and q2.xzqh=q.xzqh and q2.jyzt='1' and to_char(q2.clrq,'YYYYMM')<=v_SCNY) SQZCZB,--上期注册资本总和
            (select nvl(sum(q2.zczb),0) from frk_gsj_qyjgdjxx q2 
                    where q2.jyzt='1' and to_char(q2.clrq,'YYYYMM')<=v_TJNY) ZZCZB--注册资本总和
           from frk_gsj_qyjgdjxx q
           where q.jyzt='1' and to_char(q.clrq,'YYYYMM')<=v_TJNY
           group by q.hyml,q.xzqh)

           select SYS_GUID(),v_TJNJ,hyml,xzqh,(BQZCZB-SQZCZB)/ZZCZB*100,BQZCZB/ZZCZB*100 from info ;

           v_SCNY:=v_TJNY;
           v_TJYF:=v_TJYF+3;
           v_TJJD:=v_TJJD+1;
           if v_TJYF > 12 then
              v_TJNF:=v_TJNF+1;
              v_TJYF:=3;
              v_TJJD:='1';
              v_TJNY:=v_TJNF||'03';
              v_TJNJ:=v_TJNF||'Q'||v_TJJD;
           else
              v_TJNY:=v_TJNY+3;
              v_TJNJ:=v_TJNF||'Q'||v_TJJD;
           end if;

     end loop;

     commit;

end CSHCYBQ;