数据库之存储过程的账户储存实例

数据库之存储过程的账户储存实例

**内容
**
利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中，假设账户关系表为account(accountnum，total)， accountnum为账号， total为余额。
代码实现
create table account(accountnum char(3),total float);/创建账号关系表account/

insert into account values(‘101’,500);
insert into account values(‘102’,300);

drop procedure if exists transfer;
delimiter $
create procedure transfer(in inAccount char(3), in outAccount char(3), in amount float)/定义存储过程transfer，其参数为转入账户，转出账户，转账额度/
proc_label:begin
declare total_out float; /**/
declare total_in float;
declare inAccountNum char(3);

select total into total_out from account where accountnum=outAccount;
if total_out is null then /如果转出账号不存在或账户中没有存款/
select ‘转出账号不存在或账户中没有存款’;
rollback;
leave proc_label;
end if;
if total_out < amount then/如果转出账号存款不足/
select ‘转出账号存款不足’;
rollback;
leave proc_label;
end if;

select accountnum into inAccountNum from account where accountnum=inAccount;
if inAccountNum is null then /如果转入账号不存在/
select ‘转入账号不存在’;
rollback;
leave proc_label;
end if;

update account set total=total-amount where accountnum=outAccount;
update account set total=total+amount where accountnum=inAccount;

end $
delimiter ;

call transfer(‘102’,‘101’,200);

select accountnum from account
where accountnum=‘101’;
实验结果：