Oracle定时任务执行存储过程备份日志记录表
写在前面
需求
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日志表(部分数据)
-- ----------------------------
-- Table structure for T_S_LOG
-- ----------------------------
DROP TABLE "T_S_LOG";
CREATE TABLE "T_S_LOG" (
"ID" NVARCHAR2(32) NOT NULL ,
"BROSWER" NVARCHAR2(100) NULL ,
"LOGCONTENT" NCLOB NOT NULL ,
"LOGLEVEL" NUMBER(6) NULL ,
"NOTE" NCLOB NULL ,
"OPERATETIME" DATE NOT NULL ,
"OPERATETYPE" NUMBER(6) NULL ,
"USERID" NVARCHAR2(32) NULL ,
"USERNAME" NVARCHAR2(50) NULL ,
"REALNAME" NVARCHAR2(50) NULL
)
LOGGING
NOCOMPRESS
NOCACHE ;
COMMENT ON COLUMN "T_S_LOG"."ID" IS 'id';
COMMENT ON COLUMN "T_S_LOG"."BROSWER" IS '???';
COMMENT ON COLUMN "T_S_LOG"."LOGCONTENT" IS '????';
COMMENT ON COLUMN "T_S_LOG"."LOGLEVEL" IS '????';
COMMENT ON COLUMN "T_S_LOG"."NOTE" IS 'IP';
COMMENT ON COLUMN "T_S_LOG"."OPERATETIME" IS '????';
COMMENT ON COLUMN "T_S_LOG"."OPERATETYPE" IS '????';
COMMENT ON COLUMN "T_S_LOG"."USERID" IS '??ID';
COMMENT ON COLUMN "T_S_LOG"."USERNAME" IS '????';
COMMENT ON COLUMN "T_S_LOG"."REALNAME" IS '????'; -- ----------------------------
-- Records of T_S_LOG
-- ----------------------------
INSERT INTO "T_S_LOG" VALUES ('402881f363ba3bfc0163ba3ddd270002', 'Chrome', '入职员工更新成功', '', '本地', TO_DATE('2018-06-01 15:26:46', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员');
INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba41cafe0000', 'Chrome', '用户: admin[JEECG开源社区]common.login.success', '', '192.168.1.115', TO_DATE('2018-06-01 15:31:04', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员');
INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba4234b50001', 'Chrome', '订单主信息删除成功', '', '本地', TO_DATE('2018-06-01 15:31:31', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员');
INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba4270e80002', 'Chrome', '错误异常: NumberFormatException,错误描述:For input string: "2017-10-26 12:00"', '', '本地', TO_DATE('2018-06-01 15:31:46', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员');
INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba42f3ab0008', 'Chrome', '添加成功', '', '本地', TO_DATE('2018-06-01 15:32:20', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员');
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', '', '本地', TO_DATE('2018-06-01 15:32:42', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员');
INSERT INTO "T_S_LOG" VALUES ('402881f363ba41670163ba43946d000b', 'Chrome', '更新成功', '', '本地', TO_DATE('2018-06-01 15:33:01', 'YYYY-MM-DD HH24:MI:SS'), '', '8a8ab0b246dc81120146dc8181950052', 'admin', '管理员'); -- ----------------------------
-- Indexes structure for table T_S_LOG
-- ----------------------------
CREATE INDEX "FK_OE64K4852UYLHYC5A00RFWTAY"
ON "T_S_LOG" ("USERID" ASC)
LOGGING
VISIBLE; -- ----------------------------
-- Checks structure for table T_S_LOG
-- ----------------------------
ALTER TABLE "T_S_LOG" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "T_S_LOG" ADD CHECK ("LOGCONTENT" IS NOT NULL);
ALTER TABLE "T_S_LOG" ADD CHECK ("OPERATETIME" IS NOT NULL); -- ----------------------------
-- Primary Key structure for table T_S_LOG
-- ----------------------------
ALTER TABLE "T_S_LOG" ADD PRIMARY KEY ("ID");
T_S_LOG_BAK备份表
-- ----------------------------
-- Table structure for T_S_LOG_BAK
-- ----------------------------
DROP TABLE "T_S_LOG_BAK";
CREATE TABLE "T_S_LOG_BAK" (
"ID" NVARCHAR2(32) NOT NULL ,
"BROSWER" NVARCHAR2(100) NULL ,
"LOGCONTENT" NCLOB NOT NULL ,
"LOGLEVEL" NUMBER(6) NULL ,
"NOTE" NCLOB NULL ,
"OPERATETIME" DATE NOT NULL ,
"OPERATETYPE" NUMBER(6) NULL ,
"USERID" NVARCHAR2(32) NULL ,
"USERNAME" NVARCHAR2(50) NULL ,
"REALNAME" NVARCHAR2(50) NULL
)
LOGGING
NOCOMPRESS
NOCACHE ;
COMMENT ON COLUMN "T_S_LOG_BAK"."ID" IS 'id';
COMMENT ON COLUMN "T_S_LOG_BAK"."BROSWER" IS '???';
COMMENT ON COLUMN "T_S_LOG_BAK"."LOGCONTENT" IS '????';
COMMENT ON COLUMN "T_S_LOG_BAK"."LOGLEVEL" IS '????';
COMMENT ON COLUMN "T_S_LOG_BAK"."NOTE" IS 'IP';
COMMENT ON COLUMN "T_S_LOG_BAK"."OPERATETIME" IS '????';
COMMENT ON COLUMN "T_S_LOG_BAK"."OPERATETYPE" IS '????';
COMMENT ON COLUMN "T_S_LOG_BAK"."USERID" IS '??ID';
COMMENT ON COLUMN "T_S_LOG_BAK"."USERNAME" IS '????';
COMMENT ON COLUMN "T_S_LOG_BAK"."REALNAME" IS '????'; -- ----------------------------
-- Indexes structure for table T_S_LOG_BAK
-- ----------------------------
CREATE INDEX "FK_OE64K4852UYLHYC5A00RFWTAY"
ON "T_S_LOG_BAK" ("USERID" ASC)
LOGGING
VISIBLE; -- ----------------------------
-- Checks structure for table T_S_LOG_BAK
-- ----------------------------
ALTER TABLE "T_S_LOG_BAK" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "T_S_LOG_BAK" ADD CHECK ("LOGCONTENT" IS NOT NULL);
ALTER TABLE "T_S_LOG_BAK" ADD CHECK ("OPERATETIME" IS NOT NULL); -- ----------------------------
-- Primary Key structure for table T_S_LOG_BAK
-- ----------------------------
ALTER TABLE "T_S_LOG_BAK" ADD PRIMARY KEY ("ID");
操作步骤
1.创建备份表(上面贴了)
2.声明游标,定义记录变量接收查询出的数据,遍历记录插入到备份表,关闭游标
declare
--定义游标
cursor cursor_log is
select * from t_s_log where to_char(t_s_log.operatetime,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd');
--定义记录变量
ls_curinfo cursor_log%rowtype;
begin
open cursor_log;--打开游标
loop
FETCH cursor_log
INTO ls_curinfo;--获取记录值
EXIT WHEN cursor_log%NOTFOUND;
insert into t_s_log_bak(ID,
BROSWER,
LOGCONTENT,
LOGLEVEL,
NOTE,
OPERATETIME,
OPERATETYPE,
USERID,
USERNAME,
REALNAME) values(ls_curinfo.ID,
ls_curinfo.BROSWER,
ls_curinfo.LOGCONTENT,
ls_curinfo.LOGLEVEL,
ls_curinfo.NOTE,
ls_curinfo.OPERATETIME,
ls_curinfo.OPERATETYPE,
ls_curinfo.USERID,
ls_curinfo.USERNAME,
ls_curinfo.REALNAME); commit;
end loop;
close cursor_log;--关闭游标
end;
到这里测试没什么问题就继续创建存储过程
3.创建无参存储过程
存储过程不细说了,大致结构就是:
CREATE OR REPLACE
procedure 存储过程名字 as
begin
...(过程体)...
end;
过程体就是第2步定义的游标及遍历那部分直接粘过来就可以了
完整的存储过程如下:(这里用Navicat执行时遇到点问题, 改为PL/SQL执行没问题, 不太清楚是什么操作)
CREATE OR REPLACE
procedure procedure_log_bak as
begin
declare
--定义游标
cursor cursor_log is
select * from t_s_log where to_char(t_s_log.operatetime,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd');
--定义记录变量
ls_curinfo cursor_log%rowtype;
begin
open cursor_log;--打开游标
loop
FETCH cursor_log
INTO ls_curinfo;--获取记录值
EXIT WHEN cursor_log%NOTFOUND;
insert into t_s_log_bak(ID,
BROSWER,
LOGCONTENT,
LOGLEVEL,
NOTE,
OPERATETIME,
OPERATETYPE,
USERID,
USERNAME,
REALNAME) values(ls_curinfo.ID,
ls_curinfo.BROSWER,
ls_curinfo.LOGCONTENT,
ls_curinfo.LOGLEVEL,
ls_curinfo.NOTE,
ls_curinfo.OPERATETIME,
ls_curinfo.OPERATETYPE,
ls_curinfo.USERID,
ls_curinfo.USERNAME,
ls_curinfo.REALNAME); commit;
end loop;
close cursor_log;--关闭游标
end;
end;
到这里手动执行存储过程也没问题就继续创建定时任务,即Oracle的job
4.创建定时任务
使用PL/SQL找到DBMS_Jobs右键New...不细说了,可以移步https://www.cnblogs.com/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||'日志备份成功';
最新的存储过程为:
CREATE OR REPLACE
procedure procedure_log_bak as
gen_guid varchar2(100);
bak_date varchar2(100);
begin
select sys_guid() into gen_guid from dual;
select to_char(sysdate,'yyyyMMdd') into bak_date from dual;
bak_date := bak_date||'日志备份成功';
declare
--定义游标
cursor cursor_log is
select * from t_s_log where to_char(t_s_log.operatetime,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd');
--定义记录变量
ls_curinfo cursor_log%rowtype;
begin
open cursor_log;--打开游标
loop
FETCH cursor_log
INTO ls_curinfo;--获取记录值
EXIT WHEN cursor_log%NOTFOUND;
insert into t_s_log_bak(ID,
BROSWER,
LOGCONTENT,
LOGLEVEL,
NOTE,
OPERATETIME,
OPERATETYPE,
USERID,
USERNAME,
REALNAME) values(ls_curinfo.ID,
ls_curinfo.BROSWER,
ls_curinfo.LOGCONTENT,
ls_curinfo.LOGLEVEL,
ls_curinfo.NOTE,
ls_curinfo.OPERATETIME,
ls_curinfo.OPERATETYPE,
ls_curinfo.USERID,
ls_curinfo.USERNAME,
ls_curinfo.REALNAME); commit;
end loop;
close cursor_log;--关闭游标
end;
insert into t_s_log(id,logcontent,operatetime) values(gen_guid,bak_date,sysdate);
end;
同样,修改后用PL/SQL执行即可修改成功.
感谢
Oracle存储过程
Oracle游标
PL/SQL创建定时任务
Oracle定时器INTERVAI(时间段)写法
Oracle的job不执行解决方法
最新文章
- QQ互联登录以及非官方正版应用报100044错误
- sass中级语法
- 高性能的分布式内存对象缓存系统Memcached
- 每日一九度之 题目1038:Sum of Factorials
- HashMap和HashSet的区别
- CSS - 实现文字显示过长时用省略
- (原创)LAMP教程1-下载虚拟机软件
- 【产品体验】喵街&;飞凡
- hadoop深入研究:(十六)——Avro序列化与反序列化
- 2015第6周三ztree的使用
- 《HBase权威指南》读书笔记----简介
- Blend4精选案例图解教程(四):请给我路径指引
- java离request获取当前从访问完成url至
- 卸载php+apache+mysql
- JavaScript ECMAScript版本介绍
- 关于Python, ftplib模块中的cwd()进入含中文目录失败的问题
- 分享我编写的powershell脚本:ssh-copy-id.ps1
- 奇怪吸引子---Qi
- Sep 10th 2018
- 安装Thinkphp5