create or replace procedure statLogHistory
(startdate varchar2) is
sqlstr1 varchar2(500);
sqlstr2 varchar2(500);
sqlstr3 varchar2(500);
sqlstr4 varchar2(500);
begin
sqlstr1:='insert into stat_log_2011
select * from stat_log
where accessdate='||startdate||' and accesstime<''||12:00:00||'' ' ; sqlstr2:='delete from stat_log
where accessdate='||startdate||' and accesstime<''||12:00:00||'' '; sqlstr3:='insert into stat_log_2011
select * from stat_log
where accessdate='||startdate||' and accesstime>=''||12:00:00||'' and accesstime<=''||24:00:00||'' '; sqlstr4:='delete from stat_log
where accessdate='||startdate||' and accesstime>=''||12:00:00||'' and accesstime<=''||24:00:00||'' '; execute immediate sqlstr1;
commit;
execute immediate sqlstr2;
commit;
execute immediate sqlstr3;
commit;
execute immediate sqlstr4;
commit;
end statLogHistory;执行这个存储过程好慢呀 大概7W条记录 ,用sql执行这4个sql语句大概30秒,存储过程就不行- -。求大神啊!
(startdate varchar2) is
sqlstr1 varchar2(500);
sqlstr2 varchar2(500);
sqlstr3 varchar2(500);
sqlstr4 varchar2(500);
begin
sqlstr1:='insert into stat_log_2011
select * from stat_log
where accessdate='||startdate||' and accesstime<''||12:00:00||'' ' ; sqlstr2:='delete from stat_log
where accessdate='||startdate||' and accesstime<''||12:00:00||'' '; sqlstr3:='insert into stat_log_2011
select * from stat_log
where accessdate='||startdate||' and accesstime>=''||12:00:00||'' and accesstime<=''||24:00:00||'' '; sqlstr4:='delete from stat_log
where accessdate='||startdate||' and accesstime>=''||12:00:00||'' and accesstime<=''||24:00:00||'' '; execute immediate sqlstr1;
commit;
execute immediate sqlstr2;
commit;
execute immediate sqlstr3;
commit;
execute immediate sqlstr4;
commit;
end statLogHistory;执行这个存储过程好慢呀 大概7W条记录 ,用sql执行这4个sql语句大概30秒,存储过程就不行- -。求大神啊!
1 stat_log 中 accessdate 建立索引
2 accessdate 如果是字符 ,组合出来的脚步中 startdate 的用引号引起来 ,
如果是日期 条件最好写成 accessdate =to_date(startdate ,'yyyy-mm-dd')