好幾年沒(méi)寫(xiě)過(guò)存諸過(guò)程了，都忘光了。今天由于需求，又寫(xiě)了一個(gè)。
create or replace procedure stat_app_traffic
as
queryhour varchar2(15);
lasthour number;
begin
lasthour:= to_number(to_char(sysdate,'HH24')) - 1;
if(lasthour=-1) then
queryhour:= to_char(sysdate-1,'YYYY-MM-DD ') || '23';
else
queryhour:= to_char(sysdate,'YYYY-MM-DD ') || lasthour;
end if;
insert into app_traffic(app,bytes,log_time)
select app,round(sum(bytes)/1024/1024,2),max(log_time) from
((select b.app,a.src_port port,a.out_bytes bytes,a.log_time
from raw_netflow a,app_config b
where a.src_port=b.port and a.protocol=b.protocol_id
and to_char(log_time,'YYYY-MM-DD HH24')=queryhour)
union
(select b.app,a.dst_port port,a.in_bytes bytes,a.log_time
from raw_netflow a,app_config b
where a.dst_port=b.port and a.protocol=b.protocol_id
and to_char(log_time,'YYYY-MM-DD HH24')=queryhour)) group by app;
commit;
end stat_app_traffic;