oracle设置定时任务来调用存储过程完成统计（每小时计时一次存到hour表）

        在做项目的时候经常会有日志统计功能，如果日志量很大的话，使用原表来做统计会很慢，这时我们可以设置一个定时任务，定时的调用存储过程，把前一小时的日志做一下统计，存到hour表里，然后就可以直接用hour表来做统计了，统计用时会大大缩短。下面介绍一下大概的实现过程。

1、创建表和序列

----原日志表------------------
create table log_statistic(
       id number  not null  primary key,
       num1 number ,
       num2 number,
       num3 number,
       op_time date
)

----统计后的hour日志表-------------------------
create table log_statistic_hour(
       id number  not null  primary key,
       sum1 number ,
       sum2 number,
       sum3 number,
       op_time date
)

----存储过程执行情况日志表------------------------------------------
create table procedure_log (
       id number not null primary key,
       start_time date,--执行开始时间
       end_time date,--执行完成时间 
       opt_cont varchar2(500),--执行存储过程的大致内容
       opt_time  date,--操作时间
       status number,--状态 0:：成功 1：失败
       error_text varchar2(500)  --异常信息
)


create sequence SEQ_log_statistic
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_log_statistic_hour
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;


create sequence SEQ_PROCEDURE_LOG
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;

2、存储过程

----统计上一小时的数据，把统计结果存入hour表----------------------------------------------------------
create or replace procedure pro_statistic_hour(p_date date) is
   type num1_type is table of log_statistic.num1%type;
   type num2_type is table of log_statistic.num2%type;
   type num3_type is table of log_statistic.num3%type;
   v_sum1 num1_type;
   v_sum2 num2_type;
   v_sum3 num3_type;
   v_date date :=to_date(to_char( p_date-1/24,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24:mi:ss');
   cursor v_cur is select sum(num1) as sum1 ,sum(num2) as sum2,sum(num3) as sum3 from log_statistic
    where op_time>=to_date(to_char(p_date-1/24,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24:mi:ss')
    and op_time<to_date(to_char(p_date,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24:mi:ss')
    group by to_char(p_date-1/24,'yyyy-mm-dd hh24');
    v_count number;
    v_start_time date;
    v_end_time date;
    error_text varchar2(500);
begin
 v_count :=0;
 open v_cur;
 v_start_time := sysdate;
 loop
   fetch v_cur bulk collect into v_sum1,v_sum2,v_sum3 limit 150;--fetch bulk collect会批量的把数据读取到集合当中，取数据的时候需要使用下标
         for i in 1..v_sum1.count loop--下标从1开始
             insert into log_statistic_hour (id,sum1,sum2,sum3,op_time) 
             values (seq_log_statistic_hour.nextval,v_sum1(i),v_sum2(i),v_sum3(i),v_date);--读取的时候千万不能丢了下标
             v_count:=v_count+1;
             if v_count mod 50=0 then
                commit;
             end if;
         end loop;
         commit;
    exit when v_cur%notfound;
 end loop;
 close v_cur;
 v_end_time := sysdate;
 insert into procedure_log (id,start_time,end_time,opt_cont,opt_time,status,error_text)
  values(seq_procedure_log.nextval,v_start_time,v_end_time,v_date||'统计存储过程完成',sysdate,1,'');
 commit;
 exception
  when others then
      v_end_time := sysdate;
     error_text := sqlcode||'::'||SUBSTR(sqlerrm,1,450);
     insert into procedure_log (id,start_time,end_time,opt_cont,opt_time,status,error_text)
           values(seq_procedure_log.nextval,v_start_time,v_end_time,v_date||'统计存储过程出现异常'
           ,sysdate,1,error_text);
  commit;
end pro_statistic_hour;
    

注：substr不能直接在insert语句里使用

3、定时任务:每小时执行一次存储过程pro_statistic_hour

PL/SQL：

declare jobno number;
begin
    dbms_job.submit(jobno,'pro_statistic_hour(sysdate);',sysdate,'sysdate+1/24');
    commit;
end;

注：上面的代码是pl/sql中的写法，pl/sql中的写法跟sql plus中的写法是不一样的，需要注意的有两个地方，一个是定义变量的时候， pl/sql中用的是declare，而sql plus中用的是variable，别一个是在设置定时任务时，指定job参数时，pl/sql中jobno前面没有“:”，而sql plus中jobno前面是有“:”的。

sql plus：

variable jobno number;
begin
	dbms_job.submit(:jobno,'pro_statistic_hour(sysdate);',sysdate,'sysdate+1/24');
	commit;
end;
/

注：设置定时任务what参数时，存储过程后台的“;”是不能丢的，否则会出异常

4、造数据：每次生成2000条数据，我执行了5次

begin
       for i in 1..2000 loop
           insert into log_statistic (id,num1,num2,num3,op_time) values(seq_log_statistic.nextval,80,90,100,to_date(to_char(sysdate,'yyyy-mm-dd hh24:ss:mi'),'yyyy-mm-dd hh24:mi:ss'));
       end loop;
       commit;
end;

5、执行情况

(1)、log_statistic_hour表

(2)、procedure_log表

注：因为造数据的时候只造了15点的数据，所以，虽然存储过程执行了3次，但是log_statistic_hour表里就只有15点的一条数据。