存储过程实例

create or replace procedure proc_reback_72Hour(start_time in varchar2, --分诊开始时间
end_time in varchar2, --分诊结束时间
query_user in number, --查询工号
query_time in date, --查询时间
flag out varchar2 --返回值：1成功 0失败
) is
cursor cur_patient is
select distinct patient_id
from ws_precheck
where created_time >= to_date(start_time, 'yyyy-mm-dd ,hh24:mi:ss')
and created_time < to_date(end_time, 'yyyy-mm-dd, hh24:mi:ss');
c_patient cur_patient%rowtype;
v_sum number := 0;

cursor cur_arrivel(v_id varchar2) is
select f.precheck_id, f.arrivel_time
from ws_precheck p, ws_first_page f
where p.id = f.precheck_id
and p.patient_id = v_id
and f.arrivel_time >= to_date(start_time, 'yyyy-mm-dd ,hh24:mi:ss')
and f.arrivel_time < to_date(end_time, 'yyyy-mm-dd, hh24:mi:ss')
order by f.arrivel_time desc;
c_arrivel cur_arrivel%rowtype;
v_count number(4);
v_index number(1);
v_precheck_id1 number(8);
v_precheck_id2 number(8);
v_time1 date;
v_time2 date;

cursor cur_visit(v_precheck_id number) is
select p.id, p.name, p.card, p.patient_id, p.cause, f.arrivel_time
from ws_precheck p, ws_first_page f
where p.id = f.precheck_id
and f.precheck_id = v_precheck_id
and f.arrivel_time >= to_date(start_time, 'yyyy-mm-dd,hh24:mi:ss')
and f.arrivel_time < to_date(end_time, 'yyyy-mm-dd,hh24:mi:ss');
c_visit cur_visit%rowtype;

begin
flag := '1';

delete from rpt_return_visit r where r.query_user = query_user;

for c_patient in cur_patient loop
if c_patient.patient_id is not null then
select count(*)
into v_count
from ws_precheck p
where p.created_time >=
to_date(start_time, 'yyyy-mm-dd ,hh24:mi:ss')
and p.created_time < to_date(end_time, 'yyyy-mm-dd, hh24:mi:ss')
and p.patient_id = c_patient.patient_id;
if v_count > 1 then
v_index := 0;
for c_arrivel in cur_arrivel(c_patient.patient_id) loop
v_index := v_index + 1;
if v_index = 1 then
v_precheck_id1 := c_arrivel.precheck_id;
v_time1 := c_arrivel.arrivel_time;
elsif v_index = 2 then
v_precheck_id2 := c_arrivel.precheck_id;
v_time2 := c_arrivel.arrivel_time;
exit;
end if;
end loop;
if v_time1 - v_time2 <= 3 then
v_sum := v_sum + 1;
open cur_visit(v_precheck_id1);
fetch cur_visit
into c_visit;
insert into rpt_return_visit
values
(c_visit.id,
c_visit.patient_id,
c_visit.card,
c_visit.name,
c_visit.arrivel_time,
c_visit.cause,
query_user,
query_time);
close cur_visit;
end if;
--重置
v_count := null;
v_precheck_id1 := null;
v_precheck_id2 := null;
v_time1 := null;
v_time2 := null;
end if;
end if;

if v_sum >= 500 then
commit;
v_sum := 0;
end if;

end loop;

exception
when others then
flag := '0';

end proc_reback_72Hour;