afunms

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;