[size=large]
create or replace procedure P_TQA_F_NSPG(id_mth in number :=0) AS
v_id_mth number(6); ---一个简单的存储过程，传入加工月份，声明了个临时变量
begin ----v_id_mth做判断传入加工月份用的
if id_mth = 0 then
select to_number(to_char(sysdate,'yyyymm')) into v_id_mth from dual;
else
v_id_mth := id_mth;
end if;
delete TQA_F_NSPG where id_mth = v_id_mth ;----先删除要加工的月份数据
insert into TQA_F_NSPG(id_mth, -----插入到加工表中数据
Nsrnbm,
nsr_mc,
Id_Nspgjl,
Sk_Je,
Jj_Je,
swjg_dm,
mth_pg,
Name_Pgry)
select v_id_mth,
a.nsrnbm,
a.nsr_mc,
2,
case when b.zsxm_dm!='90' then b.je else 0 end,
case when b.zsxm_dm='90' then b.je else 0 end,
a.swjg_dm,
to_char(a.date_pgsj,'yyyymm'),
c.name_jcry
from TQA_D_NSPGJYS a,TQA_D_JYBSBSKQD b,TQA_D_NSPGSJB c where
a.no=b.no_jys and a.no_sj=c.no;
insert into TQA_F_NSPG(id_mth,
Nsrnbm,
nsr_mc,
Id_Nspgjl,
Sk_Je,
Jj_Je,
swjg_dm,
mth_pg,
Name_Pgry)
select v_id_mth,
a.nsrnbm,
a.nsr_mc,
case when a.id_nspgjl=3 then 3 else
case when a.id_nspgjl=4 then 4 else
case when a.id_nspgjl=1 then 1 else 0 end
end
end,
0,
0,
a.swjg_dm,
to_char(a.nspgrq,'yyyymm'),
c.name_jcry
from TQA_D_NSPGBG a,TQA_D_NSPGSJB c
where a.no_sj=c.no and (a.id_nspgjl=3 or a.id_nspgjl=4 or a.id_nspgjl=1);
commit; ----提交
end P_TQA_F_NSPG;
[/size] | 
转播
