存储过程实例

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;