JXL贵溪市人民医院

CREATE OR REPLACE PROCEDURE zl100_userjob预约排队 as

  type rec_病人挂号记录 is record(

    队列名称_In 病人挂号记录.执行部门id%Type,

    业务id_In   病人挂号记录.id%Type,

    患者姓名_In 病人挂号记录.姓名%Type,

    诊室_In     病人挂号记录.诊室%Type,

    执行人_in  病人挂号记录.执行人%type

    );

  v_病人挂号记录 rec_病人挂号记录;

  v_排队号码     排队叫号队列.排队号码%type;

  n_id           病人挂号记录.id%type;

  v_Id           排队叫号队列.id%type;

  v_排队序号     排队叫号队列.排队序号%type;

begin

  declare

    cursor mycusor is

      select distinct id

        from 病人挂号记录

       where 记录状态 = 1

         and 预约 = 1

         and 预约方式 <> '现场'

         and 执行状态=0

         and trunc(发生时间) = trunc(sysdate)

       order by id;

  begin

    open mycusor;

    loop

      fetch mycusor

        into n_id;

      if mycusor% found then

        select 执行部门id, id, 姓名, 诊室,执行人

          into v_病人挂号记录

          from 病人挂号记录

         where id = n_id;

        Select Nvl(Max(lpad(排队号码, 8, 0)), 0) + 1

          Into v_排队号码

          From 排队叫号队列

         where 业务类型 = 0

           and 队列名称 = v_病人挂号记录.队列名称_In

           and 排队时间 between Trunc(sysdate) and

               Trunc(sysdate) + 1 - 1 / 24 / 60 / 60;

        Select 排队叫号队列_Id.Nextval Into v_Id From Dual;

        v_排队序号 := Zl_排队叫号队列_Getqueuenum(0, v_病人挂号记录.队列名称_In);

        Insert Into 排队叫号队列

          (ID,

           业务类型,

           队列名称,

           科室id,

           医生姓名,

           排队号码,

           业务id,

           患者姓名,

           诊室,

           排队序号,

           排队状态,

           排队时间)

        Values

          (v_Id,

           0,

           v_病人挂号记录.队列名称_In,

           v_病人挂号记录.队列名称_In,

           v_病人挂号记录.执行人_In,

           v_排队号码,

           v_病人挂号记录.业务id_In,

           v_病人挂号记录.患者姓名_In,

           v_病人挂号记录.诊室_In,

           v_排队序号,

           0,

           Sysdate);

      else

        exit;

      end if;

    end loop;

    close mycusor;

  end;

end zl100_userjob预约排队;