Oracle中实现后台自动执行的定时操作(定时器)
1．确保Oracle的工作模式答应启动任务队列治理器:
Oracle定时执行“Job Queue”的后台程序是SNP进程，而要启动SNP进程，首先要确保整个系统的模式是可以启动SNP进程的，这需要以DBA的身份去执行如下命令：
svrmgrl> alter system enable restricted session;
或sql> alter system disenable restricted session;
利用如上命令更改系统的会话方式为disenable restricted，为SNP的启动创造条件。
2. Dbms_job.submit的语法为：
?? dbms_job.submit( job out binary_integer,
whatinarchar2,
next_date indate，
intervalinvarchar2,
no_parseinboolean);
?? 其中：
●job：输出变量，是此任务在任务队列中的编号；
●what：执行的任务的名称及其输入参数；
●next_date：任务执行的时间；
●interval：任务执行的时间间隔。
?? 将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott, 则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户：
svrmgrl> grant execute on dbms_job to scott;
3. 实例：
? Java代码
SQL> declare??
?2? n number;??
?3? begin??
?4? dbms_job.submit(n, 'PKG_RPT_REVERSAL.P_FORM4_REVERSAL_ENTRY;',SYSDATE, null);??
?5? commit;??
?6? end;??
?7? /??
?
L/SQL procedure successfully completed?
?SQL> declare
? 2? n number;
? 3? begin
? 4? dbms_job.submit(n, 'PKG_RPT_REVERSAL.P_FORM4_REVERSAL_ENTRY;',SYSDATE, null);
? 5? commit;
? 6? end;
? 7? /
?
PL/SQL procedure successfully completed
PKG_RPT_REVERSAL.P_FORM4_REVERSAL_ENTRY的代码如下：
Java代码
/*?
* insert data into t_prod_tmp table?
*/?
PROCEDURE P_INSERT_PROD_TMP(I_PROCESS_DATE IN DATE)AS??
? M_START_DATE date;??
? M_END_DATE date;??
? BEGIN??
??? select trunc(I_PROCESS_DATE, 'MONTH') into M_START_DATE from dual;??
??? M_END_DATE := ADD_MONTHS(M_START_DATE, 1);??
??????
?? INSERT INTO T_PROD_TMP??
??? (POLICY_ID, CHANGE_ID, SERVICE_ID, FINISH_TIME)??
?? SELECT PC.POLICY_ID,PC.CHANGE_ID,PC.SERVICE_ID,PC.FINISH_TIME??
?? FROM T_POLICY_CHANGE PC, T_ACTUARY_FORM4_TRANSACTION FT??
?? WHERE PC.CHANGE_STATUS = '4' ----撤销状态??
?? AND PC.SERVICE_ID = FT.TRANS_ID??
?? AND FT.TRANS_STATUS = '1' -----有效Service??
?? AND PC.FINISH_TIME >= M_START_DATE??
?? AND PC.FINISH_TIME < M_END_DATE??
?? AND PC.WITHDRAW_TIME <= PC.FINISH_TIME ---Service Undo操作时间应该先于finish_time??
?? ORDER BY PC.POLICY_ID,PC.FINISH_TIME;??
?????
? END P_INSERT_PROD_TMP;??
? /*?
? * Function Entry??
? */?
? PROCEDURE P_FORM4_REVERSAL_ENTRY AS??
? BEGIN??
???? ---- insert data about service reversal Trans into T_PROD_TMP Table-----??
???? P_INSERT_PROD_TMP(SYSDATE);??
???? ---some actions on database are as follows...??
? END P_FORM4_REVERSAL_ENTRY;??
end PKG_RPT_REVERSAL;?
/*
* insert data into t_prod_tmp table
*/
PROCEDURE P_INSERT_PROD_TMP(I_PROCESS_DATE IN DATE)AS
? M_START_DATE date;
? M_END_DATE date;
? BEGIN
??? select trunc(I_PROCESS_DATE, 'MONTH') into M_START_DATE from dual;
??? M_END_DATE := ADD_MONTHS(M_START_DATE, 1);
???
?? INSERT INTO T_PROD_TMP
??? (POLICY_ID, CHANGE_ID, SERVICE_ID, FINISH_TIME)
?? SELECT PC.POLICY_ID,PC.CHANGE_ID,PC.SERVICE_ID,PC.FINISH_TIME
?? FROM T_POLICY_CHANGE PC, T_ACTUARY_FORM4_TRANSACTION FT
?? WHERE PC.CHANGE_STATUS = '4' ----撤销状态
?? AND PC.SERVICE_ID = FT.TRANS_ID
?? AND FT.TRANS_STATUS = '1' -----有效Service
?? AND PC.FINISH_TIME >= M_START_DATE
?? AND PC.FINISH_TIME < M_END_DATE
?? AND PC.WITHDRAW_TIME <= PC.FINISH_TIME ---Service Undo操作时间应该先于finish_time
?? ORDER BY PC.POLICY_ID,PC.FINISH_TIME;
??
? END P_INSERT_PROD_TMP;
? /*
? * Function Entry
? */
? PROCEDURE P_FORM4_REVERSAL_ENTRY AS
? BEGIN
???? ---- insert data about service reversal Trans into T_PROD_TMP Table-----
???? P_INSERT_PROD_TMP(SYSDATE);
???? ---some actions on database are as follows...
? END P_FORM4_REVERSAL_ENTRY;
end PKG_RPT_REVERSAL;
查看一下job执行情况：
SQL> select job,next_date,next_sec,failures,broken from user_jobs;
?????? JOB NEXT_DATE?? NEXT_SEC?????????????????? FAILURES BROKEN
---------- ----------- ------------------------ ---------- ------
????? 1726 1/1/4000??? 00:00:00??????????????????????????? N
????? 2103 1/1/4000??? 00:00:00??????????????????????????? N
????? 2601 1/1/4000??? 00:00:00??????????????????????????? N
????? 4021 12/1/2009?? 00:00:00????????????????????????? 0 N
????? 6227 11/30/2009? 13:22:22????????????????????????? 0 N
????? 6228 11/30/2009? 13:22:17????????????????????????? 0 N
?????? 973 1/1/4000??? 00:00:00???????????????????????? 17 Y
????? 2977 12/7/2009?? 00:00:00????????????????????????? 0 N
????? 2976 12/7/2009?? 00:00:00????????????????????????? 0 N
????? 3579 1/1/4000??? 00:00:00???????????????????????? 16 Y
???? 13063 12/1/2009?? 00:00:00????????????????????????? 0 N
???? 13149 12/1/2009?? 00:00:00????????????????????????? 0 N
???? 19683 11/30/2009? 13:22:22????????????????????????? 0 N
???? 21929 11/30/2009? 13:22:17????????????????????????? 0 N
???? 21930 11/30/2009? 13:22:22????????????????????????? 0 N
如果事务到预定时间并没有执行，排除是过程本身的问题外，可能的原因是初始化参数job_queue_processes值为0或没有设置。用alter system set job_queue_processes=n;（n>0）做好设置。注：job_queue_processes最大值为1000
