oracle触发器、序列、任务计划练习一例

今天在闲暇时间练习了一下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_tbltimestart with 1increment by 1nomaxvaluenocyclecache 20

3.创建触发器 tr_tbltimeseq

create or replace trigger tr_tbltimeseq/*功能描述：在插入数据之前利用seq_tbltime序列使表tbl_time(id)实现递增*/before insert on tbl_timefor 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插入当前时间点*/asd_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