jsgkj/gkjsjy/trunk/WebContent/static/sql/oracle/other/7.job.sql

--许可证延续状态更新job
create or replace procedure updateXkz as
begin
  
update GK_SHARE_JSJY_JYXKZ set sfyx = 2 where to_char(yxqz,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') and record_status <> 9 and sfyx =1;

update GK_SHARE_JSJY_JYXKZ set sfyx = 1 where to_char(yxqz,'yyyy-mm-dd') = to_char(sysdate+interval '3' year,'yyyy-mm-dd')  and record_status <> 9 and sfyx = 4 ;

update GK_SHARE_JSJY_JYXKZ set sfyx=2 where  record_status <> 9 and sfyx = 1 and to_char(sysdate,'yyyy-mm-dd') >to_char(yxqz,'yyyy-mm-dd');
 
commit;

end updateXkz;

--定时任务，运行时根据时间实际时间更新to_date时间
variable job number;
begin
  sys.dbms_job.submit(job => :job,
                      what => 'updateXkz;',
                      next_date => to_date('20-07-2016 23:59:59', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+1/24/60');
  commit;
end;


update gk_operation_jsjy_xkzsqglb set record_status = 1;