模拟银行转账的SQL存储过程实现

create table t_CardInfo(id int not null auto_increment primary key,
name varchar(20) not null,
realName varchar(20) not null,
password varchar(20) not null,
cardNO varchar(25) not null,
balance int not null,
allocationKey varchar(20) not null);

insert into t_CardInfo(name,realName,password,cardNO,balance,allocationKey)
values('lisi','lisi','123','123456',1000,'123456');

insert into t_CardInfo(name,realName,password,cardNO,balance,allocationKey)
values('wangwu','wangwu','123','654321',1000,'654321');


delimiter //
drop procedure if exists transform //
create procedure transform(userName varchar(20),pwd varchar(20),myCardNO varchar(20),toCardNO varchar(20),myKey varchar(20),tomoney int,out error_login int,out error_key int,out error_card int,out error_money int,out error_trans int)
 begin
 declare t_error int default 0;
 declare selectResult int default 0;
 declare tranMaxMoney int default 0;
 declare myBalance int default 0;
 declare continue handler for sqlexception set t_error=1;
 set autocommit =0;
 start transaction;
 set selectResult = (select count(*) from t_CardInfo where name =userName and password = pwd);
 if selectResult = 0  then
 set t_error = 1;
 set error_login = 1;