Oracle定时任务执行存储过程备份日志记录表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle定时任务执行存储过程备份日志记录表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8994字，纯文字阅读大概需要13分钟。
内容图文
-- ----------------------------
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备份表
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.声明游标,定义记录变量接收查询出的数据,遍历记录插入到备份表,关闭游标
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执行没问题, 不太清楚是什么操作)
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定义的执行时间.
到这里需要手动执行job:
执行之后last_date字段有值了,而且next_date的值也是想要定义的job执行时间了
到这里就没什么问题了.
感谢
Oracle存储过程
Oracle游标
PL/SQL创建定时任务
Oracle定时器INTERVAI(时间段)写法
Oracle的job不执行解决方法
Oracle定时任务执行存储过程备份日志记录表
标签：drop pen out sda 11g value spl 思路 row
本文系统来源：https://www.cnblogs.com/yadongliang/p/11555903.html
内容总结
以上是互联网集市为您收集整理的Oracle定时任务执行存储过程备份日志记录表全部内容，希望文章能够帮你解决Oracle定时任务执行存储过程备份日志记录表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】