sp_medicinestockfindamount.sql

Delphi6开发的HIS程序

create or replace function sp_medicinestockfindamount(l_medicinestockid in integer)
  return number is
  l_medicinestockdetailid integer;
  l_count                 integer;
  l_amount                number(16,
                                 4);
begin
  select count(*)
  into   l_count
  from   medicinestockdetail
  where  medicinestockid = l_medicinestockid and amount > 0;

  if l_count = 0 then
    return - 1;
  end if;

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

  select amount
  into   l_amount
  from   medicinestockdetail
  where  medicinestockdetailid = l_medicinestockdetailid;

  return l_amount;
end;
/