寫在前面

需求

1.備份系統(tǒng)日志表T_S_LOG, 按照操作時間字段OPERATETIME, 將每天的日志增量備份到另一張表.

思路

1.創(chuàng)建一張數(shù)據(jù)結(jié)構(gòu)完全相同的表T_S_LOG_BAK作為備份表

2.查出T_S_LOG中需要備份的數(shù)據(jù)

3.將數(shù)據(jù)賦給游標變量

4.遍歷游標將數(shù)據(jù)逐條插入T_S_LOG_BAK

5.創(chuàng)建無參存儲過程將游標的這部分操作作為存儲過程主體執(zhí)行

6.創(chuàng)建定時任務(wù)定時執(zhí)行該存儲過程

操作環(huán)境

Oracle11g

T_S_LOG日志表(部分數(shù)據(jù))

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開源社區(qū)]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備份表

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.創(chuàng)建備份表(上面貼了)

2.聲明游標,定義記錄變量接收查詢出的數(shù)據(jù),遍歷記錄插入到備份表,關(guān)閉游標

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;--關(guān)閉游標 34 end; View Code

到這里測試沒什么問題就繼續(xù)創(chuàng)建存儲過程

3.創(chuàng)建無參存儲過程

存儲過程不細說了,大致結(jié)構(gòu)就是:

CREATE OR REPLACE procedure 存儲過程名字 as begin ...(過程體)... end;

過程體就是第2步定義的游標及遍歷那部分直接粘過來就可以了

完整的存儲過程如下:(這里用Navicat執(zhí)行時遇到點問題, 改為PL/SQL執(zhí)行沒問題, 不太清楚是什么操作)

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;--關(guān)閉游標 37 end; 38 end; View Code

到這里手動執(zhí)行存儲過程也沒問題就繼續(xù)創(chuàng)建定時任務(wù),即Oracle的job

4.創(chuàng)建定時任務(wù)

使用PL/SQL找到DBMS_Jobs右鍵New...不細說了,可以移步https://www.cnblogs.com/yx007/p/6519544.html這篇講的很詳細,這里主要記錄一下創(chuàng)建完成后遇到的問題.當創(chuàng)建完成后job并沒有執(zhí)行,Last_date這個字段是空的, 并且Next_date并不是job定義的執(zhí)行時間.

?

?到這里需要手動執(zhí)行job:

?

?執(zhí)行之后last_date字段有值了,而且next_date的值也是想要定義的job執(zhí)行時間了

?到這里就沒什么問題了.

新需求補充

2.在日志表T_S_LOG中插入一條記錄logcontent字段為yyyyMMdd日志備份成功.(后來提的需求)

思路

在存儲過程的最后, 也就是遍歷游標的結(jié)束后新增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 declare 10 --定義游標 11 cursor cursor_log is 12 select * from t_s_log where to_char(t_s_log.operatetime,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd'); 13 --定義記錄變量 14 ls_curinfo cursor_log%rowtype; 15 begin 16 open cursor_log;--打開游標 17 loop 18 FETCH cursor_log 19 INTO ls_curinfo;--獲取記錄值 20 EXIT 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;--關(guān)閉游標 42 end; 43 insert into t_s_log(id,logcontent,operatetime) values(gen_guid,bak_date,sysdate); 44 end; View Code

同樣,修改后用PL/SQL執(zhí)行即可修改成功.

感謝

Oracle存儲過程

Oracle游標

PL/SQL創(chuàng)建定時任務(wù)

Oracle定時器INTERVAI(時間段)寫法

Oracle的job不執(zhí)行解決方法

?

轉(zhuǎn)載于:https://www.cnblogs.com/yadongliang/p/11555903.html

總結(jié)

以上是生活随笔為你收集整理的Oracle定时任务执行存储过程备份日志记录表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。