oracle 数据库存储过程及任务调用

在oracle数据库中，可以通过创建dbms作业的方式进行定时存储过程的调用，但是如何创建呢？虽然网上有一些资料，但是语法还是比较'奇葩'，试了很多遍才弄好，具体情况看图片就可以了。

 

输入要在作业中运行的plsql :procedure_statistic_result; /*****(存储过程名称,多个可以用分号隔开，本例只有一个)*****/

开始日期:trunc(sysdate) + 22/24;/*本日的22点运行第一次*/
下一日期:trunc(sysdate) + 22/24+1;//下一个日期的22点运行

 

oracel 存储过程:

create or replace
PROCEDURE PROCEDURE_STATISTIC_RESULT AS 
  result varchar2(100);--执行结果
  v_count integer := 0;
  v_rownum integer := 0;
  cursor sdyz_cursor is select w.shengdaoyuzhong,count(w.movie_id) as moviecount from v_programlayer_sdyz w , movie b where w.movie_id = b.movie_id  group by w.shengdaoyuzhong;
  cursor zmyz_cursor is select w.zimuyuzhong,count(w.movie_id) as moviecount from v_programlayer_zmyz w , movie b where w.movie_id = b.movie_id  group by w.zimuyuzhong;
BEGIN
  /**--------------------------声道语种统计----------------------------**/
  select count (1) into v_count from T_RESULT_STATISTIC t where t.resulttype = 'shengdaoyuzhong';
  IF v_count > 0 THEN
     delete from T_RESULT_STATISTIC t where t.resulttype = 'shengdaoyuzhong';     
  END IF ;
  FOR sdyz IN sdyz_cursor LOOP
    insert into T_RESULT_STATISTIC (id,createdate,moviecount,yuzhong,resulttype) values (sys_guid(),sysdate,sdyz.moviecount,sdyz.shengdaoyuzhong,'shengdaoyuzhong');
    v_rownum := v_rownum+1;
    if mod(v_rownum,500) = 0 then
      commit;
    end if;
  END LOOP ;
  /**-----------------------字幕语种统计-----------------------**/
 select count(1) into v_count from T_RESULT_STATISTIC t where t.resulttype = 'zimuyuzhong';
  IF v_count > 0 THEN
     delete from T_RESULT_STATISTIC t where t.resulttype = 'zimuyuzhong';     
  END IF ;
  v_rownum := 0;
  FOR zmyz IN zmyz_cursor
  LOOP
    insert into T_RESULT_STATISTIC (id,createdate,moviecount,yuzhong,resulttype) values (sys_guid(),sysdate,zmyz.moviecount,zmyz.zimuyuzhong,'zimuyuzhong');
     v_rownum := v_rownum+1;
    if mod(v_rownum,500) = 0 then
      commit;
    end if;
  END LOOP ;
  if v_rownum >0 then
    commit;
  end if;
  result := 'success';
  dbms_output.put_line(result);
  exception
   when others then 
   result := 'error';
   dbms_output.put_line(result);
END PROCEDURE_STATISTIC_RESULT;