定时器job

1.定义

定时器指在特定的时间执行特定的操作。

可以多次执行。

说明：

特定的操作：指一个完成特定功能的存储过程。

多次执行:指可以每分钟、每小时、每天、每周、每月、每季度、每年等周期性的运行。

2.准备工作

源表:student,course,sc

create table student

(

sno number(6) not null primary key,

sname varchar2(20),

birth date

);

create table course

(

cno number(6) not null primary key,

cname varchar2(20),

teacher varchar2(20)

);

create table sc

(

sno number(6) not null,

cno number(6) not null,

grade number(6)

);

alter table sc add constraint pk_sc primary key (sno, cno);

--数据

insert into student (sno, sname, birth) values (1, 'sname1', to_date('06-05-1994', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (2, 'sname2', to_date('13-11-1995', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (3, 'sname3', to_date('02-08-1995', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (4, 'sname4', to_date('18-10-1990', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (5, 'sname5', to_date('11-10-1992', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (6, 'sname6', to_date('06-09-1993', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (7, 'sname7' , to_date('10-10-1994', 'dd-mm-yyyy'));

insert into student (sno, sname, birth) values (8, 'sname8', to_date('16-10-1992', 'dd-mm-yyyy'));

insert into course (cno, cname, teacher) values (1, '数学', '张三');

insert into course (cno, cname, teacher) values (2, '语文', '李四');

insert into course (cno, cname, teacher) values (3, '英语', '王五');

insert into sc (sno, cno, grade) values (1, 1, 80);

insert into sc (sno, cno, grade) values (1, 2, 75);

insert into sc (sno, cno, grade) values (2, 1, 69);

insert into sc (sno, cno, grade) values (2, 3, 85);

insert into sc (sno, cno, grade) values (3, 2, 98);

insert into sc (sno, cno, grade) values (3, 3, 73);

insert into sc (sno, cno, grade) values (4, 1, 55);

insert into sc (sno, cno, grade) values (4, 2, 76);

insert into sc (sno, cno, grade) values (4, 3, 80);

insert into sc (sno, cno, grade) values (5, 1, 58);

insert into sc (sno, cno, grade) values (5, 2, 79);

insert into sc (sno, cno, grade) values (5, 3, 65);

insert into sc (sno, cno, grade) values (6, 2, 78);

insert into sc (sno, cno, grade) values (6, 3, 86);

insert into sc (sno, cno, grade) values (7, 1, 68);

insert into sc (sno, cno, grade) values (8, 1, 90);

目标表：s_sc

create table s_sc

(

sname varchar2(20) not null primary key,

c_grade number(6),

m_grade number(6),

e_grade number(6)

);

--过程：proc_ssc_insert

create or replace procedure proc_ssc_insert

is

v_cnt number;

begin

for rs in (select sno,sname from student) loop

select count(1) into v_cnt from s_sc where sname=rs.sname;

if v_cnt=0 then

insert into s_sc (sname) values (rs.sname);

end if;

for rs2 in (select grade,cname from sc,course

where sc.cno=course.cno and sno=rs.sno) loop

if rs2.cname='语文' then

update s_sc set C_GRADE=rs2.grade where sname=rs.sname;

elsif rs2.cname='数学' then

update s_sc set M_GRADE=rs2.grade where sname=rs.sname;

elsif rs2.cname='英语' then

update s_sc set E_GRADE=rs2.grade where sname=rs.sname;

end if;

end loop;

end loop;

end;

/

show err;

检查数据：

select * from student

select * from course

select * from sc

select * from s_sc

3.创建job

variable jobid number;

begin

sys.dbms_job.submit(job => :jobid,

what => 'proc_ssc_insert;',

next_date => to_date('2018-05-14 10:45:00', 'yyyy-mm-dd hh24:mi:ss'),

interval => 'TRUNC(sysdate)+1+1/(24)');

commit;

end;

/

var jobid number;

begin

dbms_job.submit(:jobid,'proc_ssc_insert;',trunc(sysdate)+16/24,'trunc(sysdate)+1+16/24');

end;

/

show err;

4.执行间隔interval

指可以每分钟、每小时、每天、每周、每月、每季度、每年等周期性的运行。

4.1:每分钟执行

interval => trunc(sysdate,'mi') + 1/(24*60)

每5分钟执行

interval => trunc(sysdate,'mi') + 5/(24*60)

4.1:每小时执行

interval => trunc(sysdate,'hh24') + 1/(24)

4.2:每天定时执行

例如：每天的凌晨1点执行

interval => trunc(sysdate)+1+1/(24)

4.3:每周定时执行

例如：每周一凌晨1点执行

interval => trunc(next_day(sysdate,'星期一'))+1/24

4.4:每月定时执行

例如：每月1日凌晨1点执行

interval =>trunc(last_day(sysdate))+1+1/24

interval =>trunc(add_months(sysdate,1),'mm')+1/24

4.5:每季度定时执行

例如：每季度的第一天凌晨1点执行

interval => trunc(add_months(sysdate,3),'q') + 1/24

4.6:每年定时执行

例如：每年1月1日凌晨1点执行

interval =>add_months(trunc(sysdate,'yyyy'),12)+1/24

5.数据字典中job的相关视图

job信息

select * from user_jobs;

正在运行的job

select * from dba_jobs_running;

6.运行job

begin

dbms_job.run(:job);

end;

/

7.删除job

exec dbms_job.remove(:job);

commit;

举例:

exec dbms_job.remove(23);

commit;--不加commit是删不掉的

8.中止job

exec dbms_job.broken(:job,true);

9.修改job

dbms_job.change(:job,:what,:next_date,:interval);

dbms_job.what(:job,:what);

dbms_job.next_date(:job,:next_date);

dbms_job.instance(:job,:instance);

dbms_job.interval(:job,:interval);

dbms_job.broken(:job,:broken,:next_date);

10.job相关的功能。

SQL> desc dbms_job;

Element Type

------------------ ---------

ANY_INSTANCE CONSTANT

ISUBMIT PROCEDURE

SUBMIT PROCEDURE 创建job

REMOVE PROCEDURE 删除job

CHANGE PROCEDURE 修改job

WHAT PROCEDURE 修改调用的过程

NEXT_DATE PROCEDURE 修改下次运行时间

INSTANCE PROCEDURE

INTERVAL PROCEDURE 修改运行间隔

BROKEN PROCEDURE 中止job

RUN PROCEDURE 运行job

USER_EXPORT PROCEDURE

BACKGROUND_PROCESS FUNCTION

IS_JOBQ FUNCTION