oracle 创建无参存储过程

rhjajvszep

create or replace procedure pro_miss_history as
  t_waybillNo        varchar2(20);
  t_checkCode        varchar2(20);
  t_checkTime        date;
  t_missTime         date;
  t_compareTime      date;
  t_compareCheckTime date;

  cursor cur_task is
  --查询核查次数大于1的数据
    select f1.waybillcode, f1.check_zone
      from ompweb.tt_doubt_miss_check f1
     where f1.doubt_miss_time > sysdate - 8;
  -- group by f1.waybillcode, f1.check_zone
  --having count(waybillcode) > 1;

  cursor cur_time is
  --根据运单号和核查晚点查询遗失时间点
    select tt.doubt_miss_time as missTime, tt.check_time as checkTime
      from ompweb.tt_doubt_miss_check tt
     where tt.waybillcode = t_waybillNo
       and tt.check_zone = t_checkCode
     order by tt.check_time;

begin
  open cur_task;
  loop
    fetch cur_task
      into t_waybillNo, t_checkCode;
    exit when cur_task%notfound;

    open cur_time;
    loop
      fetch cur_time
        into t_missTime, t_checkTime;
      exit when cur_time%notfound;

      --第一次循环
      if (cur_time%rowcount = 1) then
        t_compareTime      := t_missTime;
        t_compareCheckTime := t_checkTime;
      end if;

      --非第一次循环
      if (cur_time%rowcount > 1) and
         to_number(t_missTime - t_compareCheckTime) * 24 != 16
          and
         to_number(t_missTime - t_compareCheckTime) * 24 != 8 then
        t_compareTime := t_missTime;
      end if;
      t_compareCheckTime := t_checkTime;
    end loop;
    close cur_time;

    -- 赋值“快件路由疑似遗失时间点”
    update ompweb.tt_doubt_miss_history hh
       set hh.route_doubt_miss_time = t_compareTime
     where hh.waybillcode = t_waybillNo
       and hh.check_zone_code = t_checkCode;

  end loop;
  close cur_task;
  commit;

end pro_miss_history;