在工作当中,经常要用到定时任务来处理一些数据库中的数据,但是每次都通过程序写定是任务,确实比较累,后来发现Oracle中可以创建定时任务,这就省去了开发程序的时间,也大大提高了工作效率。
下面我们说一下Oracle中定时任务的创建、启用、停用和删除方法。
1、首先我们定义一个存储过程。
CREATE OR REPLACE PROCEDURE BIND_USER_OWN_WAREHOUSE
AS
-- 获取用户以及用户所属机构信息
cursor uc_list IS
SELECT u.CODE,u.USERNAME,u.COMPANYCODE,c.COMPANTFULLNAME,c.REMARK,c.SAPTYPE,u.CREATIONTIME FROM USERTABLE u
LEFT JOIN COMPANY c ON c.CODE = u.COMPANYCODE
WHERE 1 = 1 AND u.DATASTATUS = 1 AND u.ISLOCKED = 0 AND c.SAPTYPE IS NOT NULL AND c.COMPANYTYPE = 6
ORDER BY u.USERNAME;
-- 获取用户所属机构,再根据用户所属机构获取Sold-TO和Ship-TO的机构对应的仓库信息
cursor uw_list(companyRemark VARCHAR) IS
SELECT CODE,NAME FROM WAREHOUSE
WHERE COMPANYCODE IN (
SELECT CODE FROM COMPANY WHERE SAPTYPE IS NOT NULL AND SUBSTR(REMARK, 0, 18) = SUBSTR(companyRemark, 0, 18)
)
ORDER BY COMPANYCODE;
exist_uw INTEGER;
admin_code VARCHAR2(50);
BEGIN
-- 获取管理员的CODE
SELECT CODE INTO admin_code FROM USERTABLE WHERE USERNAME = 'admin';
-- 如果经销商用户和所属机构下面仓库没有在管辖权限表中,那么加入到该表中去
FOR uc IN uc_list LOOP
FOR uw IN uw_list(uc.REMARK) LOOP
SELECT COUNT(0) INTO exist_uw FROM USER_RULE_WH WHERE USERCODE = uc.CODE AND WAREHOUSECODE = uw.CODE;
IF exist_uw = 0 THEN
INSERT INTO USER_RULE_WH(CODE, DATASTATUS, CREATOR, CREATIONTIME, USERCODE, WAREHOUSECODE, ISAVAILABLE)
VALUES (GET_UUID(), '1', admin_code, sysdate, uc.CODE, uw.CODE, '1');
END IF;
-- DBMS_OUTPUT.PUT_LINE(uw.NAME);
END LOOP;
END LOOP;
END;
2、创建定时任务
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'CHECK_USER_OWN_WAREHOUSE', --JOB名
JOB_TYPE => 'STORED_PROCEDURE',--JOB类型
JOB_ACTION => 'BIND_USER_OWN_WAREHOUSE', --存储过程名
NUMBER_OF_ARGUMENTS =>0, --参数设置,有几个参数就写对应参数的数量
START_DATE => SYSDATE,--开始执行时间
REPEAT_INTERVAL => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0', -- 下次执行时间,按天执行,每天23:00:00执行存储过程BIND_USER_OWN_WAREHOUSE
COMMENTS => '检查用户管辖仓库',--注释
AUTO_DROP=>false --job禁用后是否自动删除
);
END;
说明:
1、JOB_NAME: 任务名称
2、JOB_TYPE:有三种类型,PL/SQL Block、Stored procedure、Executable
3、JOB_ACTION:根据JOB_TYPE的不同,有不同的含义
如果JOB_TYPE指定的是存储过程,就需要指定存储过程的名字;
如果JOB_TYPE指定的是PL/SQL块,就需要输入完整的PL/SQL代码;
如果JOB_TYPE指定的外部程序,就需要输入script的名称或者操作系统的指令名
4、START_DATE:开始时间
5、REPEAT_INTERVAL:运行的时间间隔,上面例子是每天23点运行一次
6、END_DATE:到期时间
7、NUMBER_OF_ARGUMENTS :参数个数
8、AUTO_DROP:默认true,即当job执行完毕都到期是否直接删除job
9、COMMENTS:备注
3、启用定时任务
BEGIN
DBMS_SCHEDULER.ENABLE('CHECK_USER_OWN_WAREHOUSE');
END;
4、禁用定时任务
BEGIN
DBMS_SCHEDULER.DISABLE('CHECK_USER_OWN_WAREHOUSE');
END;
5、删除定时任务
BEGIN
DBMS_SCHEDULER.DROP_JOB('CHECK_USER_OWN_WAREHOUSE');
END;
DBMS_SCHEDULER最常用的个人感觉就是上面的几个方法了,下面就DBMS_SCHEDULER的方法再进行列举一下。
1)DBMS_SCHEDULER.CREATE_JOB(jobName,jobType,jobAction,startDate,repeat_interval,comments)创建job,指定job名称、类型,执行动作,时间间隔,注释等。
其中时间间隔参数FREQ定义如下:
FREQ 关键字用来指定间隔的时间周期,可选参数有:YEARLY(年), MONTHLY(月), WEEKLY(周), DAILY(日), HOURLY(时), MINUTELY(分), SECONDLY(秒)等单位。
INTERVAL 关键字用来指定间隔的频繁,可指定的值的范围从1-999。
BYHOUR 指定一天中的小时。可指定的值的范围从1-24。16,17,18就表示每天下午的4、5、6点。
BYDAY 关键字用来指定每周的哪天运行。
BYMONTHDAY 关键字用来指定每月中的哪一天。-1 表示每月最后一天。
BYMONTH 关键字用来指定每年的月份。
2) DBMS_SCHEDULER.RUN(jobName) 运行job 。
3) DBMS_SCHEDULER.STOP_JOB(jobName,force) 停止job,force默认为false,Oracle建议false停止失败情况下,使用true,且使用true需要有manage scheduler权限 。
4) DBMS_SCHEDULER.DROP_JOB(jobName) 删除job 。
5) DBMS_SCHEDULER.ENABLE(jobName) 打开job 。
6) DBMS_SCHEDULER.DISABLE(jobName,force) 禁用job,force参数用于dependencies,如果true,即使其他对象依赖于它,操作也能成功。
相关视图
1) user_scheduler_jobs 查看job信息 。
2) User_Scheduler_Job_Log job job日志 。
3) user_scheduler_job_run_details job运行日志 。
4) user_scheduler_running_jobs 正在运行的job。
Oracle10g以后也推荐使用DBMS_SCHEDULER创建定时任务,Oracle10g以前使用DBMS_JOB创建定时任务,关于DBMS_JOB大家可以再去网上搜索一下相关的资料。
注意:在oracle11g中创建的定时任务如果不执行,检查job_queue_processes的值,如果是0 则全部不执行。
a、job_queue_processes参数决定了job作业能够使用的总进程数。
b、当该参数为0值,任何job都不会被执行,建议合理设置该值且至少大于1。
(ALTER SYSTEM SET job_queue_processes = 10;)
c、对于job运行时间也应该尽量合理的设置间隔以及启动时间。
d、如果同一时间内运行的Job数很多,过小的参数值导致job不得不进行等待。而过大的参数值则消耗更多的系统资源。
f、对于存在依赖关系的job,尽可能将其进行合并到一个job中,如使用chain等。
如果当中有什么不足之处,请指出,谢谢!