欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

oracle设置定时执行存储过程 有大用 有大大用

星期二, 2021-09-14 21:11 — adminshiping1

oracle中可以使用dbms_job包来实现定时执行存储过程

其中主要使用的是：

1、新增定时任务：dbms_job.isubmit(jobid,what,next_date,interval,no_parse);

jobid:自己随便写一个数字，但需要注意必须唯一

what:要执行的存储过程，不用写begin end 但在结尾要加分号

next_date:下一次执行的时间，这个参数是时间格式的，而不是字符串

interval:执行频率，也就是计算下一次执行时间的公式，是字符串格式的

no_parse:是否在执行时进行语法分析，TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。一般false就好了。

范例：


  1. begin
            

  3. dbms_job.isubmit(21,'STP_REPORTIMP_SPILT;',to_date('20160223 11:40:00','yyyyMMdd hh24:mi:ss'),'trunc(sysdate+1)+(19*60)/(24*60)',false);
  4. end;






2、修改定时任务：
dbms_job.change(jobid,what,next_date,interval);


jobid:user_jobs表中的jobid（主键），数字格式

what:要执行的存储过程，不用写begin end 但在结尾要加分号

next_date:下一次执行的时间，这个参数是时间格式的，而不是字符串

interval:执行频率，也就是计算下一次执行时间的公式，是字符串格式的

范例：


  1. begin
            
  2. dbms_job.change(21,'STP_REPORTIMP_SPILT;',to_date('20160223 16:00:00','yyyyMMdd hh24:mi:ss'),'trunc(sysdate+1)+(19*60)/(24*60)');
  3. end;





3、设置定时任务的开启与关闭

dbms_job.broken(jobid,off);

jobid:user_jobs表中的jobid（主键），数字格式
off:定时任务开关，true or false，true关闭，false开启

范例：


  1. begin
            
  2. DBMS_JOB.BROKEN(21,FALSE);
  3. end;





4、直接运行定时任务

  dbms_job.run(21);
jobid:user_jobs表中的jobid（主键），数字格式
范例：

  1. begin
            
  2. dbms_job.run(21);
  3. end;




5、删除定时任务

dbms_job.remove(jobid);

jobid:user_jobs表中的jobid（主键），数字格式

范例：



  1. begin
            
  2. dbms_job.remove(21);
  3. end;

5、查看定时任务



select * from dba_jobs





其他知识点：

1、job如果由于某种原因未能成功执行，oracle将重试16次后，还未能成功执行，将被标记为broken，重新启动状态为broken的job参见上述 3、4两点;

2、dba_jobs，all_jobs，user_jobs，dba_jobs_running（包含正在运行job相关信息） 这些视图保存了job的相关信息
3、查看相关信息SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;
4、正在运行的JOB相关信息

SELECT SID, R.JOB, LOG_USER, R.THIS_DATE, R.THIS_SEC
  FROM DBA_JOBS_RUNNING R, DBA_JOBS J
 WHERE R.JOB = J.JOB;
5、JOB QUEUE LOCK相关信息
SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ';


关于 interval 的一些设置技巧
关于job运行时间
1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/(24*60)
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

job的运行频率设置
1.每天固定时间运行，比如早上8:10分钟：Trunc(Sysdate+1) + (8*60+10)/24*60
2.Toad中提供的：
每天：trunc(sysdate+1)
每周：trunc(sysdate+7)
每月：trunc(sysdate+30)
每个星期日：next_day(trunc(sysdate),'星期日')
每天6点：trunc(sysdate+1)+6/24
半个小时：sysdate+30/(24*60)
3.每个小时的第15分钟运行，比如：8:15，9:15，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60)。

更多相关资料：http://my.oschina.net/u/2309120/blog/371437

来自  https://blog.csdn.net/lhjllff12345/article/details/50723359



Oracle 编写存储过程并设置定时执行
Oracle 版本：11g
PL/SQL 版本：11.0.3.1770
1.如果编写单个存储过程，则直接在procedure新建并编写；如果需要写多个同类功能的存储过程，为了方便管理，可以现在package中定义，然后在package bodies 中编写过程体，如下图所示：
 
1.1 编写单个procedure
1.1.1右键procedure，新建——填写过程名和相关参数：
 1.1.2编写过程，可以修改方法名和参数（该过程功能是打印输入的参数）：
1.1.3点击F8提交存储过程，没有报错的话会在procedure包下出现新增的p_test过程：
 
 
 1.1.4右键p_test——点击test——输入参数——点击F9或者左上角（start debugger）
 
 
 1.1.5点击run（或Ctrl + R）执行
 
 
 1.1.6切换到DBMS ouput 可以看到过程的执行结果：
 
 
1.2 设置定时任务执行存储过程

1.2.1找到jobs包
 
 
 1.2.2右键新建一个定时任务，Name-给该定时任务命名；Type-选择Store procedure（即执行的是一个存储过程）；Action-填写编写的存储过程名称；Start date-开始时间；Frequency-选择执行频率；Interval-间隔时间（为了方便看到效果，这里设置了10秒执行一次）；Job class-选择default job class即可；下方填入存储过程的参数，点击Apply即可。
 
 
 
 
 
 1.2.3此时存储过程就会每10秒自动执行了，点击下图选项可以看到执行详情和日志，点击reflash刷新一下即可：
 
 
　　 以上就是一个完成的编写存储过程到定时执行的过程，如果编写的存储过程函数体是查询某个表的数据然后插入另外的表的，就可以实现定时从远程数据库把数据插入到本地数据的功能，这也是存储过程定时任务常用的场景。
 
 
 
 
1.3 多个同类型的存储过程可以在package中定义，然后再package bodies 中实现：
1.3.1新建package，定义名称和填写包的用途：
 
1.3.2定义存储过程，此处定义了一个无参的p_apple和一个有参的p_orange；
 
 
1.3.3按F8提交，package下多了一个PA_EATFOOD，同时package bodies下多了一个同名的PA_EATFOOD，在此编写函数的详细方法；
 
 
 暂时不需要写的函数，可以用null设置为无功能的空方法，确认无误后点击F8执行即可。
1.3.4测试，点击左方的函数，右键test，进入执行单个函数进行测试操作：
 
 
 1.3.5设置定时任务跟1.2过程一样，唯一的区别是Action 选项要在过程名前面加上包名，本例中即PA_EATFOOD.p_apple和PA_EATFOOD.p_orange。
 


来自  https://www.cnblogs.com/gdou/p/11712547.html 



**
推荐用dbms_scheduler方式更好 （2012-11-19注）
  1. /* 
  2. 查询:
  3. select job,broken,what,interval,t.* from user_jobs t; 
  4. job job的唯一标识，自动生成的   
  5. broken 是否处于运行状态，N;运行；Y:停止   
  6. what 存储过程名称
  7. next_date 初次执行时间    
  8. interval 执行周期  
  10. 删除:
  11. begin dbms_job.remove(jobno); end;
  12. 根据what的内容确定其对应的job，并如此执行删除
  14. 执行时间例子:
  15. 描述                    INTERVAL参数值   
  16. 每天午夜12点            ''TRUNC(SYSDATE + 1)''   
  17. 每天早上8点30分         ''TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)''   
  18. 每星期二中午12点         ''NEXT_DAY(TRUNC(SYSDATE ), ''''TUESDAY'''' ) + 12/24''   
  19. 每个月第一天的午夜12点    ''TRUNC(LAST_DAY(SYSDATE ) + 1)''   
  20. 每个季度最后一天的晚上11点 ''TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ''Q'' ) -1/24''   
  21. 每星期六和日早上6点10分    ''TRUNC(LEAST(NEXT_DAY(SYSDATE, ''''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）''  
  22. 每3秒钟执行一次             'sysdate+3/(24*60*60)' 
  23. 每2分钟执行一次           'sysdate+2/(24*60)' 
  25. 1:每分钟执行
  26. Interval => TRUNC(sysdate,'mi') + 1/ (24*60) --每分钟执行
  27. interval => 'sysdate+1/（24*60）'  --每分钟执行
  28. interval => 'sysdate+1'    --每天
  29. interval => 'sysdate+1/24'   --每小时
  30. interval => 'sysdate+2/24*60' --每2分钟
  31. interval => 'sysdate+30/24*60*60'  --每30秒
  32. 2:每天定时执行
  33. Interval => TRUNC(sysdate+1)  --每天凌晨0点执行
  34. Interval => TRUNC(sysdate+1)+1/24  --每天凌晨1点执行
  35. Interval => TRUNC(SYSDATE+1)+(8*60+30)/(24*60)  --每天早上8点30分执行
  36. 3:每周定时执行
  37. Interval => TRUNC(next_day(sysdate,'星期一'))+1/24  --每周一凌晨1点执行
  38. Interval => TRUNC(next_day(sysdate,1))+2/24  --每周一凌晨2点执行
  39. 4:每月定时执行
  40. Interval =>TTRUNC(LAST_DAY(SYSDATE)+1)  --每月1日凌晨0点执行
  41. Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24  --每月1日凌晨1点执行
  42. 5:每季度定时执行
  43. Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q')  --每季度的第一天凌晨0点执行
  44. Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q') + 1/24  --每季度的第一天凌晨1点执行
  45. Interval => TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24  --每季度的最后一天的晚上11点执行
  46. 6:每半年定时执行
  47. Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24  --每年7月1日和1月1日凌晨1点
  48. 7:每年定时执行
  49. Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24  --每年1月1日凌晨1点执行
  51. 相关方法:
  52. 修改要执行的操作：dbms_job.what（jobno，'sp_fact_charge_code;'）；  --修改某个job名
  53. 修改下次执行时间：dbms_job.next_date（job，next_date）；
  54. 修改间隔时间：dbms_job.interval（job，interval）； 
  55. 停止job：dbms.broken（job，broken，nextdate）； 
  56. dbms_job.broken(v_job,true,next_date);        --停止一个job,里面参数true也可是false，next_date（某一时刻停止）也可是sysdate（立刻停止）。  
  57. */
  58. create or replace procedure proc_auto_exec_job as
  59. begin
  60. declare
  61. job number;
  62. BEGIN
  63. DBMS_JOB.SUBMIT(
  64. JOB => job,  /*自动生成JOB_ID*/
  65. WHAT => 'proc_test_job;',  /*需要执行的过程或SQL语句*/
  66. /*NEXT_DATE => sysdate, */ /*初次执行时间，立刻执行*/
  67. /*INTERVAL => 'sysdate+3/(24*60*60)' */ /*执行周期 -每3秒钟*/
  68. NEXT_DATE => TRUNC(SYSDATE+1)+(0*60+30)/(24*60),  /*初次执行时间，12点30分*/
  69. INTERVAL => 'TRUNC(SYSDATE+1)+(0*60+30)/(24*60)'  /*每天12点30分*/
  70. );
  71. COMMIT;
  72. /*dbms_job.submit(job, 'proc_test_job;', sysdate, 'trunc(sysdate,''mi'')+1/(24*60)'); /*执行周期 -每1分钟*/
  73. commit;*/
  74. DBMS_JOB.RUN(job);
  75. end;
  76. end proc_auto_exec_job;
  77. begin proc_auto_exec_job; end;


      来自  https://blog.csdn.net/thinkscape/article/details/7411012
         
普通分类: 
oracle
  


  

          

    
      




  



  

          

                      

    


    
  

    Powered by Drupal  






    
友情链接

  
  

    
校园好文网

  






    
  

    
 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2