oracle的存储过程无参,oracle 创建无参存储过程

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;