oracle带返回值的存储过程

2f8gy7u81q

create table tb_storage
(
id number(9),
shopid varchar(10),
storage number(10) not null
);　　Table created
　　create table tb_shop(
id number(9),
shopid varchar(10),
shopname varchar(20)
);
　　Table created
　　insert into tb_storage values(1,1,20);
insert into tb_storage values(2,2,30);
insert into tb_storage values(3,3,33);
insert into tb_shop values(2,'s002','空调');
insert into tb_shop values(1,'s001','彩电');
insert into tb_shop values(3,'s003','电脑');

create or replace procedure proc_storage
(
param_shopId number,--商品编号
param_storageQuantity int ,--出库数量
param_shopName out varchar2,--出库商品名称
param_oldStorage out number,--原有库存
param_Storage out number,--现在库存
param_flag out number--执行状态 0成功，1找不到，2库存不足
)
as
begin
select storage into param_oldStorage from tb_storage where shopid=param_shopid;
select shopname into param_shopname from tb_shop where id=param_shopid;
if param_oldStorage < param_storageQuantity then
param_flag:=2;
else
update tb_storage set storage=storage-param_storageQuantity where shopid=param_shopId;
param_oldStorage:=param_oldStorage-param_storageQuantity;
param_flag:=0;
end if;
exception
when no_data_found then
param_flag:=1;
end;
/
　　declare
v_shopId number(5):=2;
v_storageQuantity int :=10;
v_shopname varchar2(10);
v_oldStorage number(10);
v_storage number(10);
v_flag number(2);
begin
proc_storage(
v_shopid,
v_storageQuantity ,
v_shopname,
v_oldStorage,
v_storage,
v_flag
);
case v_flag
when 0 then-- 成功
Dbms_output.put_line('出库商品：'||v_shopname);
Dbms_output.put_line('出库数量：'||v_storageQuantity);
Dbms_output.put_line('原有库存：'||v_oldStorage );
Dbms_output.put_line('现在库存：'||v_storage );
when 1 then
Dbms_output.put_line('找不到对应的商品！' );
when 2 then
Dbms_output.put_line('库存不足，不能出库！' );
end case;
end;
/