|
定时器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,birth) values (2,‘sname2‘,to_date(‘13-11-1995‘,birth) values (3,‘sname3‘,to_date(‘02-08-1995‘,birth) values (4,‘sname4‘,to_date(‘18-10-1990‘,birth) values (5,‘sname5‘,to_date(‘11-10-1992‘,birth) values (6,‘sname6‘,to_date(‘06-09-1993‘,birth) values (7,‘sname7‘,to_date(‘10-10-1994‘,birth) values (8,‘sname8‘,to_date(‘16-10-1992‘,‘dd-mm-yyyy‘));
insert into course (cno,cname,teacher) values (1,‘数学‘,‘张三‘);
insert into course (cno,teacher) values (2,‘语文‘,‘李四‘);
insert into course (cno,teacher) values (3,‘英语‘,‘王五‘);
insert into sc (sno,cno,grade) values (1,1,80);
insert into sc (sno,2,75);
insert into sc (sno,grade) values (2,69);
insert into sc (sno,3,85);
insert into sc (sno,grade) values (3,98);
insert into sc (sno,73);
insert into sc (sno,grade) values (4,55);
insert into sc (sno,76);
insert into sc (sno,grade) values (5,58);
insert into sc (sno,79);
insert into sc (sno,65);
insert into sc (sno,grade) values (6,78);
insert into sc (sno,86);
insert into sc (sno,grade) values (7,68);
insert into sc (sno,grade) values (8,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 创建jobREMOVE PROCEDURE 删除jobCHANGE PROCEDURE 修改jobWHAT PROCEDURE 修改调用的过程NEXT_DATE PROCEDURE 修改下次运行时间INSTANCE PROCEDURE INTERVAL PROCEDURE 修改运行间隔BROKEN PROCEDURE 中止jobRUN PROCEDURE 运行jobUSER_EXPORT PROCEDURE BACKGROUND_PROCESS FUNCTION IS_JOBQ FUNCTION
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
