oracle觸發(fā)器、序列、任務(wù)計(jì)劃練習(xí)一例
今天在閑暇時(shí)間練習(xí)了一下oracle任務(wù)計(jì)劃，具體詳情如下
泗洪ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書(shū)銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18982081108（備注：SSL證書(shū)合作）期待與您的合作！
1.創(chuàng)建表 TBL_TIME
create table tbl_time( id number not null, /*id號(hào)*/ vsecond varchar2(2), /* 秒*/ vtime varchar2(10) /*當(dāng)前時(shí)間*/ )
2.創(chuàng)建序列 seq_tbltime
create sequence seq_tbltime start with 1 increment by 1 nomaxvalue nocycle cache 20
3.創(chuàng)建觸發(fā)器 tr_tbltimeseq
create or replace trigger tr_tbltimeseq /* 功能描述：在插入數(shù)據(jù)之前利用seq_tbltime 序列使表tbl_time(id)實(shí)現(xiàn)遞增 */ before insert on tbl_time for each row begin select seq_tbltime.nextval into :new.id from dual; end tr_tbltimeseq;
4.創(chuàng)建存儲(chǔ)過(guò)程 proc_addtime
create or replace procedure proc_addtime /* 功能描述：在一分鐘之內(nèi)每過(guò)5秒鐘向 表tbl_time插入當(dāng)前時(shí)間點(diǎn) */ 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.創(chuàng)建任務(wù)計(jì)劃
variable n number; /*添加任務(wù)計(jì)劃，該計(jì)劃立即開(kāi)始，之后每五分鐘執(zhí)行一次計(jì)劃任務(wù)*/ begin dbms_job.submit(:n,'proc_addtime;',sysdate,'sysdate + 5/(24*60)'); commit; end;
執(zhí)行結(jié)果如下
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
分享名稱：oracle觸發(fā)器、序列、任務(wù)計(jì)劃練習(xí)一例
標(biāo)題網(wǎng)址：http://m.jiaotiyi.com/article/ghopgh.html