最近有一个需求,就是有一个记录用户日志信息的表,不停的有数据插入,需要对这个日志进行统计后,归类带对应的信息表中去,简单的写了一个存储过程,然后用job自己运行就可以。记录一下过程。
create or replace procedure P_MMS_IOD_COUNT_STAT is
--创建一个游标
cursor iodcount is
select info_id, sum(iod_num)
from t_mms_log
where substr(time, 0, 8) = to_char(sysdate, 'yyyyMMdd')
group by info_id;
--定义一个类型,包含需要的两个字段
type cnt is record(
infoid integer,
count integer
);
v_info_cnt cnt;
begin
--打开游标
open iodcount ;
loop
--循环游标
fetch iodcount into v_info_cnt;
exit when iodcount%notfound;
update t_mms t
set t.down_cnt=t.down_cnt + nvl(v_info_cnt.count,0)
where t.id=v_info_cnt.infoid;
end loop;
--提交
commit;
--关闭游标
close iodcount;
exception
when others then
rollback;
end P_MMS_IOD_COUNT_STAT;
job每天凌晨1点开始跑。代码如下:
begin
sys.dbms_job.submit(job => :job,
what => 'p_mms_iod_count_stat;',
next_date => to_date('25-12-2008 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1');
commit;
end;