sp_medicinestockdecrease2.sql

一整套的源代码

create or replace function sp_medicinestockdecrease2(l_medicinestockid in integer,
                                                    l_amount          in out soomedicinedetail.amount%type)
  return integer is
  cursor c is
    select *
    from   medicinestockdetail
    where  medicinestockid = l_medicinestockid and amount > 0;
  r_msd medicinestockdetail%rowtype;
  r_ms       medicinestock%rowtype;
  l_new_soomedicinedetailid integer;
  l_medicinestockdetailid integer;
  l_count integer;
begin
  select *
  into   r_ms
  from   medicinestock
  where  medicinestockid = l_medicinestockid;

  if r_ms.amount < l_amount or l_amount < 0 then
    raise_application_error(-20001, '库存不足');
  else
    select count(*) into l_count from medicinestockdetail
    where medicinestockid = l_medicinestockid;

    if l_count = 0 then
      update medicinestock set amount = 0 where medicinestockid = l_medicinestockid;
      raise_application_error(-20001, '没有库存');
    end if;

    select min(medicinestockdetailid) into l_medicinestockdetailid
    from medicinestockdetail 
    where medicinestockid = l_medicinestockid and amount > 0;

    select * into r_msd from medicinestockdetail
    where medicinestockdetailid = l_medicinestockdetailid;

    if r_msd.amount < l_amount then
      raise_application_error(-20001, '当前批次数量不足');
    end if;

    update medicinestockdetail set amount = amount - l_amount where medicinestockdetailid = l_medicinestockdetailid;
  end if;

  return 0;
end;
/