下面代码实现功能是:实现设备的借阅。即equip表中设备数减少,然后borrow表中对应的设备数增加,与银行转账过程类似,供参考。
create procedure transfer(in yonghu varchar(20),in shebei varchar(30),in amount int)
begin
declare num int default 0;
declare yue int default 0;
select count(*) into num from borrow where userId=yonghu and equip_id=shebei;
select equip_num into yue from equip where equip_id=shebei;
if (num=0 and yue>amount)then
start transaction;
update equip
set equip_num=equip_num-amount
where equip_id=shebei;
insert into borrow values(yonghu,shebei,amount,date(now()));
commit;
else
if(yue>amount) then
start transaction;
update equip
set equip_num=equip_num-amount
where equip_id=shebei;
update borrow
set bnum=bnum+amount
where equip_id=shebei and userId=yonghu;
commit;
end if;
end if;
end;