CREATE OR REPLACE PROCEDURE APICALL_LOG_INTERFACE_CLEAN

(

  CLEANDAY IN Number --天数

) AS

  v_cleanDay number; --天数

  v_errmsg varchar2(2000); --错误信息

  v_id varchar2(50); --ID 

BEGIN

  v_cleanDay:=NVL(CLEANDAY,0);

  select sys_guid() into v_id from dual; --随机一个log ID

  delete from APICALL_LOG_INTERFACE where trunc(RESPDATE) < trunc(sysdate-v_cleanDay);-- 删除v_cleanDay天前的数据

  insert into APP_CLEAN_LOG(ID,PRO_NAME,STATUS,CREATEDATE)values(v_id,'APICALL_LOG_INTERFACE_CLEAN','1',sysdate);--插入日志

  commit;

exception

  when others then

    begin

      v_errmsg := '错误! sqlcode:' || sqlcode || ' sqlerrm:' ||sqlerrm;

      rollback;

      dbms_output.put_line(v_errmsg);

      select sys_guid() into v_id from dual;

      insert into APP_CLEAN_LOG(ID,PRO_NAME,STATUS,CREATEDATE,ERROR_MSG)--插入日志

      values(v_id,'APICALL_LOG_INTERFACE_CLEAN','0',sysdate,v_errmsg);

      commit;

    end;

END APICALL_LOG_INTERFACE_CLEAN;