摘要：
通常，我们将直接使用存储过程来处理涉及数据库的一些预定任务，这节省了后端代码的开发和部署。它简单快速，但这样做有一个缺点——当存储过程执行错误时，我们无法察觉。解决方案很简单。学习捕获异常和打印日志的代码。
一般我们会将一些涉及到数据库的定时任务直接用存储过程搞定，省去了后端代码的开发、部署，简单、快速，但这种方式存在一个弊端——当存储过程执行出错了，我们无法感知。解决办法也简单，学代码那样去捕获异常、打印日志。
第一步，建日志表：
create table TBL_WLF_SYS_LOG ( S_TIME VARCHAR2(32) not null, S_LEVEL VARCHAR2(32), S_PROCNAME VARCHAR2(64), S_MSG VARCHAR2(4000), S_ADVICE VARCHAR2(1024) ) tablespace TBS_WLF_DAT; -- Add comments to the table comment on table TBL_WLF_SYS_LOG is '存储过程日志表'; -- Add comments to the columns comment on column TBL_WLF_SYS_LOG.S_TIME is '操作时间'; -- Add comments to the columns comment on column TBL_WLF_SYS_LOG.S_LEVEL is '操作级别'; -- Add comments to the columns comment on column TBL_WLF_SYS_LOG.S_PROCNAME is '执行存储过程名称'; -- Add comments to the columns comment on column TBL_WLF_SYS_LOG.S_MSG is '错误信息'; -- Add comments to the columns comment on column TBL_WLF_SYS_LOG.S_ADVICE is '建议信息';
第二步，建日志存储过程：
CREATE OR REPLACE PROCEDURE VCODE.prc_wlf_sys_writelog( i_flag INTEGER, i_id INTEGER, str_procname varchar2, str_msg varchar2, str_advice varchar2 ) IS -- 操作时间 str_time varchar2(32); -- 操作级别 str_level varchar2(32); -- 执行存储过程名称 p_procname varchar2(1024); -- 错误信息，或者记录信息 p_msg varchar2(1024); -- 建议信息 p_advice varchar2(1024); BEGIN IF (i_flag = 2 AND i_id >= 1 AND i_id <= 4) THEN CASE WHEN i_id = 1 THEN str_level := 'log'; WHEN i_id = 2 THEN str_level := 'debug'; WHEN i_id = 3 THEN str_level := 'alarm'; ELSE str_level := 'error'; END CASE; p_procname := str_procname; p_msg := str_msg; p_advice := str_advice; ELSE str_level := 'error'; p_procname := 'p_public_writelog'; p_msg := 'writelog_error'; p_advice := ''; END IF; str_time := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'); INSERT INTO tbl_wlf_sys_log (s_time, s_level, s_procname, s_msg, s_advice) VALUES (str_time, str_level, p_procname, p_msg, p_advice); COMMIT; END prc_wlf_sys_writelog; /
第三步，在我们业务存储过程中，调用日志存储过程：
CREATE OR REPLACE procedure VCODE.PROC_CUSTOM_RECORD_UPDATE is -- debug信息 v_debugmsg varchar2(1024); -- 错误信息 v_errmsg varchar2(1024); -- 查询用户邀请活动信息表 获取活动开始与结束时间 cursor ACTIVITY_CUR is --声明显式游标 select T.ACTIVITYID, T.COUNTSTARTTIME, T.COUNTENDTIME from vcode.T_INVITE_ACTIVITYINFO T where T.HASCOUNTTIME = 1; --定义游标变量，该变量的类型为基于游标ACTIVITY_CUR的记录 type ACTIVITY_CUR_ROW is table of ACTIVITY_CUR%ROWTYPE; cs_invitestat SYS_REFCURSOR; type tp_CUSTOM_RECORD is table of T_INVITER_CUSTOM_RECORD%ROWTYPE; va_CUSTOM_RECORD tp_CUSTOM_RECORD; ACTIVITY_ID varchar2(50); START_TIME date; END_TIME date; begin -- 存储过程开始日志 v_debugmsg := 'VCODE.PROC_CUSTOM_RECORD_UPDATE begin log- '; prc_wlf_sys_writelog(2, 2, 'PROC_CUSTOM_RECORD_UPDATE', v_debugmsg, ''); --For 循环 遍历用户邀请活动信息表，根据活动开始结束时间间隔获取数据信息 for ACTIVITY_CUR_ROW in ACTIVITY_CUR LOOP ACTIVITY_ID := ACTIVITY_CUR_ROW.ACTIVITYID; START_TIME := ACTIVITY_CUR_ROW.COUNTSTARTTIME; -- 取当天的最后一秒 select TRUNC(ACTIVITY_CUR_ROW.COUNTENDTIME+1)-1/(24*3600) into END_TIME from DUAL; -- 根据活动ID，开始时间，结束时间，查询被邀请人记录表获取邀请人激活人数与最后激活时间，查询奖励记录表获取书券奖励，并关联一起。 open cs_invitestat for SELECT t4.INVITERMSISDN,COUNT(1) AS TOTALACTIVENUMBER,max(t4.ACTIVETIME) AS LASTACTIVETIME,t4.ACTIVITYID,CASE WHEN SUM(t3.PRIZENUM) is null THEN 0 ELSE SUM(t3.PRIZENUM) END totalTicket FROM ( SELECT t1.INVITERMSISDN,t1.INVITEEMSISDN,t1.ACTIVETIME,t1.ACTIVITYID FROM T_INVITEE_RECORD t1 WHERE t1.ACTIVITYID=ACTIVITY_ID and t1.ACTIVESTATUS = 1 and t1.INVITEETYPE = 0 and t1.ACTIVETIME <= END_TIME and t1.ACTIVETIME >= START_TIME ) t4 LEFT JOIN (SELECT t2.INVITERMSISDN,t2.INVITEEMSISDN,SUM(t2.PRIZENUM) PRIZENUM,t2.ACTIVETIME FROM T_INVITING_AWARDS_RECORD t2 WHERE t2.PRIZETYPE=3 AND t2.ISSEND IN (0,1,2,3,10) AND t2.ACTIVITYID=ACTIVITY_ID and t2.ACTIVETIME <= END_TIME and t2.ACTIVETIME >= START_TIME and t2.REWARDTYPE = 0 group by t2.ACTIVETIME,t2.INVITEEMSISDN,t2.INVITERMSISDN) t3 ON t4.INVITERMSISDN=t3.INVITERMSISDN AND t4.INVITEEMSISDN=t3.INVITEEMSISDN AND t4.ACTIVETIME=t3.ACTIVETIME group by t4.ACTIVITYID,t4.INVITERMSISDN ORDER BY TOTALACTIVENUMBER desc,LASTACTIVETIME ASC; fetch cs_invitestat bulk collect into va_CUSTOM_RECORD limit 500; -- 遍历结果，并插入T_INVITER_CUSTOM_RECORD 自定义排行表中，如果存在数据则更新（邀请人、活动ID相同），不存在则插入 forall i in 1..va_CUSTOM_RECORD.count merge into vcode.T_INVITER_CUSTOM_RECORD T5 using (select * from dual) on (INVITERMSISDN = va_CUSTOM_RECORD(i).INVITERMSISDN AND ACTIVITYID=va_CUSTOM_RECORD(i).ACTIVITYID) when matched then update set TOTALACTIVENUMBER =va_CUSTOM_RECORD(i).TOTALACTIVENUMBER, LASTACTIVETIME =va_CUSTOM_RECORD(i).LASTACTIVETIME, TOTALTICKET =va_CUSTOM_RECORD(i).TOTALTICKET where T5.TOTALACTIVENUMBER!=va_CUSTOM_RECORD(i).TOTALACTIVENUMBER OR T5.TOTALTICKET!=va_CUSTOM_RECORD(i).TOTALTICKET when not matched then insert ( INVITERMSISDN, TOTALACTIVENUMBER, LASTACTIVETIME, ACTIVITYID, TOTALTICKET ) values ( va_CUSTOM_RECORD(i).INVITERMSISDN, va_CUSTOM_RECORD(i).TOTALACTIVENUMBER, va_CUSTOM_RECORD(i).LASTACTIVETIME, va_CUSTOM_RECORD(i).ACTIVITYID, va_CUSTOM_RECORD(i).TOTALTICKET ); commit; end LOOP; -- 存储过程开始日志 v_debugmsg := 'VCODE.PROC_CUSTOM_RECORD_UPDATE end log- '; prc_wlf_sys_writelog(2, 2, 'PROC_CUSTOM_RECORD_UPDATE', v_debugmsg, ''); exception when others then begin rollback; v_errmsg := 'sqlexception~~sqlcode:' || to_char(sqlcode) || ' sqlstate:' || substr(sqlerrm, 1, 512); prc_wlf_sys_writelog(2, 4, 'PROC_CUSTOM_RECORD_UPDATE', v_errmsg, ''); end; end; /