reportdel.sql

网通网关Cngp

declare
  cursor c is select content from statusblack;
  v_content varchar2(20);
begin
  open c;
  loop
    fetch c into v_content ;
    exit when c%notfound;
      update reportfeb set flag=1 where instr(content,v_content)>0;
  end loop;
  close c;
  commit;


end;
/
delete from reportfeb;
commit;
insert into reportfeb(reportdate,mobileid,content) 
  select reportdate,mobileid,content from statusreport
   where to_char(reportdate,'yyyy-mm-dd') between '2007-03-12' and '2007-03-14'
     and substr(mobileid,1,4)='0536';
commit;

update reportfeb set flag=1;
commit;

update reportfeb set flag=0 where instr(lower(content),'您的查询')>0 
 or instr(lower(content),'back message gjj')>0 
 or instr(lower(content),'back message sbcx')>0 
 or instr(lower(content),'back message srhy')>0;

update reportfeb set flag=2 where instr(lower(content),'err:000')=0 and flag=0;
commit;

select count(*) from zx2t_molog 
where substr(mobileid,1,4)='0536' 
and to_char(modate,'yyyy-mm-dd') between '2007-03-12' and '2007-03-14'
and lower(content) in('sb','zf','sf','10');