写在前面
需求
1.备份系统日志表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日志表(部分数据)
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 LOGGING18 NOCOMPRESS19 NOCACHE20 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_LOG35 -- ----------------------------36 INSERT INTO "T_S_LOG" VALUES ('402881f363ba3bfc0163ba3ddd270002', 'Chrome', '入职员工更新成功', '5', '本地', TO_DATE('-06-01 15:26:46', 'YYYY-MM-DD HH24:MI:SS'), '1', '8a8ab0b246dc8116dc8181950052', 'admin', '管理员');37 INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba41cafe0000', 'Chrome', '用户: admin[JEECG开源社区]common.login.success', '1', '192.168.1.115', TO_DATE('-06-01 15:31:04', 'YYYY-MM-DD HH24:MI:SS'), '1', '8a8ab0b246dc8116dc8181950052', 'admin', '管理员');38 INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba4234b50001', 'Chrome', '订单主信息删除成功', '4', '本地', TO_DATE('-06-01 15:31:31', 'YYYY-MM-DD HH24:MI:SS'), '1', '8a8ab0b246dc8116dc8181950052', 'admin', '管理员');39 INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba4270e80002', 'Chrome', '错误异常: NumberFormatException,错误描述：For input string: "-10-26 12:00"', '6', '本地', TO_DATE('-06-01 15:31:46', 'YYYY-MM-DD HH24:MI:SS'), '3', '8a8ab0b246dc8116dc8181950052', 'admin', '管理员');40 INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba42f3ab0008', 'Chrome', '添加成功', '3', '本地', TO_DATE('-06-01 15:32:20', 'YYYY-MM-DD HH24:MI:SS'), '1', '8a8ab0b246dc8116dc8181950052', '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('-06-01 15:32:42', 'YYYY-MM-DD HH24:MI:SS'), '3', '8a8ab0b246dc8116dc8181950052', 'admin', '管理员');42 INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba43946d000b', 'Chrome', '更新成功', '5', '本地', TO_DATE('-06-01 15:33:01', 'YYYY-MM-DD HH24:MI:SS'), '1', '8a8ab0b246dc8116dc8181950052', 'admin', '管理员');43 44 -- ----------------------------45 -- Indexes structure for table T_S_LOG46 -- ----------------------------47 CREATE INDEX "FK_OE64K4852UYLHYC5A00RFWTAY"48 ON "T_S_LOG" ("USERID" ASC)49 LOGGING50 VISIBLE;51 52 -- ----------------------------53 -- Checks structure for table T_S_LOG54 -- ----------------------------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_LOG61 -- ----------------------------62 ALTER TABLE "T_S_LOG" ADD PRIMARY KEY ("ID");
View Code
T_S_LOG_BAK备份表
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 LOGGING18 NOCOMPRESS19 NOCACHE20 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_BAK35 -- ----------------------------36 CREATE INDEX "FK_OE64K4852UYLHYC5A00RFWTAY"37 ON "T_S_LOG_BAK" ("USERID" ASC)38 LOGGING39 VISIBLE;40 41 -- ----------------------------42 -- Checks structure for table T_S_LOG_BAK43 -- ----------------------------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_BAK50 -- ----------------------------51 ALTER TABLE "T_S_LOG_BAK" ADD PRIMARY KEY ("ID");
View Code
操作步骤
1.创建备份表(上面贴了)
2.声明游标,定义记录变量接收查询出的数据,遍历记录插入到备份表,关闭游标
1 declare 2 --定义游标 3 cursor cursor_log is 4select * 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 loop10FETCH cursor_log11 INTO ls_curinfo;--获取记录值12EXIT 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 存储过程名字 asbegin...(过程体)...end;
过程体就是第2步定义的游标及遍历那部分直接粘过来就可以了
完整的存储过程如下:(这里用Navicat执行时遇到点问题, 改为PL/SQL执行没问题, 不太清楚是什么操作)
1 CREATE OR REPLACE 2 procedure procedure_log_bak as 3 begin 4 declare 5 --定义游标 6 cursor cursor_log is 7select * from t_s_log where to_char(t_s_log.operatetime,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd'); 8 --定义记录变量 9 ls_curinfo cursor_log%rowtype;10 begin11 open cursor_log;--打开游标12 loop13FETCH cursor_log14 INTO ls_curinfo;--获取记录值15EXIT 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...不细说了,可以移步/yx007/p/6519544.html这篇讲的很详细,这里主要记录一下创建完成后遇到的问题.当创建完成后job并没有执行,Last_date这个字段是空的, 并且Next_date并不是job定义的执行时间.
到这里需要手动执行job:
执行之后last_date字段有值了,而且next_date的值也是想要定义的job执行时间了
到这里就没什么问题了.
新需求补充
2.在日志表T_S_LOG中插入一条记录logcontent字段为yyyyMMdd日志备份成功.(后来提的需求)
思路
在存储过程的最后, 也就是遍历游标的结束后新增insert语句即可.
不细说了,只说一下变量的拼接是用的||符号.例如:bak_date := bak_date||'日志备份成功';
最新的存储过程为:
1 CREATE OR REPLACE 2 procedure procedure_log_bak as 3 gen_guid varchar2(100); 4 bak_date varchar2(100); 5 begin 6 select sys_guid() into gen_guid from dual; 7 select to_char(sysdate,'yyyyMMdd') into bak_date from dual; 8 bak_date := bak_date||'日志备份成功'; 9 declare10 --定义游标11 cursor cursor_log is12select * from t_s_log where to_char(t_s_log.operatetime,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd');13 --定义记录变量14 ls_curinfo cursor_log%rowtype;15 begin16 open cursor_log;--打开游标17 loop18FETCH cursor_log19 INTO ls_curinfo;--获取记录值20EXIT WHEN cursor_log%NOTFOUND;21 insert into t_s_log_bak(ID,22 BROSWER,23 LOGCONTENT,24 LOGLEVEL,25 NOTE,26 OPERATETIME,27 OPERATETYPE,28 USERID,29 USERNAME,30 REALNAME) values(ls_curinfo.ID,31 ls_curinfo.BROSWER,32 ls_curinfo.LOGCONTENT,33 ls_curinfo.LOGLEVEL,34 ls_curinfo.NOTE,35 ls_curinfo.OPERATETIME,36 ls_curinfo.OPERATETYPE,37 ls_curinfo.USERID,38 ls_curinfo.USERNAME,39 ls_curinfo.REALNAME); commit;40 end loop;41 close cursor_log;--关闭游标42 end;43 insert into t_s_log(id,logcontent,operatetime) values(gen_guid,bak_date,sysdate);44 end;
View Code
同样,修改后用PL/SQL执行即可修改成功.
