oracle存储过程带返回值,oracle带返回值的存储过程

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; /