一个普通的oracle存储过程的例子

[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]