public class GpsAnalyseSchedule {
private Timer timer = null;
public GpsAnalyseSchedule() {
long delay = FormatDate.getDateDiff("19:04:10");
timer = new Timer(true);
timer.schedule(new GpsAnalyse(), delay, 1000 * 60 * 60 * 24);
}
class GpsAnalyse extends TimerTask {
public void run() {
ServerConfig.logger.info("开始启动GPS运营统计定时器!");
Database db = null;
try {
db = new Database();
CallableStatement proc = null;
proc = db.getConn().prepareCall("{ call njtjgps.proc_gps_analyse(?) }");
proc.setDate(1, new java.sql.Date(Calendar.getInstance().getTime().getTime()));
proc.execute();
} catch (Exception e) {
e.printStackTrace();
ServerConfig.logger.info(e.getStackTrace());
} finally {
db.cleanup();
}
ServerConfig.logger.info("GPS运营统计定时器启动完毕!");
}
}
}
只需要new GpsAnalyseSchedule ()就完成了定时器调用存储过程。
我的存储过程是用来统计某一日的情况的,如下:
CREATE OR REPLACE PROCEDURE proc_gps_analyse(v_today date)
as
tmp_count number;
tmp_natural_count number;
tmp_excepl_count number;
tmp_exigent number;
tmp_over_speed number;
tmp_ent number;
cursor my_cursor is
select count(lic_nob),count(lic_noa),count(lic_nob)-count(lic_noa),nvl(sum(sign(exigent)),0),nvl(sum(sign(over_speed)),0),
ent_id
from
(select A.lic_no lic_noa,A.exigent exigent,A.over_speed over_speed ,B.lic_no lic_nob,B.ent_id ent_id
from
(select LIC_NO ,
sum(exigent) exigent,
sum(over_speed) over_speed
from T_VEHICLE_HISTORY
where pos_state='1' and to_char(SERVER_TIME,'yyyy-mm-dd')=to_char(v_today,'yyyy-mm-dd')
group by lic_no) A RIGHT JOIN (
SELECT lic_no,ent_id FROM t_vehicle_info WHERE ent_id<>0 AND to_char(create_date,'yyyy-mm-dd')<to_char(v_today,'yyyy-mm-dd')) B
ON A.lic_no=B.lic_no)
group by ent_id;
begin
open my_cursor;
loop
fetch my_cursor into tmp_count,tmp_natural_count ,tmp_excepl_count,tmp_exigent,tmp_over_speed,tmp_ent;
exit when my_cursor%notfound;
insert into T_GPS_ANALYSE(ANLS_ID,VEHICLE_COUNT,ENT_ID,natural_count,excepl_count,exigent_count,over_speed_count,total_date)
values(seq_anls.nextval,tmp_count,tmp_ent,tmp_natural_count,tmp_excepl_count,tmp_exigent,tmp_over_speed,v_today);
end loop;
close my_cursor;
commit;
exception
when others then
rollback;
end proc_gps_analyse;
/
private Timer timer = null;
public GpsAnalyseSchedule() {
long delay = FormatDate.getDateDiff("19:04:10");
timer = new Timer(true);
timer.schedule(new GpsAnalyse(), delay, 1000 * 60 * 60 * 24);
}
class GpsAnalyse extends TimerTask {
public void run() {
ServerConfig.logger.info("开始启动GPS运营统计定时器!");
Database db = null;
try {
db = new Database();
CallableStatement proc = null;
proc = db.getConn().prepareCall("{ call njtjgps.proc_gps_analyse(?) }");
proc.setDate(1, new java.sql.Date(Calendar.getInstance().getTime().getTime()));
proc.execute();
} catch (Exception e) {
e.printStackTrace();
ServerConfig.logger.info(e.getStackTrace());
} finally {
db.cleanup();
}
ServerConfig.logger.info("GPS运营统计定时器启动完毕!");
}
}
}
只需要new GpsAnalyseSchedule ()就完成了定时器调用存储过程。
我的存储过程是用来统计某一日的情况的,如下:
CREATE OR REPLACE PROCEDURE proc_gps_analyse(v_today date)
as
tmp_count number;
tmp_natural_count number;
tmp_excepl_count number;
tmp_exigent number;
tmp_over_speed number;
tmp_ent number;
cursor my_cursor is
select count(lic_nob),count(lic_noa),count(lic_nob)-count(lic_noa),nvl(sum(sign(exigent)),0),nvl(sum(sign(over_speed)),0),
ent_id
from
(select A.lic_no lic_noa,A.exigent exigent,A.over_speed over_speed ,B.lic_no lic_nob,B.ent_id ent_id
from
(select LIC_NO ,
sum(exigent) exigent,
sum(over_speed) over_speed
from T_VEHICLE_HISTORY
where pos_state='1' and to_char(SERVER_TIME,'yyyy-mm-dd')=to_char(v_today,'yyyy-mm-dd')
group by lic_no) A RIGHT JOIN (
SELECT lic_no,ent_id FROM t_vehicle_info WHERE ent_id<>0 AND to_char(create_date,'yyyy-mm-dd')<to_char(v_today,'yyyy-mm-dd')) B
ON A.lic_no=B.lic_no)
group by ent_id;
begin
open my_cursor;
loop
fetch my_cursor into tmp_count,tmp_natural_count ,tmp_excepl_count,tmp_exigent,tmp_over_speed,tmp_ent;
exit when my_cursor%notfound;
insert into T_GPS_ANALYSE(ANLS_ID,VEHICLE_COUNT,ENT_ID,natural_count,excepl_count,exigent_count,over_speed_count,total_date)
values(seq_anls.nextval,tmp_count,tmp_ent,tmp_natural_count,tmp_excepl_count,tmp_exigent,tmp_over_speed,v_today);
end loop;
close my_cursor;
commit;
exception
when others then
rollback;
end proc_gps_analyse;
/