sp_medicinestockfindprice.sql

来自「一整套的源代码」· SQL 代码 · 共 37 行

create or replace function sp_medicinestockfindprice(l_medicinestockid in integer)
  return number is
  l_count                 integer;
  l_price                 number(16,
                                 4);
  l_medicinestockdetailid integer;
  l_siomedicinedetailid   integer;
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 siomedicinedetailid
  into   l_siomedicinedetailid
  from   medicinestockdetail
  where  medicinestockdetailid = l_medicinestockdetailid;

  select sprice
  into   l_price
  from   siomedicinedetail
  where  siomedicinedetailid = l_siomedicinedetailid;

  return l_price;
end;
/