Oracle中定时任务的使用方法

在工作当中，经常要用到定时任务来处理一些数据库中的数据，但是每次都通过程序写定是任务，确实比较累，后来发现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等。

 

如果当中有什么不足之处，请指出，谢谢！