Oracle存储过程

     new_time(sysdate,'GMT','YDT') 将8:00时区的日期转为0:00时区显示

 

create or replace procedure 存储过程名(recordCount out number) as
  v_data_id number(19);
  v_borrow_nid varchar2(200);
  v_user_id number(19);
  v_account number(19,6);
  v_addip varchar2(50);
  v_addtime number(19);
  v_bank varchar2(50);
  stime number(19);
  etime number(19);
  old_time number(19);
begin
  select (new_time(to_date(to_char(sysdate-1,'yyyy-mm-dd')||' 00:00:00','yyyy-mm-dd hh24:mi:ss'),'GMT','YDT')-to_date('1970-01-01','yyyy-mm-dd'))*24*60*60*1000 into stime from dual;
  select (new_time(to_date(to_char(sysdate,'yyyy-mm-dd')||' 00:00:00','yyyy-mm-dd hh24:mi:ss'),'GMT','YDT')-to_date('1970-01-01','yyyy-mm-dd'))*24*60*60*1000 into etime from dual;
  select (new_time(to_date(to_char(sysdate-7,'yyyy-mm-dd')||' 00:00:00','yyyy-mm-dd hh24:mi:ss'),'GMT','YDT')-to_date('1970-01-01','yyyy-mm-dd'))*24*60*60*1000 into old_time from dual;
  
  delete from business_repeat_datas where addtime<old_time;
  
  recordCount := 0;
  
  for r1 in (
    select t1.id,t1.borrow_nid,t1.user_id,t1.account,t1.addip,t1.addtime from 表1 t1
  ) loop
    recordCount := recordCount + 1;
    
    v_data_id := r1.id;
    v_borrow_nid := r1.borrow_nid;
    v_user_id := r1.user_id;
    v_account := r1.account;
    v_addip := r1.addip;
    v_addtime := r1.addtime;
    
    insert into business_repeat_datas(id,data_type,data_id,borrow_nid,user_id,account,addip,addtime) 
    values (seq_repeat_datas_alarm.nextval, 'tender', v_data_id, v_borrow_nid, v_user_id, v_account, v_addip, v_addtime);
  end loop;
  
  commit;
end;