mysql和oracle计划任务
-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;
-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';
注意:如果想要始终开启事件,那么在使用SET GLOBAL开启事件后,还需要在my.ini(Windows系统)/my.cnf(Linux系统)中添加event_scheduler=on。因为如果没有添加,MySQL重启事件后又会回到原来的状态。
CREATE
[DEFINER={user | CURRENT_USER}]
EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
在ON SCHEDULE子句中,参数schedule的值为一个AS子句,用于指定事件在某个时刻发生,其语法格式如下:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
参数说明:
(1)timestamp:表示一个具体的时间点,后面加上一个时间间隔,表示在这个时间间隔后事件发生。
(2)EVERY子句:用于表示事件在指定时间区间内每隔多长时间发生一次,其中 STARTS子句用于指定开始时间;ENDS子句用于指定结束时间。
(3)interval:表示一个从现在开始的时间,其值由一个数值和单位构成。例如,使用“4 WEEK”表示4周;使用“‘1:10’ HOUR_MINUTE”表示1小时10分钟。间隔的距离用DATE_ADD()函数来支配。
interval参数值的语法格式如下:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
一些常用的时间间隔设置:
(1)每隔5秒钟执行
ON SCHEDULE EVERY 5 SECOND
(2)每隔1分钟执行
ON SCHEDULE EVERY 1 MINUTE
(3)每天凌晨1点执行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
(4)每个月的第一天凌晨1点执行
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)
(5)每 3 个月,从现在起一周后开始
ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK
(6)每十二个小时,从现在起三十分钟后开始,并于现在起四个星期后结束
ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
查询事件
在MySQL中可以通过查询information_schema.events表,查看已创建的事件。其语句如下:
SELECT * FROM information_schema.events;
启动与关闭事件
另外ALTER EVENT语句还有一个用法就是让一个事件关闭或再次活动。
示例:启动名称为event_user的事件。
ALTER EVENT event_user ENABLE;
示例:关闭名称为event_user的事件。
ALTER EVENT event_user DISABLE;
修改事件
在MySQL 5.1及以后版本中,事件被创建之后,还可以使用ALTER EVENT语句修改其定义和相关属性。其语法如下:
ALTER
[DEFINER={user | CURRENT_USER}]
EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
删除事件
在MySQL 5.1及以后版本中,删除已经创建的事件可以使用DROP EVENT语句来实现。
示例:删除名称为event_user的事件。
DROP EVENT IF EXISTS event_user;
Oracle中的定时任务
一、了解什么是定时任务?
job是oracle的定时任务,又叫定时器,定时作业,作业定时地自动执行一些脚本,或作数据备份,或作数据提炼,或作数据库性能的优化,或作重建索引等等的工作,需要用到job。
Job是一种被调度执行的任务。Job可以是一个PL/SQL块、一个SQL语句、一个外部脚本或程序等。它们可以被定时调度执行,也可以被手动启动执行。
二、创建job
Ⅰ、语法一
declare v_job_id number; begin dbms_job.submit(job =>v_job_id, --job号 what =>'pro_name/DML;', --定时执行的脚本(简称你要干什么) next_date=>sysdate+1, --第一次执行的时间 interval =>'SYSDATE+1/24/60' --间隔时间 ); --commit; end;
该语法是使用dbms_job包提交一个定时任务:
1. `declare`和`begin`是PL/SQL代码块的开始和结束标志。
2. `v_job_id`是一个变量,用于存储job的id号。
3. `dbms_job.submit`是提交一个job的过程,包括以下参数:
- `job`:job的id号,由Oracle自动生成。
- `what`:定时执行的脚本,可以是一个存储过程或SQL语句。
- `next_date`:job第一次执行的时间,可以是一个日期类型的变量或者表达式。
- `interval`:job的执行间隔时间,可以是一个日期类型的变量或者表达式,例如`SYSDATE+1/24/60`表示每隔1分钟执行一次。
4. `commit`是一个事务提交语句,用于将提交的job保存到数据库中。
注意:
使用dbms_job提交的job只能在Oracle数据库中执行,不能跨数据库执行。另外,使用dbms_job提交的job在Oracle 10g及以上版本中已经被废弃,推荐使用dbms_scheduler包提交job。
比如创建定时任务,每分钟执行一次pkg_2.p1,向emp2表中插入员工编号为7788的员工信息:
declare v1 number; begin dbms_job.submit(job => v1, what => 'insert into emp2 select * from emp where empno=7788;', next_date => sysdate,--立即执行 interval => 'SYSDATE+1/24/60'); commit; end;
select sysdate from dual;
Ⅱ、语法二:
使用DBMS_SCHEDULER包来创建和管理job,具体语法如下:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'job_name', -- job的名称 job_type => 'PLSQL_BLOCK', -- job的类型,可以是PLSQL_BLOCK、STORED_PROCEDURE等 job_action => 'begin my_proc(); end;', -- job执行的脚本或存储过程 start_date => SYSTIMESTAMP, -- job开始执行的时间 repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- job执行的间隔时间 enabled => TRUE -- 是否启用job ); END; /
DBMS_SCHEDULER包提供了丰富的job管理功能,可以设置job的执行时间、执行间隔、执行次数、执行优先级、并发控制等属性,实际应用中可以根据具体需求灵活配置。
三、查看job
select * from user_jobs;
其中user_jobs是一个视图,是 Oracle 数据库中的一个系统表,它用于存储由 DBMS_JOB.SUBMIT 提交的作业(job)的信息。该表包含了提交的作业的 ID 号、作业的描述、作业的下一次执行时间、作业的执行间隔时间、作业的状态等信息。用户可以查询该表来获取作业的信息,也可以使用该表来管理作业的状态、修改作业的执行时间等。
注意:
该表只能查看和管理由当前用户提交的作业,不能查看和管理其他用户提交的作业。
其实在Oracle中,可以使用以下SQL语句来查看定时任务Job的信息:
SELECT job_name, job_type, enabled, state, last_start_date, next_run_date FROM dba_scheduler_jobs;
该语句会列出所有的Job,包括Job的名称、类型、是否启用、状态、上次执行时间和下次执行时间等信息。其中,dba_scheduler_jobs是一个系统视图,可以查看所有的Job信息。如果只需要查看当前用户的Job,可以使用USER_SCHEDULER_JOBS视图。
另外,也可以使用以下SQL语句来查看某个Job的详细信息:
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'job_name';
该语句会列出指定Job的所有信息,包括Job的类型、执行时间、重复间隔、执行程序等。
四、删除Job
Ⅰ、调用dbms_job.remove实现:
call dbms_job.remove(23); commit;
其中dbms_job.remove是一个包名,是Oracle 数据库中的一个过程,用于删除一个已经存在的作业(job)。它的语法如下:
DBMS_JOB.REMOVE ( job IN BINARY_INTEGER );
其中,job 参数表示要删除的作业的 ID 号。调用该过程后,指定 ID 号的作业将被从数据库中删除。
注意:
该过程只能删除由 DBMS_JOB.SUBMIT 提交的作业,不能删除由 DBMS_SCHEDULER.SUBMIT 创建的作业。
Ⅱ、使用下面语句完成job删除:
BEGIN DBMS_SCHEDULER.DROP_JOB ( job_name => 'job_name', -- job的名称 force => FALSE -- 是否强制删除job ); END; /
其中,job_name是要删除的Job的名称,force参数表示是否强制删除Job。如果force参数为TRUE,则会强制删除Job及其关联的所有对象(例如,程序、链、计划等)。如果force参数为FALSE,则只会删除Job本身。
五、停止Job
begin dbms_job.broken(23,true); commit; end;
上述命令即可停止job的执行。
其中dbms_job.broken 是 Oracle 数据库中的一个过程,用于标记一个作业(job)为失效状态。它的语法如下:
DBMS_JOB.BROKEN (
job IN BINARY_INTEGER,
broken IN BOOLEAN,
next_date IN DATE DEFAULT NULL,
interval IN VARCHAR2 DEFAULT NULL);
其中,job 参数表示要标记为失效的作业的 ID 号;broken 参数表示是否将作业标记为失效状态,true 表示失效,false 表示恢复;next_date 参数表示作业下一次执行的时间;interval 参数表示作业执行的间隔时间。
调用该过程后,指定 ID 号的作业将被标记为失效状态。
如果 broken 参数为 true,则该作业将被标记为失效,不再执行;如果为 false,则该作业将被恢复为正常状态。如果指定了 next_date 和 interval 参数,则会更新作业的下一次执行时间和执行间隔时间。
或者使用下面命令也可以实现停止job:
BEGIN DBMS_SCHEDULER.STOP_JOB ( job_name => 'job_name', force_option => 'IMMEDIATE', commit_semantics=> 'ABORT'); END;
其中,job_name是要停止的Job的名称,force_option参数表示停止Job的方式,可以为IMMEDIATE或CASCADE。如果force_option为IMMEDIATE,则会立即停止Job的执行。如果force_option为CASCADE,则会将Job及其关联的所有对象都停止。commit_semantics参数表示停止Job的提交语义,可以为COMMIT或ABORT。如果commit_semantics为COMMIT,则会提交Job的事务,并将Job状态设置为STOPPED。如果commit_semantics为ABORT,则会回滚Job的事务,并将Job状态设置为BROKEN。
六、立即执行job
call dbms_job.run(23);
dbms_job.run 是 Oracle 数据库中的一个过程,用于立即执行一个作业(job)。它的语法如下:
DBMS_JOB.RUN (
job IN BINARY_INTEGER);
其中,job 参数表示要执行的作业的 ID 号。调用该过程后,指定 ID 号的作业将被立即执行一次。如果该作业正在执行中,则该过程不会产生任何效果,直到该作业执行完毕后再执行一次。
注意:
该过程也是只能执行由 DBMS_JOB.SUBMIT 提交的作业,不能执行由 DBMS_SCHEDULER.SUBMIT 创建的作业。
七、修改job
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'job_name', -- job的名称 attribute => 'start_date', -- 要修改的属性名称 value => SYSTIMESTAMP + INTERVAL '1' DAY -- 修改后的属性值 ); END; /
该语法是使用DBMS_SCHEDULER包修改job的开始时间:
1. `BEGIN`和`END`是PL/SQL代码块的开始和结束标志。
2. `DBMS_SCHEDULER.SET_ATTRIBUTE`是修改job属性的过程,包括以下参数:
- `name`:job的名称。
- `attribute`:要修改的属性名称,可以是start_date、repeat_interval、end_date等。
- `value`:修改后的属性值,可以是一个日期类型的变量或者表达式。
3. `job_name`是要修改的job的名称。
4. `SYSTIMESTAMP + INTERVAL '1' DAY`表示将job的开始时间修改为当前时间加上1天后的时间。
5. `/`是PL/SQL代码块的结束标志。
注意:
使用DBMS_SCHEDULER包修改job的属性时,需要保证job已经存在。如果job不存在,则需要先使用CREATE_JOB过程创建job,然后再使用SET_ATTRIBUTE过程修改job的属性。
如果一个作业(job)执行失败,Oracle 数据库会根据作业的重试次数和重试间隔时间来进行重试。默认情况下,Oracle 数据库会在作业执行失败后立即进行重试,最多重试 16 次,每次重试的间隔时间为 5 分钟。也就是说,如果一个作业执行失败,Oracle 数据库会在 5 分钟后再次尝试执行该作业,如果该次执行仍然失败,则会继续重试,直到达到最大重试次数为止。
注意:
作业的重试次数和重试间隔时间可以通过 DBMS_JOB.CHANGE 或 DBMS_SCHEDULER.SET_ATTRIBUTE 进行修改。用户可以根据实际情况来设置作业的重试次数和重试间隔时间,以便更好地管理作业的执行。
通常情况下:
1、每次重试时间都是递增的,第一次1分钟,2分钟,4分钟,8分钟 ... 依此类推。
2、当超过1440分钟,也就是24小时的时候,固定的重试时间为1天。
3、超过16次重试后,job就会被标记为broken,next_date为4000-1-1,也就是不再进行job重试。16次重试的时间大概是7天半。
其中前两条这样设计的目的是为了避免在短时间内频繁地重试,降低系统的负载,同时也能够保证任务能够在合理的时间内得到处理。
而第三条是因为在 Oracle 中,如果一个作业(job)执行失败达到最大重试次数后,该作业会被标记为 "broken" 状态,同时下一次执行时间会被设置为 4000-01-01,即不再对该作业进行重试。这是 Oracle 数据库的默认行为,旨在防止无限制地重试失败的作业,避免对系统造成过大的负担和风险。
当作业被标记为 "broken" 状态后,用户可以通过调用 DBMS_JOB.BROKEN 过程来修改作业的状态,例如将作业恢复为正常状态、更新作业的下一次执行时间和执行间隔时间等。同时,用户也可以通过修改作业的重试次数和重试间隔时间来避免作业被标记为 "broken" 状态,以便更好地管理作业的执行。
注意:
对于那些不需要重试的作业,用户可以将其重试次数设置为 0,以避免对系统造成不必要的负担和风险。
九、job用法
1. 创建一个PL/SQL块,用于清理过期数据:
CREATE OR REPLACE PROCEDURE clean_customer_data AS BEGIN DELETE FROM customer WHERE expiration_date < SYSDATE; COMMIT; END;
2. 创建一个Job,用于定期执行clean_customer_data存储过程:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'clean_customer_data_job', job_type => 'STORED_PROCEDURE', job_action => 'clean_customer_data', start_date => SYSDATE, repeat_interval => 'FREQ=DAILY; INTERVAL=1', enabled => TRUE, comments => '清理过期数据'); END;
其中,job_name是Job的名称,job_type表示Job的类型,可以为STORED_PROCEDURE、PLSQL_BLOCK、EXECUTABLE等。job_action是要执行的任务,可以是存储过程、PL/SQL块、外部程序等。start_date是Job的开始时间,repeat_interval表示Job的重复执行间隔,可以使用各种时间间隔语法。enabled表示Job是否启用,comments是Job的注释。
3. 检查Job是否正常运行:
SELECT job_name, state, last_start_date, next_run_date FROM dba_scheduler_jobs WHERE job_name = 'clean_customer_data_job';
4. 如果需要停止或删除Job,可以使用以下语句:
停止Job:
BEGIN DBMS_SCHEDULER.STOP_JOB ( job_name => 'clean_customer_data_job', force_option => 'IMMEDIATE', commit_semantics=> 'ABORT'); END;
删除Job:
BEGIN DBMS_SCHEDULER.DROP_JOB ( job_name => 'clean_customer_data_job', force => TRUE); END;