存储过程异常日志写法

create or replace procedure fr_pro_distribution_result
(in_comm in varchar2,in_yymm  in varchar2,
  in_optid in varchar2 ,
in_type  in varchar2,in_funid in varchar2) is
--获取递延调整__取数数据存储过程
  ls_exception varchar2(4000);
  v_rule_batchid  varchar2(20);
v_commit varchar2(4000);
  v_rcd_batchid  varchar2(20);
  v_batchid varchar2(20);
  cur_one       sys_refcursor;
  rec   fr_distribution_con%rowtype;
    v_sql    varchar2(4000);
    v_sql_3  varchar2(4000);
begin
   v_batchid :=fr_fun_base_get_batch(in_yymm,in_comm,'fr_pro_distribution_result',in_optid,in_funid);
  insert into fr_base_logs( logid,methodname, methoddes, batch_id,  happentime,opt_id) values
 (FR_fun_base_getid,'fr_pro_distribution_result', '项目调整',v_batchid,sysdate,in_optid);
 commit;
  v_rcd_batchid :=fr_fun_base_get_batch(in_yymm,in_comm,'fr_pro_distribution_get_data',in_optid,in_funid);
  v_rule_batchid :=fr_fun_base_get_batch(in_yymm,in_comm,'fr_distribution_con',in_optid,in_funid);
  ls_exception :='0';
 if (v_batchid<>'XX' and v_rcd_batchid<>'XX' and v_rule_batchid<>'XX' ) then
       v_commit:=' update fr_base_data_control s set s.opt_1=''开始筛选（1/3）''   where s.batch_id='||''''||v_batchid||'''';
   execute immediate v_commit;commit;
open cur_one for select  con.dat_id,
con.customer_po,con.product,con.pdt,con.level_one,con.level_two,con.level_three,con.level_four,con.default_product_code,con.branch,con.sm,con.project_name,con.customer_name,con.des_type
   from fr_distribution_con con where   con.batch_id= v_rule_batchid  order by con.dat_id;
   loop
     fetch CUR_ONE into
     rec.dat_id,
     rec.customer_po,rec.product,rec.pdt,rec.level_one,rec.level_two,rec.level_three,rec.level_four,rec.default_product_code,rec.branch,rec.sm,rec.project_name,rec.customer_name,rec.DES_TYPE;
      exit when CUR_ONE%Notfound;
--拼接筛选条件
select '规则与筛选条件ID为'||rec.dat_id||',批次号描述信息是：'||rec.DES_TYPE  into ls_exception  from dual;
     select WM_CONCAT('FR_fun_base_getcon('''||dd||''','||ss||') *')   into v_sql_3 from
(select bs.d_col  dd,bs.s_col  ss from FR_base_stod  bs where bs.s_tab='fr_distribution_con'
and bs.d_tab='fr_distribution_imp_data' and bs.status='0'  order by  bs.ord_sn);
v_sql_3:= replace (v_sql_3,'*,',' || ');
    v_sql_3:=replace(v_sql_3,'*',' ');
 v_sql_3:= 'select  '||v_sql_3||' '||' from ' ||' fr_distribution_con ist'||' where
 ist.dat_ID='||''||rec.dat_id||''||' and ist.batch_id='||''||v_rule_batchid||'';
   insert into fr_base_logs( logid,methodname, methoddes, batch_id,  happentime,opt_id) values
 (FR_fun_base_getid,'fr_pro_distribution_result', '规则是：'||ls_exception||','||v_sql_3,v_batchid,sysdate,in_optid);
 commit;
 execute immediate  v_sql_3  into v_sql_3;
commit;
 v_sql:='insert into FR_distribution_RESULT(dat_id,rule_id,des_type,Batch_Id)
 select  im.dat_id,'||''''||rec.dat_id||''''||','||''''||rec.des_type||''''||','||''''||v_batchid||''''||' from fr_distribution_imp_data im where 1=1 ';
 v_sql :=v_sql||' '||v_sql_3||' '||'and im.batch_id='||''''||v_rcd_batchid||'''';
   insert into fr_base_logs( logid,methodname, methoddes, batch_id,  happentime,opt_id) values
 (FR_fun_base_getid,'fr_pro_distribution_result', '项目调整'||ls_exception||','||v_sql,v_batchid,sysdate,in_optid);
 commit;
  execute immediate v_sql;commit;
 commit;
   end loop;
    close CUR_ONE;
    commit;
    v_commit:=' update fr_base_data_control s set s.opt_1=''筛选完成（1/3）''   where s.batch_id='||''''||v_batchid||'''';
  end if;

---- 异常写法
  exception when others then rollback;
if(ls_exception<>'0') then
 insert into fr_base_data_control_son (batch_id,yymm,comm_id,con_type,data_type,status,num,opt_1,opt_2,opt_3,fun_id)
values(v_batchid,in_yymm,in_comm,'data_share','fr_pro_distribution_result',0,1,'系统进入的阶段：'||ls_exception,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),in_optid,in_funid);
commit;
end if;
v_sql :='delete from fr_distribution_result r where r.batch_id='||''''||v_batchid||'''';
  execute immediate v_sql;commit;
    ls_exception := SQLCODE || '-' || SUBSTR(SQLERRM, 1, 400);
    insert into FR_base_error_logs
      (logid, methodname, methoddes, exception, happentime) values
      (FR_fun_base_getid, 'fr_pro_distribution_result',
       '报错了',
       ls_exception,
       sysdate);
       commit;
        insert into fr_base_data_control_son (batch_id,yymm,comm_id,con_type,data_type,status,num,opt_1,opt_2,opt_3,fun_id)
values(v_batchid,in_yymm,in_comm,'data_share','fr_pro_distribution_result',0,1,''||ls_exception,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),in_optid,in_funid);
commit;
 

end fr_pro_distribution_result;