Oracle_PL/SQL(10) 定时器job

定时器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