oracle存储过程完成统计功能,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

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点的一条数据。