ORACLE PROCEDURE存储过程学习

mpcrcjpldp

定期更新数据，以便统计；定期设置使用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;