一个普通的oracle存储过程的例子
create or replace procedure P_TQA_F_NSPG(id_mth in number :=0) ASv_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 fromTQA_D_NSPGBG a,TQA_D_NSPGSJB c wherea.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;
页:
[1]