简单的存储过程

 

create or replace procedure update_scp_stat_mininfo
is
begin

 insert into i_scp_stat_mininfo (caps,scpid,timezone,datetime)
  select max(caps),scpid,timezone, datetime  from (
  select sum(caps)caps,datetime times,scpid scpid,trunc(substr(datetime,15,2)/5) as timezone,
  (substr(datetime,0,13)) as datetime from i_scp_stat
  where to_char(replace(substr(datetime,0,10),'-','')) >= (select to_char(sysdate-1,'yyyymmdd') from dual)
  and to_char(replace(substr(datetime,0,10),'-','')) < (select to_char(sysdate,'yyyymmdd') from dual)
  group by datetime,scpid
  )
  group by scpid,timezone,datetime;

commit;
exception
    when others then
    dbms_output.put_line(substr(sqlerrm,1,200));
    return;
end update_scp_stat_mininfo;