利用java 定时器在某个时刻调用oracle存储过程实例,存储过程参数为date

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;
/
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值