Oracle定时任务执行存储过程备份日志记录表

时间:2022-05-29 00:11:06

写在前面

需求

备份系统日志表T_S_LOG, 按照操作时间字段OPERATETIME, 将每天的日志增量备份到另一张表.

思路

1.创建一张数据结构完全相同的表T_S_LOG_BAK作为备份表

2.查出T_S_LOG中需要备份的数据

3.将数据赋给游标变量

4.遍历游标将数据逐条插入T_S_LOG_BAK

5.创建无参存储过程将游标的这部分操作作为存储过程主体执行

6.创建定时任务定时执行该存储过程

操作环境

Oracle11g

T_S_LOG日志表(部分数据)

Oracle定时任务执行存储过程备份日志记录表Oracle定时任务执行存储过程备份日志记录表
 1 -- ----------------------------
 2 -- Table structure for T_S_LOG
 3 -- ----------------------------
 4 DROP TABLE  "T_S_LOG";
 5 CREATE TABLE  "T_S_LOG" (
 6 "ID" NVARCHAR2(32) NOT NULL ,
 7 "BROSWER" NVARCHAR2(100) NULL ,
 8 "LOGCONTENT" NCLOB NOT NULL ,
 9 "LOGLEVEL" NUMBER(6) NULL ,
10 "NOTE" NCLOB NULL ,
11 "OPERATETIME" DATE NOT NULL ,
12 "OPERATETYPE" NUMBER(6) NULL ,
13 "USERID" NVARCHAR2(32) NULL ,
14 "USERNAME" NVARCHAR2(50) NULL ,
15 "REALNAME" NVARCHAR2(50) NULL 
16 )
17 LOGGING
18 NOCOMPRESS
19 NOCACHE
20 
21 ;
22 COMMENT ON COLUMN  "T_S_LOG"."ID" IS id;
23 COMMENT ON COLUMN  "T_S_LOG"."BROSWER" IS ???;
24 COMMENT ON COLUMN  "T_S_LOG"."LOGCONTENT" IS ????;
25 COMMENT ON COLUMN  "T_S_LOG"."LOGLEVEL" IS ????;
26 COMMENT ON COLUMN  "T_S_LOG"."NOTE" IS IP;
27 COMMENT ON COLUMN  "T_S_LOG"."OPERATETIME" IS ????;
28 COMMENT ON COLUMN  "T_S_LOG"."OPERATETYPE" IS ????;
29 COMMENT ON COLUMN  "T_S_LOG"."USERID" IS ??ID;
30 COMMENT ON COLUMN  "T_S_LOG"."USERNAME" IS ????;
31 COMMENT ON COLUMN  "T_S_LOG"."REALNAME" IS ????;
32 
33 -- ----------------------------
34 -- Records of T_S_LOG
35 -- ----------------------------
36 INSERT INTO  "T_S_LOG" VALUES (402881f363ba3bfc0163ba3ddd270002, Chrome, 入职员工更新成功, 5, 本地, TO_DATE(2018-06-01 15:26:46, YYYY-MM-DD HH24:MI:SS), 1, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
37 INSERT INTO  "T_S_LOG" VALUES (402881f363ba41670163ba41cafe0000, Chrome, 用户: admin[JEECG开源社区]common.login.success, 1, 192.168.1.115, TO_DATE(2018-06-01 15:31:04, YYYY-MM-DD HH24:MI:SS), 1, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
38 INSERT INTO  "T_S_LOG" VALUES (402881f363ba41670163ba4234b50001, Chrome, 订单主信息删除成功, 4, 本地, TO_DATE(2018-06-01 15:31:31, YYYY-MM-DD HH24:MI:SS), 1, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
39 INSERT INTO  "T_S_LOG" VALUES (402881f363ba41670163ba4270e80002, Chrome, 错误异常: NumberFormatException,错误描述:For input string: "2017-10-26 12:00", 6, 本地, TO_DATE(2018-06-01 15:31:46, YYYY-MM-DD HH24:MI:SS), 3, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
40 INSERT INTO  "T_S_LOG" VALUES (402881f363ba41670163ba42f3ab0008, Chrome, 添加成功, 3, 本地, TO_DATE(2018-06-01 15:32:20, YYYY-MM-DD HH24:MI:SS), 1, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
41 INSERT INTO  "T_S_LOG" VALUES (402881f363ba41670163ba434993000a, Chrome, 错误异常: BusinessException,错误描述:Data truncation: Out of range value adjusted for column ‘‘order_money‘‘ at row 1; SQL [n/a]; nested exception is org.hibernate.exception.DataException: Data truncation: Out of range value adjusted for column ‘‘order_money‘‘ at row 1, 6, 本地, TO_DATE(2018-06-01 15:32:42, YYYY-MM-DD HH24:MI:SS), 3, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
42 INSERT INTO  "T_S_LOG" VALUES (402881f363ba41670163ba43946d000b, Chrome, 更新成功, 5, 本地, TO_DATE(2018-06-01 15:33:01, YYYY-MM-DD HH24:MI:SS), 1, 8a8ab0b246dc81120146dc8181950052, admin, 管理员);
43 
44 -- ----------------------------
45 -- Indexes structure for table T_S_LOG
46 -- ----------------------------
47 CREATE INDEX  "FK_OE64K4852UYLHYC5A00RFWTAY"
48 ON  "T_S_LOG" ("USERID" ASC)
49 LOGGING
50 VISIBLE;
51 
52 -- ----------------------------
53 -- Checks structure for table T_S_LOG
54 -- ----------------------------
55 ALTER TABLE  "T_S_LOG" ADD CHECK ("ID" IS NOT NULL);
56 ALTER TABLE  "T_S_LOG" ADD CHECK ("LOGCONTENT" IS NOT NULL);
57 ALTER TABLE  "T_S_LOG" ADD CHECK ("OPERATETIME" IS NOT NULL);
58 
59 -- ----------------------------
60 -- Primary Key structure for table T_S_LOG
61 -- ----------------------------
62 ALTER TABLE  "T_S_LOG" ADD PRIMARY KEY ("ID");
View Code

T_S_LOG_BAK备份表

Oracle定时任务执行存储过程备份日志记录表Oracle定时任务执行存储过程备份日志记录表
 1 -- ----------------------------
 2 -- Table structure for T_S_LOG_BAK
 3 -- ----------------------------
 4 DROP TABLE  "T_S_LOG_BAK";
 5 CREATE TABLE  "T_S_LOG_BAK" (
 6 "ID" NVARCHAR2(32) NOT NULL ,
 7 "BROSWER" NVARCHAR2(100) NULL ,
 8 "LOGCONTENT" NCLOB NOT NULL ,
 9 "LOGLEVEL" NUMBER(6) NULL ,
10 "NOTE" NCLOB NULL ,
11 "OPERATETIME" DATE NOT NULL ,
12 "OPERATETYPE" NUMBER(6) NULL ,
13 "USERID" NVARCHAR2(32) NULL ,
14 "USERNAME" NVARCHAR2(50) NULL ,
15 "REALNAME" NVARCHAR2(50) NULL 
16 )
17 LOGGING
18 NOCOMPRESS
19 NOCACHE
20 
21 ;
22 COMMENT ON COLUMN  "T_S_LOG_BAK"."ID" IS id;
23 COMMENT ON COLUMN  "T_S_LOG_BAK"."BROSWER" IS ???;
24 COMMENT ON COLUMN  "T_S_LOG_BAK"."LOGCONTENT" IS ????;
25 COMMENT ON COLUMN  "T_S_LOG_BAK"."LOGLEVEL" IS ????;
26 COMMENT ON COLUMN  "T_S_LOG_BAK"."NOTE" IS IP;
27 COMMENT ON COLUMN  "T_S_LOG_BAK"."OPERATETIME" IS ????;
28 COMMENT ON COLUMN  "T_S_LOG_BAK"."OPERATETYPE" IS ????;
29 COMMENT ON COLUMN  "T_S_LOG_BAK"."USERID" IS ??ID;
30 COMMENT ON COLUMN  "T_S_LOG_BAK"."USERNAME" IS ????;
31 COMMENT ON COLUMN  "T_S_LOG_BAK"."REALNAME" IS ????;
32 
33 -- ----------------------------
34 -- Indexes structure for table T_S_LOG_BAK
35 -- ----------------------------
36 CREATE INDEX  "FK_OE64K4852UYLHYC5A00RFWTAY"
37 ON  "T_S_LOG_BAK" ("USERID" ASC)
38 LOGGING
39 VISIBLE;
40 
41 -- ----------------------------
42 -- Checks structure for table T_S_LOG_BAK
43 -- ----------------------------
44 ALTER TABLE  "T_S_LOG_BAK" ADD CHECK ("ID" IS NOT NULL);
45 ALTER TABLE  "T_S_LOG_BAK" ADD CHECK ("LOGCONTENT" IS NOT NULL);
46 ALTER TABLE  "T_S_LOG_BAK" ADD CHECK ("OPERATETIME" IS NOT NULL);
47 
48 -- ----------------------------
49 -- Primary Key structure for table T_S_LOG_BAK
50 -- ----------------------------
51 ALTER TABLE  "T_S_LOG_BAK" ADD PRIMARY KEY ("ID");
View Code

操作步骤

1.创建备份表(上面贴了)

2.声明游标,定义记录变量接收查询出的数据,遍历记录插入到备份表,关闭游标

Oracle定时任务执行存储过程备份日志记录表Oracle定时任务执行存储过程备份日志记录表
 1 declare
 2   --定义游标
 3   cursor cursor_log is
 4     select * from t_s_log where to_char(t_s_log.operatetime,yyyyMMdd) = to_char(sysdate,yyyyMMdd);
 5   --定义记录变量
 6   ls_curinfo cursor_log%rowtype;
 7 begin
 8   open cursor_log;--打开游标
 9   loop
10     FETCH cursor_log
11       INTO ls_curinfo;--获取记录值
12     EXIT WHEN cursor_log%NOTFOUND;
13    insert into t_s_log_bak(ID,
14       BROSWER,
15       LOGCONTENT,
16       LOGLEVEL,
17       NOTE,
18       OPERATETIME,
19       OPERATETYPE,
20       USERID,
21       USERNAME,
22       REALNAME) values(ls_curinfo.ID,
23       ls_curinfo.BROSWER,
24       ls_curinfo.LOGCONTENT,
25       ls_curinfo.LOGLEVEL,
26       ls_curinfo.NOTE,
27       ls_curinfo.OPERATETIME,
28       ls_curinfo.OPERATETYPE,
29       ls_curinfo.USERID,
30       ls_curinfo.USERNAME,
31       ls_curinfo.REALNAME); commit;
32   end loop;
33   close cursor_log;--关闭游标
34 end;
View Code

到这里测试没什么问题就继续创建存储过程

3.创建无参存储过程

存储过程不细说了,大致结构就是:

CREATE OR REPLACE 
procedure 存储过程名字 as
begin
...(过程体)...
end;

过程体就是第2步定义的游标及遍历那部分直接粘过来就可以了

完整的存储过程如下:(这里用Navicat执行时遇到点问题, 改为PL/SQL执行没问题, 不太清楚是什么操作)

Oracle定时任务执行存储过程备份日志记录表Oracle定时任务执行存储过程备份日志记录表
 1 CREATE OR REPLACE 
 2 procedure procedure_log_bak as
 3 begin
 4   declare
 5   --定义游标
 6   cursor cursor_log is
 7     select * from t_s_log where to_char(t_s_log.operatetime,yyyyMMdd) = to_char(sysdate,yyyyMMdd);
 8   --定义记录变量
 9   ls_curinfo cursor_log%rowtype;
10 begin
11   open cursor_log;--打开游标
12   loop
13     FETCH cursor_log
14       INTO ls_curinfo;--获取记录值
15     EXIT WHEN cursor_log%NOTFOUND;
16    insert into t_s_log_bak(ID,
17       BROSWER,
18       LOGCONTENT,
19       LOGLEVEL,
20       NOTE,
21       OPERATETIME,
22       OPERATETYPE,
23       USERID,
24       USERNAME,
25       REALNAME) values(ls_curinfo.ID,
26       ls_curinfo.BROSWER,
27       ls_curinfo.LOGCONTENT,
28       ls_curinfo.LOGLEVEL,
29       ls_curinfo.NOTE,
30       ls_curinfo.OPERATETIME,
31       ls_curinfo.OPERATETYPE,
32       ls_curinfo.USERID,
33       ls_curinfo.USERNAME,
34       ls_curinfo.REALNAME); commit;
35   end loop;
36   close cursor_log;--关闭游标
37 end;
38 end;
View Code

到这里手动执行存储过程也没问题就继续创建定时任务,即Oracle的job

4.创建定时任务

使用PL/SQL找到DBMS_Jobs右键New...不细说了,可以移步https://www.cnblogs.com/yx007/p/6519544.html这篇讲的很详细,这里主要记录一下创建完成后遇到的问题.当创建完成后job并没有执行,Last_date这个字段是空的, 并且Next_date并不是job定义的执行时间.

Oracle定时任务执行存储过程备份日志记录表

 

 到这里需要手动执行job:

Oracle定时任务执行存储过程备份日志记录表

 

 执行之后last_date字段有值了,而且next_date的值也是想要定义的job执行时间了

Oracle定时任务执行存储过程备份日志记录表

 

 到这里就没什么问题了.

感谢

Oracle存储过程

Oracle游标

PL/SQL创建定时任务

Oracle定时器INTERVAI(时间段)写法

Oracle的job不执行解决方法