出库单添加以及库存更新的存储过程

create or replace procedure outstoreform(out_id in varchar2,out_serial_number in varchar2,quantity in number,
out_date in date,commodity_id in varchar2,admin_id in varchar2,var_results out varchar2) as
results number;
procedure updatestock(c_id in varchar2,amount in number) is
begin
update c_stock
set nowstock=nowstock-amount
where id=c_id;
/*判断是否有影响的行*/
if sql%notfound then
dbms_output.put_line('库存更新失败');/*该语句是为了测试用*/
else
dbms_output.put_line('库存更新成功');/*该语句是为了测试用*/
end if;
end updatestock;
begin
/*根据填写入库单中的商品编号查询库存中是否有该商品*/
results:=selectstock(commodity_id);
/*根据results的值判断库存中是否存在要出库的商品，若results的值不为零则库存中有该类商品，可以出库，否则不能出库，该入库单不能添加*/
if results<>0 then
insert into outstockform(f_id,serial_number,quantity,outdate,id,a_id)
values(out_id,out_serial_number,quantity,out_date,commodity_id,admin_id);
/*调用子过程*/
updatestock(commodity_id,quantity);
var_results:='yes';
else
var_results:='no';
/*该语句是为了测试用*/
dbms_output.put_line('出库的商品不存在，不能添加该入库单记录');
end if;
end outstoreform;

//库存查询
create or replace function selectstock(outid in varchar2) return number
as
return_result number;
begin
select count(*) into return_result
from c_stock
where id=outid;
return (return_result);
end selectstock;