Oracle存储过程记录异常⽇志
⼀般我们会将⼀些涉及到数据库的定时任务直接⽤存储过程搞定，省去了后端代码的开发、部署，简单、快速，但这种⽅式存在⼀个弊端——当存储过程执⾏出错了，我们⽆法感知。解决办法也简单，学代码那样去捕获异常、打印⽇志。
第⼀步，建⽇志表：
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 =2AND i_id >=1AND i_id <=4) THEN
CASE
WHEN i_id =1THEN
str_level :='log';
WHEN i_id =2THEN
str_level :='debug';
WHEN i_id =3THEN
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 THEN0ELSE 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=3AND 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 in1..va_untoracle手动调用存储过程
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;
/
发表评论