今天在闲暇时间练习了一下oracle任务计划,具体详情如下
1.创建表 TBL_TIME
create table tbl_time( id number not null, /*id号*/ vsecond varchar2(2), /* 秒*/ vtime varchar2(10) /*当前时间*/ )
2.创建序列 seq_tbltime
create sequence seq_tbltime start with 1 increment by 1 nomaxvalue nocycle cache 20
3.创建触发器 tr_tbltimeseq
create or replace trigger tr_tbltimeseq /* 功能描述:在插入数据之前利用seq_tbltime 序列使表tbl_time(id)实现递增 */ before insert on tbl_time for each row begin select seq_tbltime.nextval into :new.id from dual; end tr_tbltimeseq;
4.创建存储过程 proc_addtime
create or replace procedure proc_addtime /* 功能描述:在一分钟之内每过5秒钟向 表tbl_time插入当前时间点 */ as d_time1 date; d_time2 date; n_timediff number(2); i number(2); begin select sysdate into d_time1 from dual; insert into tbl_time values(1,to_char(d_time1,'ss'),to_char(d_time1,'yyyymmddhhss')); i:=5; while i<=60 loop select sysdate into d_time2 from dual; select round(to_number(d_time2 - d_time1) * 24 * 60 * 60) into n_timediff from dual; if n_timediff=i then insert into tbl_time values(1,to_char(d_time2,'ss'),to_char(d_time2,'yyyymmddhhss')); i:=i+5; end if; end loop; exception when others then rollback; commit; end;
5.创建任务计划
variable n number; /*添加任务计划,该计划立即开始,之后每五分钟执行一次计划任务*/ begin dbms_job.submit(:n,'proc_addtime;',sysdate,'sysdate + 5/(24*60)'); commit; end;
执行结果如下
SQL> select * from tbl_time; ID VSECOND VTIME ---------- ------- -------------------- 1 09 201407030509 2 14 201407030514 3 19 201407030519 4 24 201407030524 5 29 201407030529 6 34 201407030534 7 39 201407030539 8 44 201407030544 9 49 201407030549
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。